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();
    }
    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 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;
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable dt = (DataTable)Session["btnImport"];
            int       i  = 1;
            foreach (DataRow dr in dt.Rows)
            {
                vdm = new SalesDBManager();
                string itemcode    = dr["Itemcode"].ToString();
                string productname = dr["productname"].ToString();
                cmd = new SqlCommand("SELECT productid FROM productmaster WHERE ((itemcode=@itemcode) OR (productname=@productname))");
                cmd.Parameters.Add("@itemcode", itemcode);
                cmd.Parameters.Add("@productname", productname);
                DataTable dtproducts = vdm.SelectQuery(cmd).Tables[0];
                if (dtproducts.Rows.Count > 0)
                {
                }
                else
                {
                    cmd = new SqlCommand("insert into productmaster(productname,itemcode,createdby, branchid) values(@productname,@itemcode,@entryby,@branchid)");
                    cmd.Parameters.Add("@productname", productname);
                    cmd.Parameters.Add("@itemcode", itemcode);
                    cmd.Parameters.Add("@branchid", "2");
                    cmd.Parameters.Add("@entryby", Session["Employ_Sno"].ToString());
                    vdm.insert(cmd);
                }

                //cmd = new SqlCommand("update branchmaster set whcode=@whcode where branchname=@branchname");
                //cmd.Parameters.Add("@whcode", dr["Center Code"].ToString());
                //cmd.Parameters.Add("@branchname", branchname);
                ////cmd.Parameters.Add("@bin", dr["Bin No"].ToString());
                //vdm.Update(cmd);
                i++;
            }
            lblmsg.Text = i + "Records updated successfully";
        }
        catch
        {
        }
    }
Beispiel #5
0
    protected void login_click(object sender, EventArgs e)
    {
        try
        {
            vdm = new SalesDBManager();
            string userid = Usernme_txt.Text, password = Pass_pas.Text;
            cmd = new SqlCommand("SELECT branchmaster.gstin, branchmaster.branchledgername, branchmapping.mainbranch, employe_details.sno, employe_details.loginflag, employe_details.employename, employe_details.userid, employe_details.password, employe_details.emailid, employe_details.phone, employe_details.branchtype, employe_details.leveltype, employe_details.departmentid, employe_details.branchid, branchmaster.branchid AS Expr1, branchmaster.branchname, branchmaster.address, branchmaster.branchcode, branchmaster.phone AS Expr2, branchmaster.tino, branchmaster.stno, branchmaster.cstno, branchmaster.emailid AS Expr3, branchmaster.statename FROM employe_details INNER JOIN branchmaster ON employe_details.branchid = branchmaster.branchid INNER JOIN departmentmaster ON departmentmaster.sno=employe_details.departmentid INNER JOIN branchmapping ON branchmaster.branchid=branchmapping.subbranch WHERE (employe_details.userid = @userid) AND (employe_details.password = @pwd)");
            cmd.Parameters.Add("@pwd", password);
            cmd.Parameters.Add("@userid", userid);
            DataTable dt = vdm.SelectQuery(cmd).Tables[0];
            if (dt.Rows.Count > 0)
            {
                string loginflag = dt.Rows[0]["loginflag"].ToString();
                //if (loginflag == "False")
                //{
                string sno = dt.Rows[0]["sno"].ToString();
                cmd = new SqlCommand("update employe_details set loginflag=@log where sno=@sno");
                cmd.Parameters.Add("@log", "1");
                cmd.Parameters.Add("@sno", sno);
                vdm.Update(cmd);
                Session["TinNo"]       = "37921042267";
                Session["mainbranch"]  = dt.Rows[0]["mainbranch"].ToString();
                Session["Employ_Sno"]  = dt.Rows[0]["sno"].ToString();
                Session["Po_BranchID"] = dt.Rows[0]["branchid"].ToString();
                Session["stateid"]     = dt.Rows[0]["statename"].ToString();
                Session["TitleName"]   = "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
                string   julydt = "07/01/2017 12:00:00 AM";
                DateTime gst_dt = Convert.ToDateTime(julydt);
                DateTime today  = DateTime.Today;
                //if (today > gst_dt)
                //{
                Session["Address"] = dt.Rows[0]["address"].ToString();
                Session["gstin"]   = dt.Rows[0]["gstin"].ToString();
                //}
                //else
                //{
                //    Session["Address"] = "Survey No. 381-2, Punabaka Village, Pellakuru mandal SPSR Nellore (Dt) Pin - 524129, Andhra Pradesh,11. Email : [email protected] Phone: 7729995606; GSTIN NO: 37921042267.";
                //   // Session["Address"] = "Survey No. 381-2, Punabaka Village, Pellakuru mandal SPSR Nellore (Dt) Pin - 524129.Couriering address : No.45, Madhu apartments,Panagal-517640,Srikalahasthi,Chittoor(dt),AndhraPradesh. Email : [email protected];[email protected] Phone: 7729995606,7729995603,9382525913; GSTIN: 37921042267."; //dt.Rows[0]["address"].ToString();
                //}
                Session["BranchCode"]       = dt.Rows[0]["branchcode"].ToString();
                Session["TinNo"]            = "37921042267";
                Session["stno"]             = dt.Rows[0]["stno"].ToString();
                Session["cstno"]            = dt.Rows[0]["cstno"].ToString();
                Session["phone"]            = dt.Rows[0]["phone"].ToString();
                Session["emailid"]          = dt.Rows[0]["emailid"].ToString();
                Session["UserName"]         = dt.Rows[0]["employename"].ToString();
                Session["password"]         = dt.Rows[0]["password"].ToString();
                Session["BranchType"]       = dt.Rows[0]["branchtype"].ToString();
                Session["Department"]       = dt.Rows[0]["departmentid"].ToString();
                Session["leveltype"]        = dt.Rows[0]["leveltype"].ToString();
                Session["branchledgername"] = dt.Rows[0]["branchledgername"].ToString();


                string branchtype = dt.Rows[0]["BranchType"].ToString();
                string leveltype  = dt.Rows[0]["leveltype"].ToString();



                Response.Cookies["UserName"].Value   = HttpUtility.UrlEncode("true");
                Response.Cookies["UserName"].Path    = "/";
                Response.Cookies["UserName"].Expires = DateTime.Now.AddDays(1);

                Response.Cookies["Employ_Sno"].Value   = HttpUtility.UrlEncode("true");
                Response.Cookies["Employ_Sno"].Path    = "/";
                Response.Cookies["Employ_Sno"].Expires = DateTime.Now.AddDays(1);

                //get ip address and device type
                string ipaddress;
                ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
                if (ipaddress == "" || ipaddress == null)
                {
                    ipaddress = Request.ServerVariables["REMOTE_ADDR"];
                }
                DateTime ServerDateCurrentdate  = SalesDBManager.GetTime(vdm.conn);
                HttpBrowserCapabilities browser = Request.Browser;
                string devicetype  = "";
                string userAgent   = Request.ServerVariables["HTTP_USER_AGENT"];
                Regex  OS          = new Regex(@"(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows ce|xda|xiino", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                Regex  device      = new Regex(@"1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                string device_info = string.Empty;
                if (OS.IsMatch(userAgent))
                {
                    device_info = OS.Match(userAgent).Groups[0].Value;
                }
                if (device.IsMatch(userAgent.Substring(0, 4)))
                {
                    device_info += device.Match(userAgent).Groups[0].Value;
                }
                if (!string.IsNullOrEmpty(device_info))
                {
                    devicetype = device_info;
                    string[] words = devicetype.Split(')');
                    devicetype = words[0].ToString();
                }
                else
                {
                    devicetype = "Desktop";
                }


                //string alphabets = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                //string small_alphabets = "abcdefghijklmnopqrstuvwxyz";
                //string numbers = "1234567890";

                //string characters = numbers;
                //characters += alphabets + small_alphabets + numbers;
                //int length = 8;
                //string otp = string.Empty;
                //for (int i = 0; i < length; i++)
                //{
                //    string character = string.Empty;
                //    do
                //    {
                //        int index = new Random().Next(0, characters.Length);
                //        character = characters.ToCharArray()[index].ToString();
                //    } while (otp.IndexOf(character) != -1);
                //    otp += character;
                //}
                cmd = new SqlCommand("INSERT INTO logininfo(userid, username, logintime, ipaddress, devicetype) values (@userid, @UserName, @logintime, @ipaddress, @device)");
                cmd.Parameters.Add("@userid", dt.Rows[0]["sno"].ToString());
                cmd.Parameters.Add("@UserName", Session["UserName"]);
                cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
                cmd.Parameters.Add("@ipaddress", ipaddress);
                cmd.Parameters.Add("@device", devicetype);
                //cmd.Parameters.Add("@otp", otp);
                vdm.insert(cmd);
                // Session["leveltype"] = "Admin";
                if (leveltype == "Admin     ")
                {
                    Response.Redirect("chartdashboard.aspx", false);
                }
                if (leveltype == "SuperAdmin")
                {
                    Response.Redirect("chartdashboard.aspx", false);
                }
                if (leveltype == "User      ")
                {
                    Response.Redirect("InwardReport.aspx", false);
                }
                if (leveltype == "Operations")
                {
                    Response.Redirect("PoDashBoard.aspx", false);
                }
                if (leveltype == "Issue     ")
                {
                    Response.Redirect("IssueDashBoard.aspx", false);
                }
                if (leveltype == "Receipt   ")
                {
                    Response.Redirect("InwardDashboard.aspx", false);
                }
                if (leveltype == "Section   ")
                {
                    Response.Redirect("IndentEntry.aspx", false);
                }
                //}
                //else
                //{
                //    lblMsg.Text = "Already Some one Login With This User Name";
                //}
            }
            else
            {
                lblMsg.Text = "Invalid userId and Password";
            }
        }
        catch (Exception ex)
        {
            lblMsg.Text = ex.Message;
        }
    }
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        try
        {
            vdm = new SalesDBManager();
            string totIOU = lblIou.Text;
            //string DenCash = Session["Cash"].ToString();
            double IOU = 0;
            double.TryParse(totIOU, out IOU);
            //double Cash = 0;
            //double.TryParse(DenCash, out Cash);
            // double TotalAmount = 0;
            double Totalclosing = 0;
            //TotalAmount = Cash + IOU;
            //double diffamount = 0;
            //double.TryParse(lblDiffernce.Text, out diffamount);
            double.TryParse(lblhidden.Text, out Totalclosing);

            DataTable dt = (DataTable)Session["IOUReport"];
            lblmsg.Text = "";
            DateTime fromdate  = new DateTime();
            string[] datestrig = txtFromdate.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);
                }
            }
            fromdate = fromdate;
            DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);

            string DenominationString = Session["DenominationString"].ToString();
            cmd = new SqlCommand("SELECT BranchID FROM  Collections WHERE (BranchId = @BranchId) AND (PaidDate BETWEEN @d1 AND @d2)");
            cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", GetHighDate(fromdate));
            DataTable dtCol = vdm.SelectQuery(cmd).Tables[0];
            if (dtCol.Rows.Count > 0)
            {
                lblmsg.Text = "Cash Book already closed";
            }
            else
            {
                /////////////////....................Ravindra.....................////////////////
                //cmd = new SqlCommand("SELECT cashpayables.Sno, cashpayables.BranchID, cashpayables.CashTo,subpayable.HeadSno, cashpayables.DOE, cashpayables.VocherID, cashpayables.Remarks, SUM(cashpayables.ApprovedAmount) AS Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.Status='P') AND (cashpayables.VoucherType = 'Credit') GROUP BY accountheads.Sno ORDER BY accountheads.HeadName");
                cmd = new SqlCommand("SELECT cashpayables.Sno, cashpayables.BranchID, cashpayables.CashTo, subpayable.HeadSno, cashpayables.DOE, cashpayables.VocherID, cashpayables.Remarks, SUM(cashpayables.ApprovedAmount) AS Amount, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.Status = 'P') AND (cashpayables.VoucherType = 'Credit') GROUP BY cashpayables.Sno, cashpayables.BranchID, cashpayables.CashTo, subpayable.HeadSno, cashpayables.DOE, cashpayables.VocherID, cashpayables.Remarks,  cashpayables.Amount, accountheads.HeadName ORDER BY accountheads.HeadName");
                cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
                DataTable dtCredit = vdm.SelectQuery(cmd).Tables[0];
                /////////////////....................Ravindra.....................////////////////
                //cmd = new SqlCommand("SELECT cashpayables.onNameof,cashpayables.CashTo, SUM(cashpayables.ApprovedAmount) AS Amount, subpayable.HeadSno, accountheads.HeadName FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.VoucherType = 'Due') AND (cashpayables.Status<> 'C') AND (cashpayables.Status='P') GROUP BY  accountheads.HeadName ORDER BY accountheads.HeadName");
                cmd = new SqlCommand("SELECT cashpayables.onNameof, cashpayables.CashTo, SUM(cashpayables.ApprovedAmount) AS Amount, subpayable.HeadSno, accountheads.HeadName  FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.VoucherType = 'Due') AND (cashpayables.Status <> 'C') AND (cashpayables.Status = 'P') GROUP BY cashpayables.onNameof, cashpayables.CashTo, cashpayables.Amount, subpayable.HeadSno, accountheads.HeadName ORDER BY accountheads.HeadName");
                cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
                DataTable dtDebit = vdm.SelectQuery(cmd).Tables[0];
                foreach (DataRow dr in dtDebit.Rows)
                {
                    string IouName      = dr["HeadName"].ToString();
                    double iouamtdebit  = 0;
                    double iouamtcredit = 0;
                    double TotIouBal    = 0;
                    double.TryParse(dr["Amount"].ToString(), out iouamtdebit);
                    foreach (DataRow drcredit in dtCredit.Select("HeadSno='" + dr["HeadSno"].ToString() + "'"))
                    {
                        double.TryParse(drcredit["Amount"].ToString(), out iouamtcredit);
                    }
                    TotIouBal = iouamtdebit - iouamtcredit;
                    if (TotIouBal == 0)
                    {
                    }
                    else
                    {
                        cmd = new SqlCommand("Insert into ioutable (BranchID,IOU,Amount,DOE) values(@BranchID,@IOU,@Amount,@DOE)");
                        cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
                        cmd.Parameters.Add("@Amount", TotIouBal);
                        cmd.Parameters.Add("@IOU", IouName);
                        cmd.Parameters.Add("@DOE", fromdate);
                        vdm.insert(cmd);
                    }
                }
                cmd = new SqlCommand("Insert into Collections (BranchID,AmountPaid,UserData_sno,PaidDate,PaymentType,Denominations,EmpID,VarifyDate) values(@BranchID,@AmountPaid,@UserData_sno,@PaidDate,@PaymentType,@Denominations,@EmpID,@VarifyDate)");
                cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
                //cmd.Parameters.Add("@AmountPaid", TotalAmount);
                cmd.Parameters.Add("@AmountPaid", Math.Round(Totalclosing, 2));
                cmd.Parameters.Add("@Denominations", DenominationString);
                cmd.Parameters.Add("@UserData_sno", "1");
                cmd.Parameters.Add("@PaidDate", fromdate);
                cmd.Parameters.Add("@VarifyDate", ServerDateCurrentdate);
                cmd.Parameters.Add("@PaymentType", "Cash");
                cmd.Parameters.Add("@EmpID", Session["UserSno"].ToString());
                vdm.insert(cmd);
                lblmsg.Text = "Cash Book saved successfully";
                GetReport();
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.Message;
        }
    }
Beispiel #7
0
 protected void login_click(object sender, EventArgs e)
 {
     try
     {
         username           = Usernme_txt.Text;
         password           = Pass_pas.Text;
         lbl_username.Text  = username;
         lbl_passwords.Text = password;
         DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
         //cmd = new SqlCommand("SELECT employee_erp.sno, employee_erp.leveltype,employee_erp.loginstatus, branch_info.venorid, employee_erp.empname, employee_erp.deptid, employee_erp.username, employee_erp.passward, employee_erp.emptype, employee_erp.branchid, branch_info.branchtype,branch_info.address,branch_info.branchname,branch_info.tinno FROM employee_erp INNER JOIN branch_info ON employee_erp.branchid = branch_info.sno WHERE  (employee_erp.username = @UN) AND (employee_erp.passward = @Pwd)");
         cmd = new SqlCommand("SELECT employee_erp.sno, employee_erp.leveltype,employee_erp.loginstatus, branch_info.venorid, branch_info.branchcode, employee_erp.empname, employee_erp.deptid, employee_erp.username, employee_erp.passward, employee_erp.emptype, employee_erp.branchid, branch_info.branchtype,branch_info.address,branch_info.branchname,branch_info.tinno, employee_erp.phoneno, employee_erp.otpstatus,employee_erp.empid FROM employee_erp INNER JOIN branch_info ON employee_erp.branchid = branch_info.sno WHERE  (employee_erp.username = @UN) AND (employee_erp.passward = @Pwd)");
         cmd.Parameters.Add("@Pwd", password);
         cmd.Parameters.Add("@UN", username);
         DataTable dt = vdm.SelectQuery(cmd).Tables[0];
         if (dt.Rows.Count > 0)
         {
             string otpstatus = dt.Rows[0]["otpstatus"].ToString();
             if (otpstatus == "1")
             {
                 //session
                 string sno = dt.Rows[0]["sno"].ToString();
                 cmd = new SqlCommand("update employee_erp set loginstatus=@log where sno=@sno");
                 cmd.Parameters.Add("@log", "1");
                 cmd.Parameters.Add("@sno", sno);
                 vdm.Update(cmd);
                 Session["TitleName"]  = dt.Rows[0]["branchname"].ToString(); // "SRI VYSHNAVI DAIRY SPECIALITIES (P) LTD";
                 Session["TinNo"]      = dt.Rows[0]["tinno"].ToString();
                 Session["DeptID"]     = dt.Rows[0]["deptid"].ToString();
                 Session["Employ_Sno"] = dt.Rows[0]["sno"].ToString();
                 Session["Branch_ID"]  = dt.Rows[0]["branchid"].ToString();
                 Session["Emp_Type"]   = dt.Rows[0]["emptype"].ToString();
                 Session["Address"]    = dt.Rows[0]["address"].ToString(); //"R.S.No:381/2,Punabaka village Post,Pellakuru Mandal,Nellore District -524129., ANDRAPRADESH (State).Phone: 9440622077, Fax: 044 – 26177799. ";// dt.Rows[0]["brnch_address"].ToString();
                 Session["BranchType"] = dt.Rows[0]["branchtype"].ToString();
                 Session["leveltype"]  = dt.Rows[0]["leveltype"].ToString();
                 Session["UserName"]   = dt.Rows[0]["empname"].ToString();
                 Session["VendorID"]   = dt.Rows[0]["venorid"].ToString();
                 Session["loginflag"]  = dt.Rows[0]["loginstatus"].ToString();
                 Session["branchcode"] = dt.Rows[0]["branchcode"].ToString();
                 Session["HRMS_EmpId"] = dt.Rows[0]["empid"].ToString();
                 string hostName = Dns.GetHostName(); // Retrive the Name of HOST
                 //get ip address and device type
                 ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
                 if (ipaddress == "" || ipaddress == null)
                 {
                     ipaddress = Request.ServerVariables["REMOTE_ADDR"];
                 }
                 HttpBrowserCapabilities browser = Request.Browser;
                 string devicetype  = "";
                 string userAgent   = Request.ServerVariables["HTTP_USER_AGENT"];
                 Regex  OS          = new Regex(@"(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows ce|xda|xiino", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                 Regex  device      = new Regex(@"1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                 string device_info = string.Empty;
                 if (OS.IsMatch(userAgent))
                 {
                     device_info = OS.Match(userAgent).Groups[0].Value;
                 }
                 if (device.IsMatch(userAgent.Substring(0, 4)))
                 {
                     device_info += device.Match(userAgent).Groups[0].Value;
                 }
                 if (!string.IsNullOrEmpty(device_info))
                 {
                     devicetype = device_info;
                     string[] words = devicetype.Split(')');
                     devicetype = words[0].ToString();
                 }
                 else
                 {
                     devicetype = "Desktop";
                 }
                 cmd = new SqlCommand("INSERT INTO logininfo(UserId, UserName, Logintime, IpAddress,devicetype,status) values (@userid, @UserName, @logintime, @ipaddress,@devicetype,@status)");
                 cmd.Parameters.Add("@userid", dt.Rows[0]["sno"].ToString());
                 cmd.Parameters.Add("@UserName", Session["UserName"]);
                 cmd.Parameters.Add("@logintime", ServerDateCurrentdate);
                 cmd.Parameters.Add("@ipaddress", ipaddress);
                 cmd.Parameters.Add("@devicetype", devicetype);
                 cmd.Parameters.Add("@status", "1");
                 vdm.insert(cmd);
                 //End
                 //otp
                 string Id         = string.Empty;
                 string no         = dt.Rows[0]["phoneno"].ToString();
                 string empid      = dt.Rows[0]["sno"].ToString();
                 string numbers    = "1234567890";
                 string characters = numbers;
                 int    length     = 6;
                 string otp        = string.Empty;
                 for (int i = 0; i < length; i++)
                 {
                     string character = string.Empty;
                     do
                     {
                         int index = new Random().Next(0, characters.Length);
                         character = characters.ToCharArray()[index].ToString();
                     } while (otp.IndexOf(character) != -1);
                     otp += character;
                 }
                 DateTime sdt        = SalesDBManager.GetTime(vdm.conn);
                 int      h          = Convert.ToInt32(sdt.ToString("HH"));
                 int      m          = 0;
                 string   otpexptime = string.Empty;
                 string   sss        = string.Empty;
                 string   mm         = string.Empty;
                 m = Convert.ToInt32(sdt.ToString("mm")) + 3;
                 int ss = Convert.ToInt32(sdt.ToString("ss"));
                 if (ss > 60)
                 {
                     ss = ss - 60;
                 }
                 if (ss < 10)
                 {
                     sss = "0" + m.ToString();
                 }
                 if (m > 60)
                 {
                     m = m - 60;
                 }
                 if (m < 10)
                 {
                     if (ss < 10)
                     {
                         mm         = "0" + m.ToString();
                         otpexptime = h.ToString() + ":" + mm.ToString() + ":" + sss.ToString();
                     }
                     else
                     {
                         mm         = m.ToString();
                         otpexptime = h.ToString() + ":" + mm.ToString() + ":" + ss.ToString();
                     }
                 }
                 else
                 {
                     if (ss < 10)
                     {
                         otpexptime = h.ToString() + ":" + m.ToString() + ":" + sss.ToString();
                     }
                     else
                     {
                         otpexptime = h.ToString() + ":" + m.ToString() + ":" + ss.ToString();
                     }
                 }
                 Otpupdate(no, otp, otpexptime, empid);
                 Id = Encrypt(no.Trim());
                 string hyperlink = "otp.aspx?Id=" + Id.Trim();
                 string message1  = "OTP for  " + empid + "  Login : "******". Valid till " + otpexptime + "  Do not share OTP for security reasons.";
                 string strUrl    = "http://123.63.33.43/blank/sms/user/urlsms.php?username=vyshnavidairy&pass=vyshnavi@123&senderid=VYSAKG&dest_mobileno=" + no + "&message=" + message1 + "&response=Y";
                 //string strUrl = "http://103.16.101.52:8080/sendsms/bulksms?username=kapd-vyshnavi&password=vysavi&type=0&dlr=1&destination=" + no + "&source=VYSHRM&message=" + message1 + "";
                 WebRequest      request    = HttpWebRequest.Create(strUrl);
                 HttpWebResponse response   = (HttpWebResponse)request.GetResponse();
                 Stream          s          = (Stream)response.GetResponseStream();
                 StreamReader    readStream = new StreamReader(s);
                 string          dataString = readStream.ReadToEnd();
                 response.Close();
                 s.Close();
                 readStream.Close();
                 string msg = hyperlink;
                 Response.Redirect("otp.aspx?Id=" + Id.Trim());
             }
             else
             {
                 fill_login_details();
             }
         }
         else
         {
             lbl_validation.Text = "Check Your Username and password";
         }
     }
     catch (Exception ex)
     {
         lbl_validation.Text = ex.Message;
     }
 }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable dtmiss = new DataTable();
            dtmiss.Columns.Add("Itemcode");
            dtmiss.Columns.Add("productname");
            DataTable dt = (DataTable)Session["btnImport"];
            int       i  = 1;
            foreach (DataRow dr in dt.Rows)
            {
                vdm = new SalesDBManager();
                //string productid = dr["productid"].ToString();
                //string qty = dr["qty"].ToString();
                //string price = dr["price"].ToString();
                //string branchid = dr["branchid"].ToString();
                //string minstock = dr["minstock"].ToString();
                //string maxsstock = dr["maxsstock"].ToString();


                string itemname      = dr["SWEETNAME"].ToString();
                string BILLINGPRICE  = dr["MRP"].ToString();
                string MRP           = dr["MRP"].ToString();
                string Categoryid    = dr["Categoryid"].ToString();
                string Subcategoryid = dr["Subcategoryid"].ToString();
                string UOM           = dr["uom"].ToString();
                string GST           = dr["GST"].ToString();
                string IGST          = dr["IGST"].ToString();
                string CGST          = dr["CGST"].ToString();
                string SGST          = dr["SGST"].ToString();
                cmd = new SqlCommand("insert into productmaster(subcategoryid, categoryid, productname, price, billingprice, uim, gsttaxcategory, igst, cgst, sgst,status) values (@subcategoryid, @categoryid, @productname, @price, @billingprice, @uim, @gsttaxcategory, @igst, @cgst, @sgst, @status)");
                cmd.Parameters.Add("@subcategoryid", Subcategoryid);
                cmd.Parameters.Add("@categoryid", Categoryid);
                cmd.Parameters.Add("@productname", itemname);
                cmd.Parameters.Add("@price", MRP);
                cmd.Parameters.Add("@billingprice", BILLINGPRICE);
                cmd.Parameters.Add("@uim", UOM);
                cmd.Parameters.Add("@gsttaxcategory", GST);
                cmd.Parameters.Add("@igst", IGST);
                cmd.Parameters.Add("@cgst", CGST);
                cmd.Parameters.Add("@sgst", SGST);
                cmd.Parameters.Add("@status", "Active");
                vdm.insert(cmd);

                cmd = new SqlCommand("select MAX(productid) as productid from productmaster");
                DataTable dtproduct = vdm.SelectQuery(cmd).Tables[0];
                string    productid = dtproduct.Rows[0]["productid"].ToString();

                cmd = new SqlCommand("insert into productmoniter( productid, qty, price, branchid, minstock, maxsstock) values (@productid, @mqty, @mprice,@mbranchid,@minstock,@maxstock)");
                cmd.Parameters.Add("@productid", productid);
                cmd.Parameters.Add("@mqty", "0");
                cmd.Parameters.Add("@mprice", MRP);
                cmd.Parameters.Add("@mbranchid", "1");
                cmd.Parameters.Add("@minstock", "1");
                cmd.Parameters.Add("@maxstock", "100");
                vdm.insert(cmd);

                cmd = new SqlCommand("insert into productmoniter( productid, qty, price, branchid, minstock, maxsstock) values (@productid, @mqty, @mprice,@mbranchid,@minstock,@maxstock)");
                cmd.Parameters.Add("@productid", productid);
                cmd.Parameters.Add("@mqty", "0");
                cmd.Parameters.Add("@mprice", MRP);
                cmd.Parameters.Add("@mbranchid", "2");
                cmd.Parameters.Add("@minstock", "1");
                cmd.Parameters.Add("@maxstock", "100");
                vdm.insert(cmd);

                cmd = new SqlCommand("insert into productmoniter( productid, qty, price, branchid, minstock, maxsstock) values (@productid, @mqty, @mprice,@mbranchid,@minstock,@maxstock)");
                cmd.Parameters.Add("@productid", productid);
                cmd.Parameters.Add("@mqty", "0");
                cmd.Parameters.Add("@mprice", MRP);
                cmd.Parameters.Add("@mbranchid", "3");
                cmd.Parameters.Add("@minstock", "1");
                cmd.Parameters.Add("@maxstock", "100");
                vdm.insert(cmd);

                cmd = new SqlCommand("insert into productmoniter( productid, qty, price, branchid, minstock, maxsstock) values (@productid, @mqty, @mprice,@mbranchid,@minstock,@maxstock)");
                cmd.Parameters.Add("@productid", productid);
                cmd.Parameters.Add("@mqty", "0");
                cmd.Parameters.Add("@mprice", MRP);
                cmd.Parameters.Add("@mbranchid", "4");
                cmd.Parameters.Add("@minstock", "1");
                cmd.Parameters.Add("@maxstock", "100");
                vdm.insert(cmd);

                i++;
            }
            grdmiss.DataSource = dtmiss;
            grdmiss.DataBind();
            Session["xportdata"] = dtmiss;
            Session["filename"]  = "Branch Transfer report";
        }
        catch
        {
        }
        lblmsg.Text = "Records inserted successfully";
    }