private void get_serveyresponce_dtls(HttpContext context)
{
try
{
vdm = new SalesDBManager();
string fromdate = context.Request["fromdate"].ToString();
string todate = context.Request["todate"].ToString();
DateTime dtfromdate = Convert.ToDateTime(fromdate);
DateTime dttodate = Convert.ToDateTime(todate);
cmd = new SqlCommand("SELECT COUNT(*) AS count, Status FROM tbl_TRN_SurveyResponse WHERE (SynchedOn BETWEEN @d1 AND @d2) GROUP BY Status");
cmd.Parameters.Add("@d1", GetLowDate(dtfromdate));
cmd.Parameters.Add("@d2", GetHighDate(dttodate));
DataTable dtcount = vdm.SelectQuery(cmd).Tables[0];
List <companydetails> SectionMaster = new List <companydetails>();
foreach (DataRow dr in dtcount.Rows)
{
companydetails getsectiondetails = new companydetails();
getsectiondetails.count = dr["count"].ToString();
getsectiondetails.status = dr["Status"].ToString();
getsectiondetails.fromdate = fromdate;
getsectiondetails.todate = todate;
SectionMaster.Add(getsectiondetails);
}
string response = GetJson(SectionMaster);
context.Response.Write(response);
}
catch (Exception ex)
{
string Response = GetJson(ex.Message);
context.Response.Write(Response);
}
}
private void bindsilos()
{
string branch_id = Context.Session["Branch_ID"].ToString();
SalesDBManager SalesDB = new SalesDBManager();
cmd = new SqlCommand("SELECT SiloId, SiloName,branchid FROM silomaster where branchid=@branchid ");
cmd.Parameters.Add("@branchid", branch_id);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlfromsilo.DataSource = dttrips;
ddlfromsilo.DataTextField = "SiloName";
ddlfromsilo.DataValueField = "SiloId";
ddlfromsilo.DataBind();
ddlfromsilo.Items.Insert(0, new ListItem {
Value = "0", Text = "Select Silo", Selected = true
});
ddlfromsilo.SelectedValue = "0";
ddltosilo.DataSource = dttrips;
ddltosilo.DataTextField = "SiloName";
ddltosilo.DataValueField = "SiloId";
ddltosilo.DataBind();
ddltosilo.Items.Insert(0, new ListItem {
Value = "0", Text = "Select Silo", Selected = true
});
ddltosilo.SelectedValue = "0";
}
protected void ddlBranch_SelectedIndexChanged(object sender, EventArgs e)
{
SalesDBManager SalesDB = new SalesDBManager();
if (ddlbranchType.SelectedValue == "All")
{
cmd = new SqlCommand("SELECT vendors.sno, vendors.vendorcode, vendors.vendorname,vendors.shortname, vendors.email, vendors.mobno, vendors.panno, vendors.doe, vendors.branchid, vendors.address FROM vendors INNER JOIN branch_info ON vendors.sno = branch_info.venorid INNER JOIN branchmapping ON branch_info.sno = branchmapping.subbranch WHERE (branchmapping.superbranch = @branchid)");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranches.DataSource = dttrips;
ddlbranches.DataTextField = "shortname";
ddlbranches.DataValueField = "sno";
ddlbranches.DataBind();
}
if (ddlbranchType.SelectedValue == "Inter Branches")
{
cmd = new SqlCommand("SELECT vendors.sno, vendors.vendorcode,vendors.shortname, vendors.vendorname, vendors.email, vendors.mobno, vendors.panno, vendors.doe, vendors.branchid, vendors.address FROM vendors INNER JOIN branch_info ON vendors.sno = branch_info.venorid INNER JOIN branchmapping ON branch_info.sno = branchmapping.subbranch WHERE (branchmapping.superbranch = @branchid) and vendors.branchtype='Inter Branch'");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranches.DataSource = dttrips;
ddlbranches.DataTextField = "shortname";
ddlbranches.DataValueField = "sno";
ddlbranches.DataBind();
}
if (ddlbranchType.SelectedValue == "Other Branches")
{
cmd = new SqlCommand("SELECT vendors.sno, vendors.vendorcode, vendors.shortname,vendors.vendorname, vendors.email, vendors.mobno, vendors.panno, vendors.doe, vendors.branchid, vendors.address FROM vendors INNER JOIN branch_info ON vendors.sno = branch_info.venorid INNER JOIN branchmapping ON branch_info.sno = branchmapping.subbranch WHERE (branchmapping.superbranch = @branchid) and vendors.branchtype='Other Branch'");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranches.DataSource = dttrips;
ddlbranches.DataTextField = "shortname";
ddlbranches.DataValueField = "sno";
ddlbranches.DataBind();
}
}
void GetReport()
{
try
{
SalesDBManager SalesDB = new SalesDBManager();
lblmsg.Text = "";
DataTable dtIssueTyre = new DataTable();
string branchid = Session["Po_BranchID"].ToString();
cmd = new SqlCommand("SELECT supplierid, name, description, companyname, contactname, street1, street2, city, state, country, zipcode, phoneno, mobileno, emailid, websiteurl, createdby,createdon, status, warranty, warrantytype, insurance, insuranceamount, contactnumber, branchid, name + '_' + street1 AS Address FROM suppliersdetails WHERE (branchid = @branchid)");
//cmd = new SqlCommand("SELECT sno, productid, qty, doe, vehicleno, branchid FROM diesel_consumptiondetails ORDER BY doe");
cmd.Parameters.Add("@branchid", branchid);
DataTable dtoutward = SalesDB.SelectQuery(cmd).Tables[0];
if (dtoutward.Rows.Count > 0)
{
grdReports.DataSource = dtoutward;
grdReports.DataBind();
string title = "Supplier Details";
Session["title"] = title;
Session["filename"] = "Supplier Details Report";
Session["xportdata"] = dtoutward;
}
else
{
lblmsg.Text = "No data found";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
vdm = new SalesDBManager();
string connString = "";
string filePath = Server.MapPath("~/Files/") + Path.GetFileName(fileuploadExcel.PostedFile.FileName);
fileuploadExcel.SaveAs(filePath);
if (filePath.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (filePath.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
Session["btnImport"] = dt;
grvExcelData.DataSource = dt;
grvExcelData.DataBind();
}
catch (Exception ex)
{
lblMessage.Text = ex.ToString();
lblMessage.Visible = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
vdm = new SalesDBManager();
if (!Page.IsPostBack)
{
if (!Page.IsCallback)
{
dtp_FromDate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm"); //Convert.ToString(lblFromDate.Text); //// /////
dtp_Todate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm"); // Convert.ToString(lbltodate.Text);/// ////
// lblAddress.Text = Session["Address"].ToString();
// lblTitle.Text = Session["TitleName"].ToString();
}
}
//if (Session["Po_BranchID"] == null)
// Response.Redirect("Login.aspx");
//else
//{
// BranchID = Session["Po_BranchID"].ToString();
// vdm = new SalesDBManager();
// if (!Page.IsPostBack)
// {
// if (!Page.IsCallback)
// {
// dtp_FromDate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm");
// dtp_Todate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm");
// lblAddress.Text = Session["Address"].ToString();
// lblTitle.Text = Session["TitleName"].ToString();
// }
// }
//}
}
protected void ddlBranch_SelectedIndexChanged(object sender, EventArgs e)
{
SalesDBManager SalesDB = new SalesDBManager();
if (ddlBranch.SelectedValue == "All")
{
hideVehicles.Visible = false;
}
if (ddlBranch.SelectedValue == "Branch Wise")
{
hideVehicles.Visible = true;
cmd = new SqlCommand("SELECT vendors.sno, vendors.vendorcode, vendors.vendorname, vendors.email, vendors.mobno, vendors.panno, vendors.doe, vendors.branchid, vendors.address FROM vendors INNER JOIN branch_info ON vendors.sno = branch_info.venorid INNER JOIN branchmapping ON branch_info.sno = branchmapping.subbranch WHERE (branchmapping.superbranch = @branchid)");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranches.DataSource = dttrips;
ddlbranches.DataTextField = "vendorname";
ddlbranches.DataValueField = "sno";
ddlbranches.DataBind();
}
if (ddlBranch.SelectedValue == "Vehicle Wise")
{
hideVehicles.Visible = true;
cmd = new SqlCommand("SELECT sno,vehicleno,capacity,noofqty FROM vehicle_master WHERE branchid=@branchid");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranches.DataSource = dttrips;
ddlbranches.DataTextField = "vehicleno";
ddlbranches.DataValueField = "vehicleno";
ddlbranches.DataBind();
}
}
private void bindvehicleno()
{
string vehicletype = ddlslct_vtype.SelectedItem.Text;
SalesDBManager SalesDB = new SalesDBManager();
try
{
cmd = new SqlCommand("SELECT distinct(vehicleno) FROM weighbridge where vehicletype = @vehicletype");
cmd.Parameters.AddWithValue("@vehicletype", vehicletype);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlvehicleno.DataSource = dttrips;
ddlvehicleno.DataTextField = "vehicleno";
// ddl_vehicleno.DataValueField = "sno";
ddlvehicleno.DataBind();
ddlvehicleno.ClearSelection();
ddlvehicleno.Items.Insert(0, new ListItem {
Value = "0", Text = "All", Selected = true
});
ddlvehicleno.SelectedValue = "0";
// dataGridView1.DataBindings();
}
catch (Exception ex)
{
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
lbltodate.Text = todate.ToString("dd/MM/yyyy");
if (BranchID == "1" || BranchID == "22")
{
cmd = new SqlCommand("SELECT CONVERT(varchar, doe, 103) AS Date, type AS Type, time AS Time, coldroom1 AS ColdRoom1, coldroom2 AS ColdRoom2, incubationroom AS IncubationRoom, remarks AS Remarks, CONVERT(varchar, doe, 103) AS Date FROM curdcoldroom_details WHERE (branchid = @branchid) AND (doe BETWEEN @d1 AND @d2) ORDER BY doe");
}
else
{
cmd = new SqlCommand("SELECT CONVERT(varchar, doe, 103) AS Date, time AS Time, coldroom1 AS ColdRoom1, coldroom2 AS ColdRoom2, coldroom3 AS ColdRoom3, coldroom4 AS BlastRoom, coldroom5 AS ContainerNo1, coldroom6 AS ContainerNo2, coldroom7 AS IBT, incubationroom AS IncubationRoom, remarks AS Remarks FROM curdcoldroom_details WHERE (branchid = @branchid) AND (doe BETWEEN @d1 AND @d2) ORDER BY doe ");
}
cmd.Parameters.Add("@branchid", BranchID);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
DataTable tabledetails = SalesDB.SelectQuery(cmd).Tables[0];
if (tabledetails.Rows.Count > 0)
{
grdReports.DataSource = tabledetails;
grdReports.DataBind();
hidepanel.Visible = true;
}
else
{
lblmsg.Text = "No data found";
hidepanel.Visible = false;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
void FillRouteName()
{
try
{
vdm = new SalesDBManager();
if (Session["LevelType"].ToString() == "2" || Session["LevelType"].ToString() == "1")
{
cmd = new SqlCommand("SELECT Plant_Code, Plant_Name FROM Plant_Master WHERE (Plant_Code = @BranchID)");
cmd.Parameters.Add("@BranchID", Session["branch"].ToString());
DataTable dtPlant = vdm.SelectQuery(cmd).Tables[0];
ddlSalesOffice.DataSource = dtPlant;
ddlSalesOffice.DataTextField = "Plant_Name";
ddlSalesOffice.DataValueField = "Plant_Code";
ddlSalesOffice.DataBind();
}
else
{
cmd = new SqlCommand("SELECT Plant_Code, Plant_Name FROM Plant_Master ");
DataTable dtPlant = vdm.SelectQuery(cmd).Tables[0];
ddlSalesOffice.DataSource = dtPlant;
ddlSalesOffice.DataTextField = "Plant_Name";
ddlSalesOffice.DataValueField = "Plant_Code";
ddlSalesOffice.DataBind();
}
}
catch
{
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
int plantcode = Convert.ToInt32(ddlplantname.SelectedItem.Value);
lblFromDate.Text = fromdate.ToString("dd/MMM/yyyy");
lbltodate.Text = todate.ToString("dd/MMM/yyyy");
DateTime.Now.ToString("hh:mm:ss tt");
hidepanel.Visible = true;
Datatable dtdiff = new Datatable();
cmd = new SqlCommand("SELECT Tid, Agent_id, Prdate, Sessions, Milk_kg, Fat, Snf, Plant_Code, modify_Kg, modify_fat, modify_snf, DIFFKG, DIFFFAT, DIFFSNF, Remark FROM Procurementimport WHERE Plant_Code=@plantcode AND Prdate between @d1 and @d2");
// cmd = new SqlCommand("SELECT date,session,starttime,ibttempstart,stoptime,ibttempstop FROM cmpruntime where Plant_code=@plantcode and date between @d1 and @d2");
cmd.Parameters.Add("@plantcode", plantcode);
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
DataTable dtcompresssionrunninghours = SalesDB.SelectQuery(cmd).Tables[0];
if (dtcompresssionrunninghours.Rows.Count > 0)
{
grdcompressor.DataSource = dtcompresssionrunninghours;
grdcompressor.DataBind();
hidepanel.Visible = true;
}
else
{
lblmsg.Text = "No dasta were found";
hidepanel.Visible = false;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void finalize_stockclosing(object sender, EventArgs e)
{
vdm = new SalesDBManager();
DataTable dt = (DataTable)Session["xportdata"];
DateTime doe = SalesDBManager.GetTime(vdm.conn);
DateTime closingdate = doe.AddDays(1);
string entryby = Session["Employ_Sno"].ToString();
string branchid = Session["Po_BranchID"].ToString();
foreach (DataRow dr in dt.Rows)
{
try
{
string productname = dr["Product Name"].ToString();
if (productname != "Total")
{
string qty = dr["ClosingQty"].ToString();
if (qty != "0")
{
double price = Convert.ToDouble(dr["OpPrice"].ToString());
if (price > 1)
{
}
else
{
price = Convert.ToDouble(dr["ReceiptPrice"].ToString());
}
cmd = new SqlCommand("select productid from productmaster where productname = @productname");
cmd.Parameters.Add("@productname", productname);
DataTable dt_productid = vdm.SelectQuery(cmd).Tables[0];
string productid = dt_productid.Rows[0]["productid"].ToString();
cmd = new SqlCommand("insert into stockclosingdetails (productid,qty,price,doe,entryby,branchid) values (@productid,@qty,@price,@doe,@entryby,@branchid)");
cmd.Parameters.Add("@productid", productid);
cmd.Parameters.Add("@qty", qty);
cmd.Parameters.Add("@price", price);
cmd.Parameters.Add("@doe", closingdate);
cmd.Parameters.Add("@entryby", entryby);
cmd.Parameters.Add("@branchid", branchid);
vdm.insert(cmd);
}
else
{
}
}
}
catch (Exception ex)
{
string productname = dr["Product Name"].ToString();
}
}
lblmsg.Text = "Saved successfully";
DataTable dtempty = new DataTable();
grdReports.DataSource = dtempty;
grdReports.DataBind();
}
public void btn_Generate_Click()
{
try
{
Report.Columns.Add("sno");
Report.Columns.Add("ERP Code");
Report.Columns.Add("Item Name");
Report.Columns.Add("UOM");
Report.Columns.Add("Opening Balance");
Report.Columns.Add("Per Day Concsumption");
Report.Columns.Add("No of Days");
Report.Columns.Add("Remarks");
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string branchid = Session["Po_BranchID"].ToString();
cmd = new SqlCommand("SELECT productmoniter.productid, uimmaster.uim, productmoniter.qty, productmoniter.price, productmoniter.branchid, productmoniter.minstock, productmoniter.maxstock, productmoniter.perdayconcsumption, productmaster.productname, productmaster.itemcode FROM productmoniter INNER JOIN productmaster ON productmoniter.productid = productmaster.productid INNER JOIN uimmaster ON productmaster.uim = uimmaster.sno WHERE (productmoniter.perdayconcsumption IS NOT NULL) AND (productmoniter.branchid = @branchid) ORDER BY productmaster.productid");
cmd.Parameters.Add("@branchid", branchid);
DataTable dtproducts = SalesDB.SelectQuery(cmd).Tables[0];
var i = 1;
if (dtproducts.Rows.Count > 0)
{
foreach (DataRow dr in dtproducts.Rows)
{
DataRow newrow = Report.NewRow();
newrow["Sno"] = i++.ToString();
newrow["ERP Code"] = dr["itemcode"].ToString();
newrow["Item Name"] = dr["productname"].ToString();
newrow["UOM"] = dr["uim"].ToString();
newrow["Opening Balance"] = dr["qty"].ToString();
double minqty = 0;
double OBQTY = Convert.ToDouble(dr["qty"].ToString());
string perdayconcsumption = dr["perdayconcsumption"].ToString();
double days = 0;
if (perdayconcsumption != "0")
{
minqty = Convert.ToDouble(perdayconcsumption);
days = OBQTY / minqty;
}
newrow["Per Day Concsumption"] = perdayconcsumption;
newrow["No of Days"] = Math.Round(days, 2);
newrow["Remarks"] = "Based on need vary the perday consumption";
Report.Rows.Add(newrow);
}
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
DateTime fromdate = DateTime.Now;
DateTime todate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
datestrig = dtp_Todate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MMM/yyyy");
lbltodate.Text = todate.ToString("dd/MMM/yyyy");
//cmd = new SqlCommand("SELECT milktransactions.dcno AS DCNo, milktransactions.inwardno as InwardNo, milktransactions.vehicleno ,processingdept.deptname AS DeptName, milktransactions.transtype as Type, milktransactions.qty_ltr AS QtyLtr, milktransactions.qty_kgs AS QtyKgs, milktransactions.percentageon AS PercentageOn, milktransactions.snf AS SNF, milktransactions.fat AS FAT, milktransactions.clr AS CLR, milktransactions.cob AS COB, milktransactions.hs AS HS, milktransactions.phosps as Phosps, milktransactions.alcohol as Alcohol, milktransactions.temp AS Temp, processingdept_1.deptname AS SectionName, milktransactions.remarks, milktransactions.qco, milktransactions.chemist FROM milktransactions INNER JOIN processingdept ON milktransactions.transid = processingdept.sno INNER JOIN processingdept processingdept_1 ON milktransactions.sectionid = processingdept_1.sno WHERE (milktransactions.doe BETWEEN @d1 AND @d2) AND (milktransactions.transtype = @transtype) ");
cmd = new SqlCommand("SELECT CONVERT(VARCHAR(10), milktransactions.doe, 103) AS Date, milktransactions.dcno AS DCNo, milktransactions.inwardno AS InwardNo, milktransactions.vehicleno AS VehicleNo,milktransactions.qty_kgs AS KGS, milktransactions.qty_ltr AS LTR, milktransactions.snf AS SNF, milktransactions.fat AS FAT, milktransactions.clr AS CLR, milktransactions.cob1 AS COB, milktransactions.hs AS HS, milktransactions.phosps1 AS Phosps, milktransactions.alcohol AS Alcohol, milktransactions.temp AS Temp, milktransactions.acidity AS Acidity, milktransactions.mbrt AS MBRT,vendors.vendorname as VendorName, milktransactions.qco AS QCO, milktransactions.chemist AS Chemist FROM milktransactions INNER JOIN vendors ON milktransactions.sectionid = vendors.sno WHERE (milktransactions.doe BETWEEN @d1 AND @d2) AND (milktransactions.transtype = @transtype) AND (milktransactions.branchid = @branchid)");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetHighDate(todate));
cmd.Parameters.Add("@transtype", "in");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dtInward = SalesDB.SelectQuery(cmd).Tables[0];
if (dtInward.Rows.Count > 0)
{
grdReports.DataSource = dtInward;
grdReports.DataBind();
hidepanel.Visible = true;
}
else
{
lblmsg.Text = "No data were found";
hidepanel.Visible = false;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
hidepanel.Visible = false;
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
SalesDBManager SalesDB = new SalesDBManager();
if (BranchID == "1" || BranchID == "26")
{
//SELECT silo_outward_transaction.sno AS ref_no, silo_outward_transaction.date, processingdepartments.departmentname, silo_outward_transaction.qty_kgs, silo_outward_transaction.qty_ltrs, silo_outward_transaction.fat, silo_outward_transaction.snf FROM silo_outward_transaction INNER JOIN processingdepartments ON silo_outward_transaction.departmentid = processingdepartments.departmentid WHERE (silo_outward_transaction.date BETWEEN @d1 AND @d2) AND (processingdepartments.branchid = @branchid) AND (silo_outward_transaction.sno = @ref_no)
//cmd = new SqlCommand("SELECT silo_outward_transaction.sno AS ref_no, silo_outward_transaction.date, processingdepartments.departmentname, silo_outward_transaction.qty_kgs, silo_outward_transaction.qty_ltrs, silo_outward_transaction.fat, silo_outward_transaction.snf FROM silo_outward_transaction INNER JOIN processingdepartments ON silo_outward_transaction.departmentid = processingdepartments.departmentid WHERE (processingdepartments.branchid = @branchid) AND (silo_outward_transaction.sno = @RefDcNo)");
cmd = new SqlCommand("SELECT silo_outward_transaction.sno AS ref_no, silo_outward_transaction.date, processingdepartments.departmentname as NameOfTheItem, silo_outward_transaction.qty_kgs as Quantity, silo_outward_transaction.qty_ltrs, silo_outward_transaction.fat as FAT, silo_outward_transaction.snf AS SNF, silomaster.SiloName FROM silo_outward_transaction INNER JOIN processingdepartments ON silo_outward_transaction.departmentid = processingdepartments.departmentid INNER JOIN silomaster ON silo_outward_transaction.siloid = silomaster.SiloId WHERE (processingdepartments.branchid = @branchid) AND (silo_outward_transaction.sno = @RefDcNo)");
cmd.Parameters.Add("@RefDcNo", txt_refdcno.Text);
cmd.Parameters.Add("@BranchID", BranchID);
}
else
{
}
DataTable dtDispatch = vdm.SelectQuery(cmd).Tables[0];
if (dtDispatch.Rows.Count > 0)
{
DataView view1 = new DataView(dtDispatch);
DataTable dtlblValues = view1.ToTable(true, "ref_no", "date", "SiloName", "NameOfTheItem");
DataTable dtgrdValues = view1.ToTable(true, "NameOfTheItem", "Quantity", "FAT", "SNF");
if (dtlblValues.Rows.Count > 0)
{
lblRefdcno.Text = txt_refdcno.Text;
//string branchcode = dtlblValues.Rows[0]["branchcode"].ToString();
lblRefdcno.Text = dtlblValues.Rows[0]["ref_no"].ToString();
lblassigndate.Text = dtlblValues.Rows[0]["date"].ToString();
lblfrom.Text = "Processing Section";
lblto.Text = dtlblValues.Rows[0]["NameOfTheItem"].ToString();
}
if (dtgrdValues.Rows.Count > 0)
{
grdReports.DataSource = dtgrdValues;
grdReports.DataBind();
}
//string barname = dtlblValues.Rows[0]["branchcode"].ToString();
ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "generateBarcode('');", true);
pnlHide.Visible = true;
}
else
{
lblmsg.Text = "No data were found";
pnlHide.Visible = false;
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
pnlHide.Visible = false;
}
}
void FillRouteName()
{
vdm = new SalesDBManager();
cmd = new SqlCommand("SELECT Plant_Code, Plant_Name FROM Plant_Master ");
DataTable dtPlant = vdm.SelectQuery(cmd).Tables[0];
ddlSalesOffice.DataSource = dtPlant;
ddlSalesOffice.DataTextField = "Plant_Name";
ddlSalesOffice.DataValueField = "Plant_Code";
ddlSalesOffice.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
vdm = new SalesDBManager();
if (!Page.IsPostBack)
{
if (!Page.IsCallback)
{
dtp_FromDate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm"); //Convert.ToString(lblFromDate.Text); //// /////
dtp_Todate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm"); // Convert.ToString(lbltodate.Text);/// ////
}
}
}
protected void ddlSalesOffice_SelectedIndexChanged(object sender, EventArgs e)
{
vdm = new SalesDBManager();
cmd = new SqlCommand("SELECT accountheads.HeadName, accountheads.Sno FROM subpayable INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno INNER JOIN cashpayables ON subpayable.RefNo = cashpayables.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.VoucherType = 'Debit') GROUP BY accountheads.Sno,accountheads.HeadName ORDER BY accountheads.HeadName,accountheads.Sno");
cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
DataTable dtHead = vdm.SelectQuery(cmd).Tables[0];
ddlHeadOfaccounts.DataSource = dtHead;
ddlHeadOfaccounts.DataTextField = "HeadName";
ddlHeadOfaccounts.DataValueField = "Sno";
ddlHeadOfaccounts.DataBind();
ddlHeadOfaccounts.Items.Insert(0, new ListItem("Select", "0"));
}
public void fillbranches()
{
SalesDBManager SalesDB = new SalesDBManager();
cmd = new SqlCommand("SELECT vendors.sno, vendors.vendorcode, vendors.vendorname, vendors.email, vendors.mobno, vendors.panno, vendors.doe, vendors.branchid, vendors.address FROM vendors INNER JOIN branch_info ON vendors.sno = branch_info.venorid INNER JOIN branchmapping ON branch_info.sno = branchmapping.subbranch WHERE (branchmapping.superbranch = @branchid) and vendors.branchtype='Other Branch'");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranches.DataSource = dttrips;
ddlbranches.DataTextField = "vendorname";
ddlbranches.DataValueField = "sno";
ddlbranches.DataBind();
}
protected void btn_finalizeclick(object sender, EventArgs e)
{
DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
DataTable dtdetails = (DataTable)Session["finalize"];
if (dtdetails.Rows.Count > 0)
{
DateTime fromdate = DateTime.Now;
string[] datestrig = dtp_FromDate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
string date = fromdate.ToString("dd/MM/yyyy");
string[] data = date.Split('/');
string month = data[1].ToString();
string year = data[2].ToString();
string mainbranch = Session["Branch_ID"].ToString();
foreach (DataRow dr in dtdetails.Rows)
{
string productid = dr["ProductId"].ToString();
string packingcharge = dr["RATE PER LTR"].ToString();
cmd = new SqlCommand("Update packingcharges set rateperltr=@rateperltr where productid=@pid and month=@month and year=@yr and branchid=@bid");
cmd.Parameters.Add("@month", month);
cmd.Parameters.Add("@yr", year);
cmd.Parameters.Add("@pid", productid);
cmd.Parameters.Add("@rateperltr", packingcharge);
cmd.Parameters.Add("@bid", mainbranch);
if (vdm.Update(cmd) == 0)
{
cmd = new SqlCommand("INSERT INTO packingcharges(productid, rateperltr, month, year, branchid, doe) values (@productid, @rate, @mnth, @year, @branchid, @doe)");
cmd.Parameters.Add("@mnth", month);
cmd.Parameters.Add("@year", year);
cmd.Parameters.Add("@productid", productid);
cmd.Parameters.Add("@rate", packingcharge);
cmd.Parameters.Add("@branchid", mainbranch);
cmd.Parameters.Add("@doe", ServerDateCurrentdate);
vdm.insert(cmd);
}
}
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
try
{
devdm = new VehicleDBMgr();
SalesDBManager vdm = new SalesDBManager();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txtTodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
cmd = new SqlCommand("SELECT productid, qty, doe, vehicleno, dieselcost FROM diesel_consumptiondetails WHERE (doe BETWEEN @d1 AND @d2) AND branchid=@branchid");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetLowDate(Todate));
cmd.Parameters.Add("@branchid", "2");
DataTable dttotal = vdm.SelectQuery(cmd).Tables[0];
grdReports.DataSource = dttotal;
grdReports.DataBind();
string title = "DieselReport From: " + fromdate.ToString() + " To: " + Todate.ToString();
Session["title"] = title;
Session["filename"] = "DieselReport";
Session["xportdata"] = dttotal;
pvisible.Visible = true;
//Session["quantity"] = quantity;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
private void bindvechicle()
{
vdm = new SalesDBManager();
cmd = new SqlCommand("select sectionid,name from sectionmaster where name like '%AP%'");
DataTable dtvehcle = vdm.SelectQuery(cmd).Tables[0];
ddl_vehicle_no.DataSource = dtvehcle;
ddl_vehicle_no.DataTextField = "name";
ddl_vehicle_no.DataValueField = "name";
ddl_vehicle_no.DataBind();
ddl_vehicle_no.ClearSelection();
ddl_vehicle_no.Items.Insert(0, new ListItem {
Value = "0", Text = "--Select VehcleNumber--", Selected = true
});
ddl_vehicle_no.SelectedValue = "0";
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
SalesDBManager nvdm = new SalesDBManager();
DataTable dtconsumption = (DataTable)Session["xportdata"];
DateTime CreateDate = SalesDBManager.GetTime(nvdm.conn);
string createdby = Session["Employ_Sno"].ToString();
if (dtconsumption.Rows.Count > 0)
{
foreach (DataRow dr in dtconsumption.Rows)
{
string date = dr["Invoice Date"].ToString();
DateTime dt = Convert.ToDateTime(date);
string qty = dr["Qty"].ToString();
string vcehicleno = dr["Customer Name"].ToString();
string Rate = dr["Rate"].ToString();
string pid = "2285";
if (Convert.ToDouble(qty) > 0)
{
sqlcmd = new SqlCommand("Insert into diesel_consumptiondetails (productid, qty, doe, vehicleno, dieselcost, createdby, createddate, branchid, categoryid, subcategoryid) values (@productid, @qty, @doe, @vehicleno, @dieselcost, @createdby, @createddate,@branchid, @catid, @subcatid)");
sqlcmd.Parameters.Add("@productid", pid);
sqlcmd.Parameters.Add("@qty", qty);
sqlcmd.Parameters.Add("@doe", dt);
sqlcmd.Parameters.Add("@vehicleno", vcehicleno);
sqlcmd.Parameters.Add("@createdby", createdby);
sqlcmd.Parameters.Add("@createddate", CreateDate);
sqlcmd.Parameters.Add("@dieselcost", Rate);
sqlcmd.Parameters.Add("@branchid", "2");
sqlcmd.Parameters.Add("@catid", "8");
sqlcmd.Parameters.Add("@subcatid", "37");
nvdm.insert(sqlcmd);
sqlcmd = new SqlCommand("update productmoniter set qty=qty-@qty where productid='2285' AND branchid=@branchid");
sqlcmd.Parameters.Add("@branchid", "2");
sqlcmd.Parameters.Add("@qty", qty);
nvdm.Update(sqlcmd);
}
}
lblmsg.Text = "Successfuly Saved";
}
}
catch (Exception ex)
{
throw ex;
}
}
private void bindcompanydetails()
{
SalesDBManager SalesDB = new SalesDBManager();
cmd = new SqlCommand("select sno, cmpname, cmpcode from companymaster");
DataTable dtcmp = vdm.SelectQuery(cmd).Tables[0];
ddlcompany.DataSource = dtcmp;
ddlcompany.DataTextField = "cmpname";
ddlcompany.DataValueField = "sno";
ddlcompany.DataBind();
ddlcompany.ClearSelection();
ddlcompany.Items.Insert(0, new ListItem {
Value = "0", Text = "--Select Company--", Selected = true
});
ddlcompany.SelectedValue = "0";
}
private void bindbranches()
{
SalesDBManager SalesDB = new SalesDBManager();
cmd = new SqlCommand("SELECT batchid, batch, batchcode FROM batchmaster");
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlslct_Batch.DataSource = dttrips;
ddlslct_Batch.DataTextField = "batch";
ddlslct_Batch.DataValueField = "batchid";
ddlslct_Batch.DataBind();
ddlslct_Batch.ClearSelection();
ddlslct_Batch.Items.Insert(0, new ListItem {
Value = "0", Text = "--Select Batch--", Selected = true
});
ddlslct_Batch.SelectedValue = "0";
}
private void bindsilos()
{
SalesDBManager SalesDB = new SalesDBManager();
cmd = new SqlCommand("SELECT SiloId, SiloName FROM silomaster where branchid=@branchid");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlfromsilo.DataSource = dttrips;
ddlfromsilo.DataTextField = "SiloName";
ddlfromsilo.DataValueField = "SiloId";
ddlfromsilo.DataBind();
ddlfromsilo.ClearSelection();
ddlfromsilo.Items.Insert(0, new ListItem {
Value = "0", Text = "--Select Silo--", Selected = true
});
ddlfromsilo.SelectedValue = "0";
}
public void binddepartments()
{
SalesDBManager SalesDB = new SalesDBManager();
cmd = new SqlCommand("SELECT departmentid, departmentname, quantity, branchid FROM processingdepartments ");
cmd.Parameters.Add("@branchid", BranchID);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlsection.DataSource = dttrips;
ddlsection.DataTextField = "departmentname";
ddlsection.DataValueField = "departmentid";
ddlsection.DataBind();
ddlsection.ClearSelection();
ddlsection.Items.Insert(0, new ListItem {
Value = "0", Text = "Select Section", Selected = true
});
ddlsection.SelectedValue = "0";
}
protected void btnSubmitt_Click(object sender, EventArgs e)
{
try
{
if (Session["Employ_Sno"] != null)
{
lblError.Text = "";
UserName = Session["Employ_Sno"].ToString();
vdm = new SalesDBManager();
cmd = new SqlCommand("SELECT Passward FROM employee_erp WHERE Sno = @Sno");
cmd.Parameters.Add("@Sno", UserName);
DataTable dt = vdm.SelectQuery(cmd).Tables[0];
if (dt.Rows.Count > 0)
{
if (txtNewPassWord.Text == txtConformPassWord.Text)
{
txtNewPassWord.Text = txtConformPassWord.Text;
cmd = new SqlCommand("Update employee_erp set Passward=@Password where Sno=@Sno ");
cmd.Parameters.Add("@Sno", UserName);
cmd.Parameters.Add("@Password", txtNewPassWord.Text.Trim());
vdm.Update(cmd);
lblMessage.Text = "Your Password has been Changed successfully";
Response.Redirect("Login.aspx", false);
}
else
{
lblError.Text = "Conform password not match";
}
}
else
{
lblError.Text = "Entered username is incorrect";
}
}
else
{
Response.Redirect("Login.aspx", false);
}
}
catch (Exception ex)
{
lblError.Text = "Password Changed Failed";
}
}
private void bindbranchs()
{
SalesDBManager SalesDB = new SalesDBManager();
string mainbranch = ddlcompany.SelectedItem.Value;
cmd = new SqlCommand("SELECT branchmaster.branchid, branchmaster.branchname, branchmaster.cmpid FROM branchmaster WHERE (branchmaster.cmpid = @m)");
cmd.Parameters.Add("@m", mainbranch);
DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
ddlbranch.DataSource = dttrips;
ddlbranch.DataTextField = "branchname";
ddlbranch.DataValueField = "branchid";
ddlbranch.DataBind();
ddlbranch.ClearSelection();
ddlbranch.Items.Insert(0, new ListItem {
Value = "0", Text = "--Select Branch--", Selected = true
});
ddlbranch.SelectedValue = "0";
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["Branch_ID"] == null)
{
Response.Redirect("Login.aspx");
}
else
{
BranchID = Session["Branch_ID"].ToString();
vdm = new SalesDBManager();
if (!Page.IsPostBack)
{
if (!Page.IsCallback)
{
dtp_FromDate.Text = DateTime.Now.ToString("dd-MM-yyyy HH:mm");
}
}
}
}
