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();
    }
示例#2
0
    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;
        }
    }
示例#4
0
 protected void sessionsclick_click(object sender, EventArgs e)
 {
     try
     {
         this.AlertBox.Visible = false;
         string   username = lbl_username.Text.ToString();
         string   password = lbl_passwords.Text.ToString();
         DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
         cmd = new SqlCommand("update employee_erp set loginstatus=@log where username=@username and passward=@passward");
         cmd.Parameters.Add("@log", "0");
         cmd.Parameters.Add("@username", username);
         cmd.Parameters.Add("@passward", password);
         vdm.Update(cmd);
         cmd = new SqlCommand("SELECT sno FROM employee_erp where username=@username and passward=@passward");
         cmd.Parameters.Add("@username", username);
         cmd.Parameters.Add("@passward", password);
         DataTable dtEMP = vdm.SelectQuery(cmd).Tables[0];
         if (dtEMP.Rows.Count > 0)
         {
             string empid = dtEMP.Rows[0]["sno"].ToString();
             cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
             cmd.Parameters.Add("@userid", empid);
             DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
             if (dttime.Rows.Count > 0)
             {
                 string transno = dttime.Rows[0]["transno"].ToString();
                 cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime,status=@status where sno=@sno");
                 cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
                 cmd.Parameters.Add("@status", "0");
                 cmd.Parameters.Add("@sno", transno);
                 vdm.Update(cmd);
             }
         }
         this.AlertBox.Visible = false;
         fill_login_details();
     }
     catch
     {
     }
 }
示例#5
0
 protected void btnSave_Click(object sender, EventArgs e)
 {
     try
     {
         string         BranchID = Session["Branch_ID"].ToString();
         DataTable      dt       = (DataTable)Session["dtImport"];
         FleetDBManager fdm      = new FleetDBManager();
         vdm = new SalesDBManager();
         DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
         if (dt.Rows.Count > 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 try
                 {
                     string sno         = dr["sno"].ToString();
                     string tripsheetno = dr["tripsheetno"].ToString();
                     string dcost       = dr["DIESEL COST"].ToString();
                     mycmd = new MySqlCommand("UPDATE tripdata set DieselCost=@DieselCost where sno=@sno and tripsheetno=@tripsheetno");
                     mycmd.Parameters.Add("@DieselCost", dcost);
                     mycmd.Parameters.Add("@sno", sno);
                     mycmd.Parameters.Add("@tripsheetno", tripsheetno);
                     fdm.Update(mycmd);
                 }
                 catch (Exception ex)
                 {
                     throw ex;
                 }
                 //
             }
             lblmsg.Text = "uploded success";
         }
     }
     catch (Exception ex)
     {
         lblMessage.Text = ex.Message;
         lblmsg.Text     = ex.Message;
     }
 }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["Employ_Sno"] != null)
        {
            string sno = Session["Employ_Sno"].ToString();
            cmd = new SqlCommand("update employe_details set loginflag=@log where sno=@sno");
            cmd.Parameters.Add("@log", "0");
            cmd.Parameters.Add("@sno", sno);
            vdm.Update(cmd);

            DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
            cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid AND UserName=@UserName");
            cmd.Parameters.Add("@userid", Session["Employ_Sno"]);
            cmd.Parameters.Add("@UserName", Session["UserName"]);
            DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
            if (dttime.Rows.Count > 0)
            {
                string transno = dttime.Rows[0]["transno"].ToString();
                cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime where sno=@sno");
                cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
                cmd.Parameters.Add("@sno", transno);
                vdm.Update(cmd);
            }
        }
        else
        {
            cmd = new SqlCommand("update employe_details set loginflag=@log");
            cmd.Parameters.Add("@log", "0");
            vdm.Update(cmd);
        }
        Session.Clear();
        Session.RemoveAll();
        Session.Abandon();
        Response.Cookies["UserName"].Expires   = DateTime.Now.AddDays(-1);
        Response.Cookies["Employ_Sno"].Expires = DateTime.Now.AddDays(-1);
        Response.Redirect("Default.aspx");
    }
示例#7
0
    protected void Page_Load(object sender, EventArgs e)
    {
        DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);

        //cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
        //cmd.Parameters.Add("@userid", Session["Employ_Sno"]);
        //cmd.Parameters.Add("@UserName", Session["UserName"]);
        if (Session["Employ_Sno"] != "" || Session["Employ_Sno"] != null || Session["Employ_Sno"] != "undefined")
        {
            string sno = Session["Employ_Sno"].ToString();
            cmd = new SqlCommand("update employee_erp set loginstatus=@log where sno=@sno");
            cmd.Parameters.Add("@log", "0");
            cmd.Parameters.Add("@sno", sno);
            vdm.Update(cmd);

            cmd = new SqlCommand("Select max(sno) as transno from logininfo where UserId=@userid");
            cmd.Parameters.Add("@userid", sno);
            DataTable dttime = vdm.SelectQuery(cmd).Tables[0];
            if (dttime.Rows.Count > 0)
            {
                string transno = dttime.Rows[0]["transno"].ToString();
                cmd = new SqlCommand("UPDATE logininfo set logouttime=@logouttime,status=@status where sno=@sno");
                cmd.Parameters.Add("@logouttime", ServerDateCurrentdate);
                cmd.Parameters.Add("@status", "0");
                cmd.Parameters.Add("@sno", transno);
                vdm.Update(cmd);
            }
        }
        ExpireAllCookies();
        Session.Clear();
        Session.RemoveAll();
        Session.Abandon();
        //window.localStorage.clear();
        //ClearCache();
        //clearchachelocalall();
        Response.Redirect("login.aspx");
    }
示例#8
0
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        try
        {
            vdm = new SalesDBManager();
            DateTime    CreateDate    = SalesDBManager.GetTime(vdm.conn);
            SAPdbmanger SAPvdm        = new SAPdbmanger();
            DateTime    fromdate      = DateTime.Now;
            DataTable   dt            = (DataTable)Session["xportdata"];
            string[]    dateFromstrig = dtp_FromDate.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);
                }
            }
            foreach (DataRow dr in dt.Rows)
            {
                if (ddltype.SelectedItem.Value == "2")
                {
                    string AcctCode = dr["Ledger Code"].ToString();
                    string whCode   = dr["WH Code"].ToString();
                    if (AcctCode == "" && whCode == "")
                    {
                    }
                    else
                    {
                        sqlcmd = new SqlCommand("SELECT CreateDate, RefDate, DocDate, Ref1, Ref2, Ref3, TransNo, AcctCode FROM EMROJDT WHERE (RefDate BETWEEN @d1 AND @d2) AND (TransNo = @TransNo) AND (Ref1=@Ref1) AND (OcrCode = @OcrCode) AND (AcctCode=@AcCode)");
                        sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
                        sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
                        sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                        sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
                        sqlcmd.Parameters.Add("@AcCode", dr["Ledger Code"].ToString());
                        DataTable dtJournelPay = SAPvdm.SelectQuery(sqlcmd).Tables[0];
                        if (dtJournelPay.Rows.Count > 0)
                        {
                        }
                        else
                        {
                            sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
                            sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
                            sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
                            sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
                            sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
                            double amount = 0;
                            double.TryParse(dr["Total Amount"].ToString(), out amount);
                            double Debit = 0;
                            sqlcmd.Parameters.Add("@Debit", Debit);
                            sqlcmd.Parameters.Add("@Credit", amount);
                            string B1Upload  = "N";
                            string Processed = "N";
                            sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                            sqlcmd.Parameters.Add("@Processed", Processed);
                            sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                            sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
                            string series = "230";
                            sqlcmd.Parameters.Add("@series", series);
                            SAPvdm.insert(sqlcmd);


                            sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
                            sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
                            sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
                            sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
                            sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
                            double tamount = 0;
                            double.TryParse(dr["Total Amount"].ToString(), out tamount);
                            double Credit = 0;
                            sqlcmd.Parameters.Add("@Debit", tamount);
                            sqlcmd.Parameters.Add("@Credit", Credit);
                            sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                            sqlcmd.Parameters.Add("@Processed", Processed);
                            sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                            sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
                            sqlcmd.Parameters.Add("@series", series);
                            SAPvdm.insert(sqlcmd);
                        }
                    }
                }
                else
                {
                    double amount = 0;
                    double.TryParse(dr["Total Amount"].ToString(), out amount);
                    string B1Upload  = "N";
                    string Processed = "N";
                    sqlcmd = new SqlCommand("SELECT CreateDate, PostingDate, DocDate, ReferenceNo, ItemCode, ItemName FROM EMROIGE WHERE (PostingDate BETWEEN @d1 AND @d2) AND (ItemCode=@ItemCode) AND (ReferenceNo = @ReferenceNo) AND (WhsCode = @WhsCode)");
                    sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
                    sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
                    sqlcmd.Parameters.Add("@ReferenceNo", dr["JV No"].ToString());
                    sqlcmd.Parameters.Add("@WhsCode", dr["WH Code"].ToString());
                    sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
                    DataTable dtGoodsIssue = SAPvdm.SelectQuery(sqlcmd).Tables[0];
                    if (dtGoodsIssue.Rows.Count > 0)
                    {
                    }
                    else
                    {
                        sqlcmd = new SqlCommand("Insert into EMROIGE (CreateDate,PostingDate,DocDate,ReferenceNo,ItemCode,ItemName,Price,Quantity,WhsCode,OcrCode,OcrCode2,Remarks,B1Upload,Processed,series) values (@CreateDate,@PostingDate,@DocDate,@ReferenceNo,@ItemCode,@ItemName,@Price,@Quantity,@WhsCode,@OcrCode,@OcrCode2,@Remarks,@B1Upload,@Processed,@series)");
                        sqlcmd.Parameters.Add("@CreateDate", CreateDate);
                        sqlcmd.Parameters.Add("@PostingDate", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@DocDate", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@ReferenceNo", dr["JV No"].ToString());
                        sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
                        sqlcmd.Parameters.Add("@ItemName", dr["Item Name"].ToString());
                        sqlcmd.Parameters.Add("@Price", amount);
                        sqlcmd.Parameters.Add("@Quantity", dr["Qty"].ToString());
                        sqlcmd.Parameters.Add("@WhsCode", dr["WH Code"].ToString());
                        sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                        sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
                        sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
                        sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                        sqlcmd.Parameters.Add("@Processed", Processed);
                        string series = "240";
                        sqlcmd.Parameters.Add("@series", series);
                        SAPvdm.insert(sqlcmd);
                    }
                    string AcctCode = dr["Ledger Code"].ToString();
                    string whCode   = dr["WH Code"].ToString();
                    if (AcctCode == "" && whCode == "")
                    {
                    }
                    else
                    {
                        sqlcmd = new SqlCommand("SELECT CreateDate, RefDate, DocDate, Ref1, Ref2, Ref3, TransNo, AcctCode FROM EMROJDT WHERE (RefDate BETWEEN @d1 AND @d2) AND (TransNo = @TransNo) AND (Ref1=@Ref1) AND (OcrCode = @OcrCode)");
                        sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
                        sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
                        sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                        sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
                        DataTable dtJournelPay = SAPvdm.SelectQuery(sqlcmd).Tables[0];
                        if (dtJournelPay.Rows.Count > 0)
                        {
                        }
                        else
                        {
                            sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
                            sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
                            sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
                            sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
                            sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
                            double camount = 0;
                            double.TryParse(dr["Total Amount"].ToString(), out camount);
                            double Debit = 0;
                            sqlcmd.Parameters.Add("@Debit", Debit);
                            sqlcmd.Parameters.Add("@Credit", camount);
                            string cB1Upload  = "N";
                            string cProcessed = "N";
                            sqlcmd.Parameters.Add("@B1Upload", cB1Upload);
                            sqlcmd.Parameters.Add("@Processed", cProcessed);
                            sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                            sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
                            string series = "230";
                            sqlcmd.Parameters.Add("@series", series);
                            SAPvdm.insert(sqlcmd);


                            sqlcmd = new SqlCommand("Insert into EMROJDT (CreateDate, RefDate, DocDate, TransNo, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,OcrCode2,series) values (@CreateDate, @RefDate, @DocDate,@TransNo, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@OcrCode2,@series)");
                            sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@RefDate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@docdate", GetLowDate(fromdate));
                            sqlcmd.Parameters.Add("@Ref1", dr["Item Code"].ToString());
                            sqlcmd.Parameters.Add("@TransNo", dr["JV No"].ToString());
                            sqlcmd.Parameters.Add("@AcctCode", dr["Ledger Code"].ToString());
                            sqlcmd.Parameters.Add("@AcctName", dr["Ledger Name"].ToString());
                            double damount = 0;
                            double.TryParse(dr["Total Amount"].ToString(), out damount);
                            double Credit = 0;
                            sqlcmd.Parameters.Add("@Debit", damount);
                            sqlcmd.Parameters.Add("@Credit", Credit);
                            sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                            sqlcmd.Parameters.Add("@Processed", Processed);
                            sqlcmd.Parameters.Add("@OcrCode", dr["WH Code"].ToString());
                            sqlcmd.Parameters.Add("@OcrCode2", dr["Category Code"].ToString());
                            sqlcmd.Parameters.Add("@series", series);
                            SAPvdm.insert(sqlcmd);
                        }
                    }
                }
            }
            DataTable dtempty = new DataTable();
            grdReports.DataSource = dtempty;
            grdReports.DataBind();
            lblmsg.Text = "Successfully Saved";
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }
    protected void btn_Generate_Click(object sender, EventArgs e)
    {
        try
        {
            Report.Columns.Add("CREATE DATE");
            Report.Columns.Add("CARD CODE");
            Report.Columns.Add("CARD NAME");
            Report.Columns.Add("DISCOUNT %");
            Report.Columns.Add("REFERENCE NO");
            Report.Columns.Add("ITEMCODE");
            Report.Columns.Add("DESCRIPTION");
            Report.Columns.Add("WARE HOUSE CODE");
            Report.Columns.Add("QUANTITY");
            Report.Columns.Add("PRICE");
            Report.Columns.Add("TAX CODE");
            Report.Columns.Add("TAX%");
            Report.Columns.Add("TRANSPORT");
            Report.Columns.Add("LINE TOTAL");
            Report.Columns.Add("Narration");
            lblmsg.Text = "";
            SalesDBManager SalesDB   = new SalesDBManager();
            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);
                }
            }
            lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
            string   branchid    = Session["Po_BranchID"].ToString();
            DateTime ReportDate  = SalesDBManager.GetTime(SalesDB.conn);
            DateTime dtapril     = new DateTime();
            DateTime dtmarch     = new DateTime();
            int      currentyear = ReportDate.Year;
            int      nextyear    = ReportDate.Year + 1;
            if (ReportDate.Month > 3)
            {
                string apr = "4/1/" + currentyear;
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + nextyear;
                dtmarch = DateTime.Parse(march);
            }
            if (ReportDate.Month <= 3)
            {
                string apr = "4/1/" + (currentyear - 1);
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + (nextyear - 1);
                dtmarch = DateTime.Parse(march);
            }
            cmd = new SqlCommand("SELECT  suppliersdetails.name, suppliersdetails.suppliercode, inwarddetails.sno, inwarddetails.inwarddate, inwarddetails.invoiceno, inwarddetails.invoicedate,inwarddetails.dcno, inwarddetails.lrno, inwarddetails.supplierid, inwarddetails.podate, inwarddetails.doorno, inwarddetails.remarks, inwarddetails.pono, inwarddetails.inwardno, inwarddetails.transportname, inwarddetails.vehicleno, inwarddetails.modeofinward, inwarddetails.securityno, inwarddetails.uimid,inwarddetails.status, inwarddetails.mrnno, inwarddetails.indentno, inwarddetails.freigtamt, inwarddetails.transportcharge, inwarddetails.inwardamount,  subinwarddetails.sno AS Expr1, subinwarddetails.productid, subinwarddetails.quantity, subinwarddetails.perunit, subinwarddetails.totalcost, subinwarddetails.in_refno, subinwarddetails.status AS Expr2, subinwarddetails.taxtype, subinwarddetails.ed, subinwarddetails.dis, subinwarddetails.disamt, subinwarddetails.tax, subinwarddetails.edtax, productmaster.itemcode, productmaster.productname, taxmaster.type AS tax_type, taxmaster_1.type AS ed_type, inwarddetails.pfid, pandf.pandf, subinwarddetails.igst, subinwarddetails.cgst, subinwarddetails.sgst FROM inwarddetails INNER JOIN subinwarddetails ON inwarddetails.sno = subinwarddetails.in_refno INNER JOIN productmaster ON subinwarddetails.productid = productmaster.productid INNER JOIN suppliersdetails ON inwarddetails.supplierid = suppliersdetails.supplierid left outer JOIN taxmaster ON subinwarddetails.taxtype = taxmaster.sno left outer JOIN taxmaster AS taxmaster_1 ON subinwarddetails.ed = taxmaster_1.sno LEFT OUTER JOIN pandf ON inwarddetails.pfid = pandf.sno WHERE (inwarddetails.inwarddate BETWEEN @fromdate AND @todate) AND (inwarddetails.branchid = @branchid) AND (inwarddetails.mrnno IS NOT NULL) AND  (inwarddetails.mrnno <> 0)");
            cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
            cmd.Parameters.Add("@todate", GetHighDate(fromdate));
            cmd.Parameters.Add("@branchid", branchid);
            DataTable dttotalinward = vdm.SelectQuery(cmd).Tables[0];
            if (dttotalinward.Rows.Count > 0)
            {
                string   taxcode = "", taxtype = "", transport = "";
                double   ttotalamount = 0;
                DateTime dt           = DateTime.Now;
                string   prevdate     = string.Empty;
                int      i            = 0;
                string   PONo         = "";
                string   newpoNo      = "";
                foreach (DataRow dr in dttotalinward.Rows)
                {
                    DataRow  newrow         = Report.NewRow();
                    DateTime dt_inward      = Convert.ToDateTime(dr["inwarddate"].ToString());
                    string   dt_inward_date = dt_inward.ToString("dd/MM/yyyy");

                    DateTime invoicedate = Convert.ToDateTime(dr["invoicedate"].ToString());
                    string   indate      = invoicedate.ToString("dd/MM/yyyy");

                    newrow["CREATE DATE"] = dt_inward_date; //dr["inwarddate"].ToString();
                    newrow["CARD CODE"]   = dr["suppliercode"].ToString();
                    newrow["CARD NAME"]   = dr["name"].ToString();
                    string cardname = dr["name"].ToString();
                    newrow["DISCOUNT %"] = dr["dis"].ToString();
                    string newmrn  = "0";
                    int    countdc = 0;
                    int.TryParse(dr["mrnno"].ToString(), out countdc);
                    if (countdc < 10)
                    {
                        newmrn = "0000" + countdc;
                    }
                    if (countdc >= 10 && countdc <= 99)
                    {
                        newmrn = "000" + countdc;
                    }
                    if (countdc >= 99 && countdc <= 999)
                    {
                        newmrn = "00" + countdc;
                    }
                    if (countdc >= 999 && countdc <= 9999)
                    {
                        newmrn = "0" + countdc;
                    }
                    if (countdc > 9999)
                    {
                        newmrn = "" + countdc;
                    }
                    string BranchCode = Session["BranchCode"].ToString();
                    string invno      = dr["invoiceno"].ToString();
                    newrow["REFERENCE NO"] = BranchCode + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newmrn;
                    newrow["ITEMCODE"]     = dr["itemcode"].ToString();
                    newrow["DESCRIPTION"]  = dr["productname"].ToString();
                    string product = dr["productname"].ToString();
                    newrow["WARE HOUSE CODE"] = "SVDSPP02";
                    newrow["QUANTITY"]        = dr["quantity"].ToString();
                    double rate = 0;
                    double.TryParse(dr["perunit"].ToString(), out rate);
                    rate            = Math.Round(rate, 2);
                    newrow["PRICE"] = rate;
                    taxtype         = dr["tax_type"].ToString();
                    string edtype = dr["edtax"].ToString();
                    string IGST   = dr["igst"].ToString();
                    string CGST   = dr["cgst"].ToString();
                    string SGST   = dr["sgst"].ToString();
                    if (IGST != "0")
                    {
                        double igsttax = Convert.ToDouble(IGST);
                        taxcode        = "IGST" + IGST + "";
                        newrow["TAX%"] = IGST;
                    }
                    if (CGST != "0")
                    {
                        double cgsttax = Convert.ToDouble(CGST);
                        cgsttax        = cgsttax + cgsttax;
                        taxcode        = "CGST" + cgsttax + "";
                        newrow["TAX%"] = CGST;
                    }
                    if (IGST == "0" && CGST == "0")
                    {
                        taxcode        = "EXEMPT";
                        newrow["TAX%"] = "0";
                    }
                    //if (edtype == "0")
                    //{
                    //    if (taxtype == "CST 2% against Form C1" || taxtype == "Input CST @2%")
                    //    {
                    //        taxcode = "CST@2";
                    //    }
                    //    else if (taxtype == "Input CST @5%")
                    //    {
                    //        taxcode = "CST@5";
                    //    }
                    //    else if (taxtype == "Input VAT @14.5%")
                    //    {
                    //        taxcode = "[email protected]";
                    //    }
                    //    else if (taxtype == "Input VAT @5%" || taxtype == "vat 5%")
                    //    {
                    //        taxcode = "VAT@5";
                    //    }
                    //    else if (taxtype == "Input VAT @0%")
                    //    {
                    //        taxcode = "EXEMPT";
                    //    }
                    //    else if (taxtype == "CST @14.5%")
                    //    {
                    //        taxcode = "[email protected]";
                    //    }
                    //}
                    //else
                    //{
                    //    if (taxtype == "Input VAT @14.5%" && edtype == "12.5")
                    //    {
                    //        taxcode = "E125V145";
                    //    }
                    //    if (taxtype == "Input VAT @5%" && edtype == "12.5")
                    //    {
                    //        taxcode = "E12.5V5";
                    //    }
                    //    if ((taxtype == "CST 2% against Form C1" || taxtype == "Input CST @2%") && (edtype == "12.5"))
                    //    {
                    //        taxcode = "ED12.5C2";
                    //    }
                    //    if (taxtype == "Input VAT @0%" && edtype == "12.5")
                    //    {
                    //        taxcode = "[email protected]";
                    //    }
                    //}
                    newrow["TAX CODE"] = taxcode;
                    double price = 0;
                    double.TryParse(dr["perunit"].ToString(), out price);
                    double qty = 0;
                    double.TryParse(dr["quantity"].ToString(), out qty);
                    double total = 0;
                    double total_amt = 0, edamount = 0;
                    total = qty * price;
                    double dis = 0;
                    double.TryParse(dr["disamt"].ToString(), out dis);
                    total_amt = total - dis;


                    double pfamt = 0; double pfamount = 0;
                    if (dr["pandf"].ToString() != "")
                    {
                        double.TryParse(dr["pandf"].ToString(), out pfamt);
                        pfamount            = (total_amt * pfamt) / 100;
                        newrow["TRANSPORT"] = pfamount.ToString();
                    }
                    string edcode = "";
                    //string edtype = dr["edtax"].ToString();
                    //if (edtype == "12.5")
                    //{
                    //    edcode = "[email protected]";
                    //}
                    //else
                    //{
                    //    edcode = "EXEMPT";
                    //}
                    //newrow["ED CODE"] = edcode;
                    double edamt = 0;
                    double.TryParse(dr["edtax"].ToString(), out edamt);
                    edamount = (total_amt * edamt) / 100;
                    double totedamount = 0;
                    totedamount = total_amt + edamount + pfamount;
                    double vat_per = 0; double vat_amt = 0; double amt_tax = 0;
                    //double edamt = 0;
                    //double.TryParse(dr["edtax"].ToString(), out edamt);
                    //edamount = (total_amt * edamt) / 100;
                    //double totedamount = 0;
                    //totedamount = total_amt + edamount;//stock
                    double csttax = 0;

                    string trans_chrgs = dr["transportcharge"].ToString();
                    int    trans       = 0;
                    int.TryParse(trans_chrgs, out trans);
                    string freight_chrgs = dr["freigtamt"].ToString();
                    int    frigh         = 0;
                    int.TryParse(freight_chrgs, out frigh);

                    if (trans != 0)
                    {
                        transport = trans_chrgs;
                    }
                    else if (frigh != 0)
                    {
                        transport = freight_chrgs;
                    }
                    else
                    {
                        transport = "0";
                    }

                    string val = newmrn;
                    if (val == tempval)
                    {
                        double tamount = Convert.ToDouble(transport);
                        transport = "0";
                        tempval   = val;
                    }
                    else
                    {
                        if (transport == "0")
                        {
                            transport = "0";
                        }
                        else
                        {
                            newrow["TRANSPORT"] = transport;
                        }
                        tempval = val;
                    }
                    amt_tax = totedamount + vat_amt + Convert.ToDouble(transport);
                    newrow["LINE TOTAL"] = amt_tax.ToString("F2");
                    ttotalamount        += amt_tax;
                    string narration = "Being the purchase of " + product + ", Qty:" + qty + ", Amount:" + amt_tax + "/-. From Invoice Name :" + cardname + ". Invoice No:" + invno + ", Invoice Date:" + indate + ",MRN No:" + newmrn + ", MRN Date:" + dt_inward_date + ", taxtype:" + taxtype + "";
                    newrow["Narration"] = narration;
                    Report.Rows.Add(newrow);
                    i++;
                }
                Session["xportdata"]  = Report;
                grdReports.DataSource = Report;
                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;
        }
    }
示例#10
0
 protected void login_click(object sender, EventArgs e)
 {
     try
     {
         vdm = new SalesDBManager();
         string userid = txtusername.Text, password = txtpassword.Text;
         cmd = new SqlCommand("SELECT sno, employename, userid, password, emailid, phone, branchtype, leveltype, departmentid, branchid  FROM employe_details where userid=@username AND password=@pwd");
         cmd.Parameters.Add("@pwd", password);
         cmd.Parameters.Add("@username", userid);
         DataTable dt = vdm.SelectQuery(cmd).Tables[0];
         if (dt.Rows.Count > 0)
         {
             string sno = dt.Rows[0]["sno"].ToString();
             Session["Employ_Sno"]   = dt.Rows[0]["sno"].ToString();
             Session["BranchID"]     = dt.Rows[0]["branchid"].ToString();
             Session["UserName"]     = dt.Rows[0]["employename"].ToString();
             Session["posleveltype"] = dt.Rows[0]["leveltype"].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);
             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";
             }
             cmd = new SqlCommand("Select * from empbranchmapping Where empid=@sno");
             cmd.Parameters.Add("@sno", sno);
             DataTable dtemp = vdm.SelectQuery(cmd).Tables[0];
             if (dtemp.Rows.Count > 1)
             {
                 Response.Redirect("Switchaccounts.aspx", false);
             }
             else
             {
                 if (leveltype == "Admin     ")
                 {
                     Response.Redirect("Default.aspx", false);
                 }
                 if (leveltype == "SuperAdmin")
                 {
                     Response.Redirect("Default.aspx", false);
                 }
                 if (leveltype == "User      ")
                 {
                     Response.Redirect("vpos.aspx", false);
                 }
                 if (leveltype == "Manager   ")
                 {
                     Response.Redirect("Default.aspx", false);
                 }
                 if (leveltype == "Accounts  ")
                 {
                     Response.Redirect("Default.aspx", false);
                 }
                 if (leveltype == "Distibutor")
                 {
                     Response.Redirect("Distibuterindent.aspx", false);
                 }
                 if (leveltype == "Indent    ")
                 {
                     ValidateLogin();
                     Response.Redirect("Distibuterindent.aspx", false);
                 }
             }
         }
         else
         {
             lblMsg.Visible = true;
             lblMsg.Text    = "Invalid userId and Password";
         }
     }
     catch (Exception ex)
     {
         lblMsg.Text = ex.Message;
     }
 }
 protected void btn_save_click(object sender, EventArgs e)
 {
     try
     {
         vdm = new SalesDBManager();
         DateTime    CreateDate    = SalesDBManager.GetTime(vdm.conn);
         SAPdbmanger SAPvdm        = new SAPdbmanger();
         DateTime    fromdate      = DateTime.Now;
         DataTable   dt            = (DataTable)Session["xportdata"];
         string[]    dateFromstrig = dtp_FromDate.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);
             }
         }
         string ocrcode2 = "", remarks = "";
         foreach (DataRow dr in dt.Rows)
         {
             string itemcode = dr["ITEMCODE"].ToString();
             if (itemcode == "")
             {
             }
             else
             {
                 cmd = new SqlCommand("insert into EMROPDN (CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, ReferenceNo, ItemCode, Dscription, WhsCode, Quantity, Price, VAT_Percent, LineTotal, OcrCode, OcrCode2, REMARKS, B1Upload, Processed) values (@CreateDate, @CardCode, @CardName, @TaxDate, @DocDate, @DocDueDate, @ReferenceNo, @ItemCode, @Dscription, @WhsCode, @Quantity, @Price, @VAT_Percent, @LineTotal, @OcrCode, @OcrCode2, @REMARKS, @B1Upload, @Processed)");
                 cmd.Parameters.Add("@CreateDate", fromdate);
                 cmd.Parameters.Add("@CardCode", dr["CARD CODE"].ToString());
                 cmd.Parameters.Add("@CardName", dr["CARD NAME"].ToString());
                 cmd.Parameters.Add("@TaxDate", fromdate);
                 cmd.Parameters.Add("@DocDate", fromdate);
                 cmd.Parameters.Add("@DocDueDate", fromdate);
                 cmd.Parameters.Add("@ReferenceNo", dr["REFERENCE NO"].ToString());
                 cmd.Parameters.Add("@ItemCode", dr["ITEMCODE"].ToString());
                 cmd.Parameters.Add("@Dscription", dr["DESCRIPTION"].ToString());
                 cmd.Parameters.Add("@WhsCode", dr["WARE HOUSE CODE"].ToString());
                 double qty = 0;
                 double.TryParse(dr["QUANTITY"].ToString(), out qty);
                 cmd.Parameters.Add("@Quantity", qty);
                 double price = 0;
                 double.TryParse(dr["price"].ToString(), out price);
                 cmd.Parameters.Add("@Price", price);
                 double vatpercent = 0;
                 double.TryParse(dr["VAT PERCENT"].ToString(), out vatpercent);
                 cmd.Parameters.Add("@VAT_Percent", vatpercent);
                 double linetotal = 0;
                 double.TryParse(dr["LINE TOTAL"].ToString(), out linetotal);
                 cmd.Parameters.Add("@LineTotal", linetotal);
                 cmd.Parameters.Add("@OcrCode", dr["WARE HOUSE CODE"].ToString());
                 cmd.Parameters.Add("@OcrCode2", ocrcode2);
                 cmd.Parameters.Add("@REMARKS", remarks);
                 string B1Upload  = "N";
                 string Processed = "N";
                 cmd.Parameters.Add("@B1Upload", B1Upload);
                 cmd.Parameters.Add("@Processed", Processed);
                 SAPvdm.insert(cmd);
             }
         }
         lblmsg.Text = "Saved successfully";
         DataTable dtempty = new DataTable();
         grdReports.DataSource = dtempty;
         grdReports.DataBind();
     }
     catch (Exception ex)
     {
         lblmsg.Text       = ex.Message;
         hidepanel.Visible = false;
     }
 }
示例#12
0
    void GetReport()
    {
        try
        {
            lblmsg.Text          = "";
            pnlHide.Visible      = true;
            Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
            Session["IDate"]     = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
            vdm = new SalesDBManager();
            DateTime fromdate      = DateTime.Now;
            DateTime todate        = 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);
                }
            }
            dateFromstrig = dtp_Todate.Text.Split(' ');
            if (dateFromstrig.Length > 1)
            {
                if (dateFromstrig[0].Split('-').Length > 0)
                {
                    string[] dates = dateFromstrig[0].Split('-');
                    string[] times = dateFromstrig[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);
                }
            }
            DateTime ReportDate  = SalesDBManager.GetTime(vdm.conn);
            DateTime dtapril     = new DateTime();
            DateTime dtmarch     = new DateTime();
            int      currentyear = ReportDate.Year;
            int      nextyear    = ReportDate.Year + 1;
            if (ReportDate.Month > 3)
            {
                string apr = "4/1/" + currentyear;
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + nextyear;
                dtmarch = DateTime.Parse(march);
            }
            if (ReportDate.Month <= 3)
            {
                string apr = "4/1/" + (currentyear - 1);
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + (nextyear - 1);
                dtmarch = DateTime.Parse(march);
            }
            DataTable dtReport = new DataTable();
            dtReport.Columns.Add("Voucher Date");
            dtReport.Columns.Add("Voucher No");
            dtReport.Columns.Add("whcode");
            dtReport.Columns.Add("Voucher Type");
            dtReport.Columns.Add("Ledger Code");
            dtReport.Columns.Add("Ledger (Dr)");
            dtReport.Columns.Add("PaidDate");
            dtReport.Columns.Add("Customer Code");
            dtReport.Columns.Add("Ledger (Cr)");
            dtReport.Columns.Add("Amount");
            dtReport.Columns.Add("Narration");
            lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
            lblRoutName.Text     = ddlSalesOffice.SelectedItem.Text;
            Session["xporttype"] = "TallyReceipts";
            Session["filename"]  = ddlSalesOffice.SelectedItem.Text + " Tally Receipts" + fromdate.ToString("dd/MM/yyyy");
            cmd = new SqlCommand("SELECT cashreceipts.ReceivedFrom,cashreceipts.BranchId,Plant_Master.ladger_dr_code, cashreceipts.AgentID, cashreceipts.Empid, cashreceipts.Amountpayable, cashreceipts.AmountPaid, cashreceipts.DOE, cashreceipts.Remarks, cashreceipts.Receipt, cashreceipts.PaymentStatus, cashreceipts.TransactionType, cashreceipts.Sno, Plant_Master.Plant_Name,Plant_Master.WHcode, Plant_Master.ladger_dr FROM cashreceipts INNER JOIN Plant_Master ON cashreceipts.BranchId = Plant_Master.Plant_Code WHERE (cashreceipts.DOE BETWEEN @d1 AND @d2) AND (cashreceipts.BranchId = @Branchid)");
            cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", GetHighDate(todate));
            DataTable dtothers = vdm.SelectQuery(cmd).Tables[0];

            cmd = new SqlCommand("SELECT   cashcollections.LedgerCode,cashcollections.Branchid, Plant_Master.Plant_Name, cashcollections.Name, cashcollections.Amount, cashcollections.Remarks, cashcollections.DOE, cashcollections.Receiptno, cashcollections.PaymentType, cashcollections.CollectionType  FROM cashcollections INNER JOIN Plant_Master ON cashcollections.Branchid = Plant_Master.Plant_Code WHERE (cashcollections.DOE BETWEEN @d1 AND @d2) AND (cashcollections.Branchid = @Branchid)");
            cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", GetHighDate(todate));
            DataTable dtreceipts = vdm.SelectQuery(cmd).Tables[0];
            if (dtothers.Rows.Count > 0)
            {
                foreach (DataRow dr in dtothers.Rows)
                {
                    DataRow newrow    = dtReport.NewRow();
                    string  VoucherNo = dr["Receipt"].ToString();

                    string NewVoucherNo = "0";
                    int    countdc      = 0;
                    int.TryParse(VoucherNo, out countdc);
                    if (countdc <= 10)
                    {
                        NewVoucherNo = "000" + countdc;
                    }
                    if (countdc >= 10 && countdc <= 99)
                    {
                        NewVoucherNo = "00" + countdc;
                    }
                    if (countdc >= 99 && countdc <= 999)
                    {
                        NewVoucherNo = "0" + countdc;
                    }

                    string   Date      = dr["DOE"].ToString();
                    DateTime from_date = Convert.ToDateTime(Date);
                    newrow["Voucher Date"] = from_date.ToString("dd-MMM-yyyy");
                    newrow["Voucher No"]   = ddlSalesOffice.SelectedValue + dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
                    int i = 1;
                    newrow["PaidDate"] = from_date.ToString("dd-MMM-yyyy");
                    newrow["whcode"]   = dr["WHcode"].ToString();
                    double AmountPaid = 0;
                    double.TryParse(dr["AmountPaid"].ToString(), out AmountPaid);
                    newrow["Amount"]       = AmountPaid;
                    newrow["Voucher Date"] = from_date.ToString("dd-MMM-yyyy");
                    string Name = "";
                    foreach (DataRow drcash in dtreceipts.Select("Receiptno='" + dr["Receipt"].ToString() + "' and Branchid='" + dr["BranchId"].ToString() + "'"))
                    {
                        newrow["Voucher Type"] = drcash["CollectionType"].ToString();
                        newrow["Ledger (Cr)"]  = drcash["Name"].ToString();
                        Name = drcash["Name"].ToString();
                        //newrow["Customer Code"] = drcash["customercode"].ToString();
                        //getledger = newrow["Customer Code"].ToString();
                        newrow["Customer Code"] = drcash["LedgerCode"].ToString();
                    }
                    newrow["whcode"]      = dr["whcode"].ToString();
                    newrow["Ledger Code"] = dr["ladger_dr_code"].ToString();
                    newrow["Ledger (Dr)"] = dr["ladger_dr"].ToString();
                    double invval = 0;
                    newrow["Narration"] = "Being the cash receipt to " + Name + " vide Receipt No " + dr["Receipt"].ToString() + ",Receipt Date " + from_date.ToString("dd/MM/yyyy");
                    dtReport.Rows.Add(newrow);
                    i++;
                }
                grdReports.DataSource = dtReport;
                grdReports.DataBind();
                Session["xportdata"] = dtReport;
            }
            else
            {
                pnlHide.Visible       = false;
                lblmsg.Text           = "No Data Found";
                grdReports.DataSource = dtReport;
                grdReports.DataBind();
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text           = ex.Message;
            grdReports.DataSource = dtReport;
            grdReports.DataBind();
        }
    }
    void getdet()
    {
        try
        {
            vdm         = new SalesDBManager();
            lblmsg.Text = "";
            DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
            DateTime dtapril       = new DateTime();
            DateTime dtmarch       = new DateTime();
            int      currentyear   = ServerDateCurrentdate.Year;
            int      nextyear      = ServerDateCurrentdate.Year + 1;
            int      currntyearnum = 0;
            int      nextyearnum   = 0;
            if (ServerDateCurrentdate.Month > 3)
            {
                string apr = "4/1/" + currentyear;
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + nextyear;
                dtmarch       = DateTime.Parse(march);
                currntyearnum = currentyear;
                nextyearnum   = nextyear;
            }
            if (ServerDateCurrentdate.Month <= 3)
            {
                string apr = "4/1/" + (currentyear - 1);
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + (nextyear - 1);
                dtmarch       = DateTime.Parse(march);
                currntyearnum = currentyear - 1;
                nextyearnum   = nextyear - 1;
            }
            //cmd = new SqlCommand("SELECT sno, BranchName, BranchCode FROM branchdata WHERE (sno = @BranchID)");
            //cmd.Parameters.Add("@BranchID", Session["branch"]);
            //DataTable dtCode = vdm.SelectQuery(cmd).Tables[0];
            string Receiptid = "";
            cmd = new SqlCommand("SELECT CONVERT(VARCHAR(10), DOE, 103) AS DOE , Receiptno, Name, Amount,remarks,CollectionType,PaymentType  FROM cashcollections WHERE  (Sno = @Receipt)");
            cmd.Parameters.Add("@BranchID", Session["branch"]);
            cmd.Parameters.Add("@Receipt", txtReceiptNo.Text);
            DataTable dtReceiptBook = vdm.SelectQuery(cmd).Tables[0];
            if (dtReceiptBook.Rows.Count > 0)
            {
                Receiptid          = Session["branch"].ToString() + "/OTH_RCPT/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtReceiptBook.Rows[0]["Receiptno"].ToString();
                lblreceiptno.Text  = Receiptid;
                lblDate.Text       = dtReceiptBook.Rows[0]["DOE"].ToString();
                lblPayCash.Text    = dtReceiptBook.Rows[0]["Name"].ToString();
                lbltowards.Text    = dtReceiptBook.Rows[0]["PaymentType"].ToString();
                lblAmount.Text     = dtReceiptBook.Rows[0]["Amount"].ToString();
                lblCheque.Text     = dtReceiptBook.Rows[0]["CollectionType"].ToString();
                lblChequeDate.Text = dtReceiptBook.Rows[0]["DOE"].ToString();
                lblRemarks.Text    = dtReceiptBook.Rows[0]["remarks"].ToString();
            }
            string   Amont = lblAmount.Text;
            string[] Ones  = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };

            string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };

            int Num = int.Parse(Amont);
            lblRupess.Text = NumToWordBD(Num) + " Rupees Only";
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.Message;
        }
    }
示例#14
0
    protected void btn_Generate_Click(object sender, EventArgs e)
    {
        try
        {
            DataReport.Columns.Add("CREATE DATE");
            DataReport.Columns.Add("CARD CODE");
            DataReport.Columns.Add("CARD NAME");
            DataReport.Columns.Add("DISCOUNT %");
            DataReport.Columns.Add("REFERENCE NO");
            DataReport.Columns.Add("ITEMCODE");
            DataReport.Columns.Add("DESCRIPTION");
            DataReport.Columns.Add("WARE HOUSE CODE");
            DataReport.Columns.Add("QUANTITY");
            DataReport.Columns.Add("PRICE");
            DataReport.Columns.Add("TAXCODE");
            DataReport.Columns.Add("TAX%");
            DataReport.Columns.Add("TRANSPORT");
            DataReport.Columns.Add("Narration");
            lblmsg.Text = "";
            SalesDBManager SalesDB   = new SalesDBManager();
            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);
                }
            }

            lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
            string branchid = Session["Po_BranchID"].ToString();

            cmd = new SqlCommand("SELECT po_entrydetailes.remarks, po_entrydetailes.sno, po_entrydetailes.shortname, po_entrydetailes.podate, po_entrydetailes.poamount, po_entrydetailes.name, po_entrydetailes.delivarydate, po_entrydetailes.freigtamt, po_entrydetailes.status, po_entrydetailes.quotationdate, po_entrydetailes.supplierid, po_entrydetailes.others, po_entrydetailes.paymentid, po_entrydetailes.deliverytermsid, po_entrydetailes.insurance, po_entrydetailes.insuranceamount, po_entrydetailes.remarks, po_entrydetailes.otp, po_entrydetailes.ponumber, po_entrydetailes.branchid, po_entrydetailes.pricebasis, po_entrydetailes.addressid, po_entrydetailes.billaddressid, po_entrydetailes.transportcharge, po_entrydetailes.indentno, po_sub_detailes.code AS Expr1, po_sub_detailes.sno AS Expr2, po_sub_detailes.description, po_sub_detailes.qty, po_sub_detailes.cost, po_sub_detailes.taxtype, po_sub_detailes.ed, po_sub_detailes.dis, po_sub_detailes.disamt, po_sub_detailes.tax, po_sub_detailes.po_refno, po_sub_detailes.productsno, po_sub_detailes.edtax, po_sub_detailes.productamount, productmaster.productname, productmaster.itemcode, suppliersdetails.name AS sup_name, suppliersdetails.suppliercode, taxmaster.type, taxmaster_1.type AS edtype, po_entrydetailes.pfid, po_sub_detailes.igst, po_sub_detailes.cgst, po_sub_detailes.sgst FROM po_entrydetailes INNER JOIN po_sub_detailes ON po_entrydetailes.sno = po_sub_detailes.po_refno INNER JOIN productmaster ON po_sub_detailes.productsno = productmaster.productid LEFT OUTER JOIN taxmaster ON po_sub_detailes.taxtype = taxmaster.sno INNER JOIN suppliersdetails ON po_entrydetailes.supplierid = suppliersdetails.supplierid LEFT OUTER JOIN taxmaster AS taxmaster_1 ON po_sub_detailes.ed = taxmaster_1.sno WHERE (po_entrydetailes.podate BETWEEN @fromdate AND @todate) AND (po_entrydetailes.branchid = @branchid)");
            cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
            cmd.Parameters.Add("@todate", GetHighDate(fromdate));
            cmd.Parameters.Add("@branchid", branchid);
            DataTable dttotalinward         = vdm.SelectQuery(cmd).Tables[0];
            DateTime  ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
            if (dttotalinward.Rows.Count > 0)
            {
                string   taxcode = "", taxtype = "", transport = "";
                double   ttotalamount = 0;
                DateTime dt           = DateTime.Now;
                string   prevdate     = string.Empty;
                DateTime dtapril      = new DateTime();
                DateTime dtmarch      = new DateTime();

                foreach (DataRow dr in dttotalinward.Rows)
                {
                    DataRow  newrow        = DataReport.NewRow();
                    DateTime dt_po         = Convert.ToDateTime(dr["podate"].ToString());
                    int      currentyear   = dt_po.Year;
                    int      nextyear      = dt_po.Year + 1;
                    int      currntyearnum = 0;
                    int      nextyearnum   = 0;
                    if (dt_po.Month > 3)
                    {
                        string apr = "4/1/" + currentyear;
                        dtapril = DateTime.Parse(apr);
                        string march = "3/31/" + nextyear;
                        dtmarch       = DateTime.Parse(march);
                        currntyearnum = currentyear;
                        nextyearnum   = nextyear;
                    }
                    if (dt_po.Month <= 3)
                    {
                        string apr = "4/1/" + (currentyear - 1);
                        dtapril = DateTime.Parse(apr);
                        string march = "3/31/" + (nextyear - 1);
                        dtmarch       = DateTime.Parse(march);
                        currntyearnum = currentyear - 1;
                        nextyearnum   = nextyear - 1;
                    }
                    string dt_po_date = dt_po.ToString("dd/MM/yyyy");
                    newrow["CREATE DATE"] = dt_po_date;
                    newrow["CARD CODE"]   = dr["suppliercode"].ToString();
                    newrow["CARD NAME"]   = dr["sup_name"].ToString();
                    newrow["DISCOUNT %"]  = dr["dis"].ToString();
                    string po = "";
                    po = "SVDS/PBK/PO/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dr["ponumber"].ToString();
                    newrow["REFERENCE NO"]    = po.ToString();
                    newrow["ITEMCODE"]        = dr["itemcode"].ToString();
                    newrow["DESCRIPTION"]     = dr["productname"].ToString();
                    newrow["WARE HOUSE CODE"] = "SVDSPP02";
                    newrow["QUANTITY"]        = dr["qty"].ToString();
                    newrow["PRICE"]           = dr["cost"].ToString();
                    taxtype = dr["type"].ToString();
                    string igst = dr["igst"].ToString();
                    string cgst = dr["cgst"].ToString();
                    string sgst = dr["sgst"].ToString();
                    if (igst != "0")
                    {
                        taxcode        = "IGST" + igst + "";
                        newrow["TAX%"] = igst;
                    }
                    if (cgst != "0")
                    {
                        double ctax = Convert.ToDouble(cgst);
                        cgst           = (ctax + ctax).ToString();
                        taxcode        = "CGST" + cgst + "";
                        newrow["TAX%"] = ctax;
                    }
                    if (igst == "0" && cgst == "0")
                    {
                        taxcode        = "EXEMPT";
                        newrow["TAX%"] = "0";
                    }
                    newrow["TAXCODE"] = taxcode;
                    double total = 0; double vat_amt = 0; double amt_tax = 0;
                    string trans_chrgs   = dr["transportcharge"].ToString();
                    string freight_chrgs = dr["freigtamt"].ToString();
                    if (trans_chrgs != "" || trans_chrgs != "0")
                    {
                        transport = trans_chrgs;
                    }
                    else if (freight_chrgs != "" || freight_chrgs != "0")
                    {
                        transport = freight_chrgs;
                    }
                    else
                    {
                        transport = "0";
                    }
                    amt_tax             = total + vat_amt + Convert.ToDouble(transport);
                    newrow["TRANSPORT"] = transport;
                    newrow["Narration"] = dr["remarks"].ToString();
                    ttotalamount       += amt_tax;
                    DataReport.Rows.Add(newrow);
                }
                Session["xportdata"]  = DataReport;
                Session["filename"]   = "SAP PO Report";
                grdReports.DataSource = DataReport;
                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;
        }
    }
    void GetReport()
    {
        try
        {
            lblmsg.Text          = "";
            pnlHide.Visible      = true;
            Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
            Session["IDate"]     = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");

            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 ReportDate  = SalesDBManager.GetTime(vdm.conn);
            DateTime dtapril     = new DateTime();
            DateTime dtmarch     = new DateTime();
            int      currentyear = ReportDate.Year;
            int      nextyear    = ReportDate.Year + 1;
            string   pdate       = "03/31/" + currentyear + "";
            DateTime dtpdate     = Convert.ToDateTime(pdate);
            string   cdate       = GetLowDate(fromdate).ToString("MM/dd/yyyy");
            DateTime dtcdate     = Convert.ToDateTime(cdate);
            if (dtcdate > dtpdate)
            {
            }
            else
            {
                currentyear = currentyear - 1;
                nextyear    = currentyear + 1;
            }

            if (ReportDate.Month > 3)
            {
                string apr = "4/1/" + currentyear;
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + nextyear;
                dtmarch = DateTime.Parse(march);
            }
            if (ReportDate.Month <= 3)
            {
                string apr = "4/1/" + (currentyear);
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + (nextyear);
                dtmarch = DateTime.Parse(march);
            }
            DataTable Report = new DataTable();
            Report.Columns.Add("DOE");
            Report.Columns.Add("Ref Receipt");
            Report.Columns.Add("Receipt");
            Report.Columns.Add("Type");
            Report.Columns.Add("Name");
            Report.Columns.Add("Remarks");

            Report.Columns.Add("Amount").DataType = typeof(Double);
            lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
            lblRoutName.Text     = ddlSalesOffice.SelectedItem.Text;
            Session["xporttype"] = "TallyReceipts";
            string ledger = "";
            cmd = new SqlCommand("SELECT ladger_dr FROM Plant_Master WHERE (Plant_Code = @Plant_Code)");
            cmd.Parameters.Add("@Plant_Code", ddlSalesOffice.SelectedValue);
            DataTable dtledger = vdm.SelectQuery(cmd).Tables[0];
            if (dtledger.Rows.Count > 0)
            {
                ledger = dtledger.Rows[0]["ladger_dr"].ToString();
            }
            Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Receipts" + fromdate.ToString("dd/MM/yyyy");
            DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
            cmd = new SqlCommand("SELECT Branchid, Amount, Remarks, CONVERT(VARCHAR(10), DOE, 103) AS DOE, Receiptno, Name,PaymentType as Type FROM cashcollections WHERE (Branchid = @BranchID) AND (DOE BETWEEN @d1 AND @d2)  AND (CollectionType = 'Cash') ORDER BY DOE");
            cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", GetHighDate(fromdate));
            cmd.Parameters.Add("@Type", "Others");
            DataTable dtOthers = vdm.SelectQuery(cmd).Tables[0];
            foreach (DataRow dr in dtOthers.Rows)
            {
                DataRow newrow = Report.NewRow();
                newrow["DOE"]     = dr["DOE"].ToString();
                newrow["Receipt"] = dr["Receiptno"].ToString();
                newrow["Type"]    = dr["Type"].ToString();
                newrow["Name"]    = dr["Name"].ToString();
                double AmountPaid = 0;
                double.TryParse(dr["Amount"].ToString(), out AmountPaid);
                newrow["Amount"]  = AmountPaid;
                newrow["Remarks"] = dr["Remarks"].ToString();
                Report.Rows.Add(newrow);
            }
            string Receiptno = "";
            Receiptno = dtapril.ToString("yy") + dtmarch.ToString("yy");
            if (Report.Rows.Count > 0)
            {
                DataView view = new DataView(Report);
                dtReport = new DataTable();
                dtReport.Columns.Add("Voucher Date");
                dtReport.Columns.Add("Voucher No");
                dtReport.Columns.Add("Voucher Type");
                dtReport.Columns.Add("Ledger (Dr)");
                dtReport.Columns.Add("Ledger (Cr)");
                dtReport.Columns.Add("Amount");
                dtReport.Columns.Add("Narration");
                //DataTable distincttable = view.ToTable(true, "BranchName", "BSno");
                int i = 1;
                foreach (DataRow branch in Report.Rows)
                {
                    DataRow newrow = dtReport.NewRow();
                    newrow["Voucher Date"] = fromdate.ToString("dd-MMM-yyyy");
                    string newreceipt = "0";
                    int    countdc    = 0;
                    int.TryParse(branch["Receipt"].ToString(), out countdc);
                    if (countdc < 10)
                    {
                        newreceipt = "000" + countdc;
                    }
                    if (countdc >= 10 && countdc <= 99)
                    {
                        newreceipt = "00" + countdc;
                    }
                    if (countdc >= 99 && countdc <= 999)
                    {
                        newreceipt = "0" + countdc;
                    }
                    if (countdc > 999)
                    {
                        newreceipt = "" + countdc;
                    }
                    newrow["Voucher No"]   = Receiptno + newreceipt;
                    newrow["Voucher Type"] = "Cash Receipt Import";
                    newrow["Ledger (Dr)"]  = ledger;
                    if (branch["Name"].ToString() == "")
                    {
                    }
                    else
                    {
                        newrow["Ledger (Cr)"] = branch["Name"].ToString();
                        newrow["Amount"]      = branch["Amount"].ToString();
                        double invval = 0;
                        //newrow["Narration"] = branch["Remarks"].ToString();
                        newrow["Narration"] = "" + branch["Remarks"].ToString() + " vide Receipt No " + branch["Receipt"].ToString() + ",Receipt Date " + fromdate.ToString("dd/MM/yyyy") + ",Emp Name " + Session["EmpName"].ToString();
                        dtReport.Rows.Add(newrow);
                        i++;
                    }
                }
                grdReports.DataSource = dtReport;
                grdReports.DataBind();
                Session["xportdata"] = dtReport;
            }
            else
            {
                pnlHide.Visible       = false;
                lblmsg.Text           = "No Indent Found";
                grdReports.DataSource = dtReport;
                grdReports.DataBind();
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text           = ex.Message;
            grdReports.DataSource = dtReport;
            grdReports.DataBind();
        }
    }
示例#16
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;
     }
 }
示例#17
0
    void fill_login_details()
    {
        string   sendmsgs = lbl_messsge.Text.ToString();
        string   username = lbl_username.Text.ToString();
        string   password = lbl_passwords.Text.ToString();
        DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);

        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.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 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";
            }
            //End
            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);
            cmd = new SqlCommand("SELECT  TOP (1) sno, UserId, status FROM logininfo WHERE (UserId = @UserId)  ORDER BY sno DESC");
            cmd.Parameters.Add("@UserId", dt.Rows[0]["sno"].ToString());
            DataTable dttatus = vdm.SelectQuery(cmd).Tables[0];
            Session["lgininfosno"]    = dttatus.Rows[0]["sno"].ToString();
            Session["lgininfostatus"] = dttatus.Rows[0]["status"].ToString();
            // Cookies
            Response.Cookies["userInfo"]["Employ_Sno"]     = Session["Employ_Sno"].ToString();
            Response.Cookies["userInfo"]["UserName"]       = Session["UserName"].ToString();
            Response.Cookies["userInfo"]["Branch_ID"]      = Session["Branch_ID"].ToString();
            Response.Cookies["userInfo"]["TitleName"]      = Session["TitleName"].ToString();
            Response.Cookies["userInfo"]["TinNo"]          = Session["TinNo"].ToString();
            Response.Cookies["userInfo"]["DeptID"]         = Session["DeptID"].ToString();
            Response.Cookies["userInfo"]["Emp_Type"]       = Session["Emp_Type"].ToString();
            Response.Cookies["userInfo"]["Address"]        = Session["Address"].ToString();
            Response.Cookies["userInfo"]["BranchType"]     = Session["BranchType"].ToString();
            Response.Cookies["userInfo"]["leveltype"]      = Session["leveltype"].ToString();
            Response.Cookies["userInfo"]["VendorID"]       = Session["VendorID"].ToString();
            Response.Cookies["userInfo"]["loginflag"]      = Session["loginflag"].ToString();
            Response.Cookies["userInfo"]["lgininfosno"]    = Session["lgininfosno"].ToString();
            Response.Cookies["userInfo"]["lgininfostatus"] = Session["lgininfostatus"].ToString();
            Response.Cookies["userInfo"]["lastVisit"]      = DateTime.Now.ToString();
            Response.Cookies["userInfo"].Expires           = DateTime.Now.AddDays(1);
            HttpCookie aCookie = new HttpCookie("userInfo");
            aCookie.Values["UserName"]       = Session["UserName"].ToString();
            aCookie.Values["Employ_Sno"]     = Session["Employ_Sno"].ToString();
            aCookie.Values["Branch_ID"]      = Session["Branch_ID"].ToString();
            aCookie.Values["TitleName"]      = Session["TitleName"].ToString();
            aCookie.Values["TinNo"]          = Session["TinNo"].ToString();
            aCookie.Values["DeptID"]         = Session["DeptID"].ToString();
            aCookie.Values["Emp_Type"]       = Session["Emp_Type"].ToString();
            aCookie.Values["Address"]        = Session["Address"].ToString();
            aCookie.Values["BranchType"]     = Session["BranchType"].ToString();
            aCookie.Values["leveltype"]      = Session["leveltype"].ToString();
            aCookie.Values["VendorID"]       = Session["VendorID"].ToString();
            aCookie.Values["loginflag"]      = Session["loginflag"].ToString();
            aCookie.Values["lgininfosno"]    = Session["lgininfosno"].ToString();
            aCookie.Values["lgininfostatus"] = Session["lgininfostatus"].ToString();
            aCookie.Values["lastVisit"]      = DateTime.Now.ToString();
            aCookie.Expires = DateTime.Now.AddDays(1);
            Response.Cookies.Add(aCookie);
            string branchtype = dt.Rows[0]["branchtype"].ToString();
            string leveltype  = dt.Rows[0]["leveltype"].ToString();
            if (branchtype == "CC")
            {
                Response.Redirect("Despatch_Entry.aspx", false);
            }
            else
            {
                if (leveltype == "lab")
                {
                    Response.Redirect("DashBoard.aspx", false);
                }
                if (leveltype == "Security")
                {
                    Response.Redirect("Inward_dc_varify.aspx", false);
                }
                if (leveltype == "Processing")
                {
                    Response.Redirect("InwardSilo.aspx", false);
                }
                if (leveltype == "Operations")
                {
                    Response.Redirect("chartdashboard.aspx", false);
                }
                if (leveltype == "packing")
                {
                    Response.Redirect("Packingsection.aspx", false);
                }
                if (leveltype == "weighing")
                {
                    Response.Redirect("WeighingDayReport.aspx", false);
                }
                if (leveltype == "Admin")
                {
                    Response.Redirect("chartdashboard.aspx", false);
                }
                if (leveltype == "MAdmin")
                {
                    Response.Redirect("chartdashboard.aspx", false);
                }
                if (leveltype == "curdblock")
                {
                    Response.Redirect("chemicalconsumption.aspx", false);
                }
                if (leveltype == "Data")
                {
                    Response.Redirect("TankersInwardReport.aspx", false);
                }
                if (leveltype == "Reports")
                {
                    Response.Redirect("TankersInwardReport.aspx", false);
                }
                if (leveltype == "dailyreport")
                {
                    Response.Redirect("plantdailyreport.aspx", false);
                }
                if (leveltype == "ghee")
                {
                    Response.Redirect("GheeSection.aspx", false);
                }
                if (leveltype == "Butter")
                {
                    Response.Redirect("butterproductiondetails.aspx", false);
                }
                if (leveltype == "suboperations")
                {
                    Response.Redirect("SiloMonitor.aspx", false);
                }
                if (leveltype == "curdandlab")
                {
                    Response.Redirect("chemicalconsumption.aspx", false);
                }
            }
        }
    }
示例#18
0
 protected void btn_save_click(object sender, EventArgs e)
 {
     try
     {
         vdm = new SalesDBManager();
         DateTime    CreateDate    = SalesDBManager.GetTime(vdm.conn);
         SAPdbmanger SAPvdm        = new SAPdbmanger();
         DateTime    fromdate      = DateTime.Now;
         DataTable   dt            = (DataTable)Session["xportdata"];
         string[]    dateFromstrig = dtp_FromDate.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);
             }
         }
         foreach (DataRow dr in dt.Rows)
         {
             string itemcode = dr["ITEMCODE"].ToString();
             string cardcode = dr["CARD CODE"].ToString();
             if (itemcode == "")
             {
             }
             else if (cardcode == "")
             {
             }
             else
             {
                 cmd = new SqlCommand("SELECT * FROM EMROPOR WHERE (TaxDate BETWEEN @d1 AND @d2) AND (ReferenceNo = @ReferenceNo) AND (ItemCode = @ItemCode) AND (WhsCode = @WhsCode)");
                 cmd.Parameters.Add("@d1", GetLowDate(fromdate));
                 cmd.Parameters.Add("@d2", GetHighDate(fromdate));
                 cmd.Parameters.Add("@ReferenceNo", dr["REFERENCE NO"].ToString());
                 cmd.Parameters.Add("@WhsCode", dr["WARE HOUSE CODE"].ToString());
                 cmd.Parameters.Add("@ItemCode", dr["ITEMCODE"].ToString());
                 DataTable dtPCH = SAPvdm.SelectQuery(cmd).Tables[0];
                 if (dtPCH.Rows.Count > 0)
                 {
                 }
                 else
                 {
                     cmd = new SqlCommand("insert into EMROPOR (CreateDate, CardCode, CardName, TaxDate, DocDate, DocDueDate, DiscPercent, ReferenceNo, ItemCode, Dscription, WhsCode, Quantity, Price, OcrCode, TaxCode, PURCHASETYPE, B1Upload, Processed,remarks,VAT_Percent) values (@CreateDate, @CardCode, @CardName, @TaxDate, @DocDate, @DocDueDate, @DiscPercent, @ReferenceNo, @ItemCode, @Dscription, @WhsCode, @Quantity, @Price, @OcrCode, @TaxCode, @purchasetype, @B1Upload, @Processed,@remarks,@TAXPER)");
                     cmd.Parameters.Add("@CreateDate", CreateDate);
                     cmd.Parameters.Add("@CardCode", dr["CARD CODE"].ToString());
                     cmd.Parameters.Add("@CardName", dr["CARD NAME"].ToString());
                     cmd.Parameters.Add("@TaxDate", fromdate);
                     cmd.Parameters.Add("@DocDate", fromdate);
                     cmd.Parameters.Add("@DocDueDate", fromdate);
                     cmd.Parameters.Add("@DiscPercent", dr["DISCOUNT %"].ToString());
                     cmd.Parameters.Add("@ReferenceNo", dr["REFERENCE NO"].ToString());
                     cmd.Parameters.Add("@ItemCode", dr["ITEMCODE"].ToString());
                     cmd.Parameters.Add("@Dscription", dr["DESCRIPTION"].ToString());
                     cmd.Parameters.Add("@WhsCode", dr["WARE HOUSE CODE"].ToString());
                     double qty = 0;
                     double.TryParse(dr["QUANTITY"].ToString(), out qty);
                     cmd.Parameters.Add("@Quantity", qty);
                     double price = 0;
                     double.TryParse(dr["PRICE"].ToString(), out price);
                     cmd.Parameters.Add("@Price", price);
                     cmd.Parameters.Add("@OcrCode", dr["WARE HOUSE CODE"].ToString());
                     cmd.Parameters.Add("@TaxCode", dr["TAXCODE"].ToString());
                     cmd.Parameters.Add("@TAXPER", dr["TAX%"].ToString());
                     string B1Upload     = "N";
                     string Processed    = "N";
                     string purchasetype = "93";
                     cmd.Parameters.Add("@purchasetype", purchasetype);
                     cmd.Parameters.Add("@B1Upload", B1Upload);
                     cmd.Parameters.Add("@Processed", Processed);
                     cmd.Parameters.Add("@remarks", dr["Narration"].ToString());
                     if (qty == 0.0)
                     {
                     }
                     else
                     {
                         SAPvdm.insert(cmd);
                     }
                 }
             }
         }
         hidepanel.Visible = false;
         lblmsg.Text       = "Saved successfully";
         DataTable dtempty = new DataTable();
         grdReports.DataSource = dtempty;
         grdReports.DataBind();
     }
     catch (Exception ex)
     {
         lblmsg.Text       = ex.Message;
         hidepanel.Visible = false;
     }
 }
    void getdet()
    {
        try
        {
            vdm = new SalesDBManager();
            string BrachSOID = "";

            BrachSOID = Session["branch"].ToString();
            //cmd = new SqlCommand("SELECT cashpayables.onNameof, cashpayables.DOE,cashpayables.VocherID, cashpayables.Sno, cashpayables.Amount, cashpayables.ApprovedAmount, cashpayables.Remarks, cashpayables.ApprovalRemarks,cashpayables.VoucherType, empmanage.EmpName FROM cashpayables LEFT OUTER JOIN empmanage ON cashpayables.Approvedby = empmanage.Sno WHERE (cashpayables.Sno = @VocherID) AND (cashpayables.BranchID = @BranchID)");
            cmd = new SqlCommand("SELECT Sno, BranchID, CashTo, DOE, VocherID, Remarks, Amount, Approvedby, ApprovedAmount, ApprovalRemarks, Status, Created_by, Modify_by, Empid, onNameof,                          CloBal, VoucherType, CashierRemarks, ForceApproval, ReceivedAmount, DueAmount, AccountType, AgentID, DOR, Denominations FROM cashpayables WHERE (Sno = @VocherID) AND (BranchID = @BranchID)");
            cmd.Parameters.Add("@VocherID", txtVoucherNo.Text);
            cmd.Parameters.Add("@BranchID", BrachSOID);
            DataTable dtCash = vdm.SelectQuery(cmd).Tables[0];
            //cmd = new SqlCommand("SELECT sno, BranchName, BranchCode FROM branchdata WHERE (sno = @BranchID)");
            //cmd.Parameters.Add("@BranchID", BrachSOID);
            //DataTable dtCode = vdm.SelectQuery(cmd).Tables[0];
            string   voucherid             = "";
            DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
            DateTime dtapril       = new DateTime();
            DateTime dtmarch       = new DateTime();
            int      currentyear   = ServerDateCurrentdate.Year;
            int      nextyear      = ServerDateCurrentdate.Year + 1;
            int      currntyearnum = 0;
            int      nextyearnum   = 0;
            if (ServerDateCurrentdate.Month > 3)
            {
                string apr = "4/1/" + currentyear;
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + nextyear;
                dtmarch       = DateTime.Parse(march);
                currntyearnum = currentyear;
                nextyearnum   = nextyear;
            }
            if (ServerDateCurrentdate.Month <= 3)
            {
                string apr = "4/1/" + (currentyear - 1);
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + (nextyear - 1);
                dtmarch       = DateTime.Parse(march);
                currntyearnum = currentyear - 1;
                nextyearnum   = nextyear - 1;
            }
            if (dtCash.Rows.Count > 0)
            {
                //lblVoucherno.Text = txtVoucherNo.Text;
                voucherid         = Session["plant_code"].ToString() + "/VOC/" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + dtCash.Rows[0]["VocherID"].ToString();
                lblVoucherno.Text = voucherid;
                string   DOE   = dtCash.Rows[0]["DOE"].ToString();
                DateTime dtDOE = Convert.ToDateTime(DOE);
                lblApprove.Text = "G.M/P.M";
                string ChangedTime = dtDOE.ToString("dd/MMM/yyyy");
                lblDate.Text    = ChangedTime;
                lblPayCash.Text = dtCash.Rows[0]["onNameof"].ToString();
                string AppRemarks = dtCash.Rows[0]["ApprovalRemarks"].ToString();
                if (AppRemarks == "")
                {
                    lblTowards.Text = dtCash.Rows[0]["Remarks"].ToString();
                }
                else
                {
                    lblTowards.Text = dtCash.Rows[0]["ApprovalRemarks"].ToString();
                }
                lblVoucherType.Text = dtCash.Rows[0]["VoucherType"].ToString().ToUpper() + " VOUCHER";
                string   Amont = dtCash.Rows[0]["Amount"].ToString();
                string[] Ones  = { "", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Ninteen" };

                string[] Tens = { "Ten", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninty" };

                int Num = int.Parse(Amont);

                lblReceived.Text = NumToWordBD(Num) + " Rupees Only";
            }
        }
        catch (Exception ex)
        {
            lblReceived.Text = ex.Message;
        }
    }
示例#20
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;
        }
    }
示例#21
0
    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;
        }
    }
示例#22
0
    private void report()
    {
        try
        {
            lblmsg.Text = "";
            string         milkopeningbal  = string.Empty;
            string         milkclosingbal  = string.Empty;
            SalesDBManager SalesDB         = new SalesDBManager();
            DateTime       fromdate        = DateTime.Now;
            DateTime       todate          = DateTime.Now;
            string         idcno           = string.Empty;
            string         inworddate      = string.Empty;
            double         totalpaidamount = 0;
            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");
            int vendorid = Convert.ToInt32(ddlbranches.SelectedItem.Value);
            Report.Columns.Add("Sno");
            Report.Columns.Add("Date");
            Report.Columns.Add("Dc No");
            Report.Columns.Add("Dc Amount");
            Report.Columns.Add("Opp Bal");
            Report.Columns.Add("Total Amount");
            if (ddlcollectiontype.SelectedValue == "Collection")
            {
                Report.Columns.Add("Received Amount");
            }
            else
            {
                Report.Columns.Add("Paid Amount");
            }
            Report.Columns.Add("Balance");
            hidepanel.Visible = true;
            DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
            cmd = new SqlCommand("SELECT vendors.vendorname, collections.opp_bal, collections.amount, collections.transtype,CONVERT(VARCHAR(20),collections.doe, 101) AS doe,  collections.remarks, collections.clo_bal FROM   collections INNER JOIN vendors ON collections.vendorid = vendors.sno  WHERE (collections.vendorid=@vid) AND (collections.doe between @d1 and @d2) AND (collections.transtype='Payment') ");
            cmd.Parameters.Add("@vid", vendorid);
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", ServerDateCurrentdate);
            DataTable dtcollection = vdm.SelectQuery(cmd).Tables[0];
            double    debitprice   = 0;
            foreach (DataRow dr in dtcollection.Rows)
            {
                double amountDebited = 0;
                double.TryParse(dr["amount"].ToString(), out amountDebited);
                debitprice += amountDebited;
            }
            cmd = new SqlCommand("SELECT vendor_accounts.sno, vendor_accounts.amount, vendor_subtable.milktype FROM vendor_accounts INNER JOIN vendors ON vendor_accounts.vendorid = vendors.sno INNER JOIN  vendor_subtable ON vendors.sno = vendor_subtable.vendor_refno WHERE (vendor_accounts.vendorid = @VendorID)");
            cmd.Parameters.Add("@VendorID", vendorid);
            DataTable dtvendor_presentopp = vdm.SelectQuery(cmd).Tables[0];
            double    vendorpresentopp    = 0;
            double.TryParse(dtvendor_presentopp.Rows[0]["Amount"].ToString(), out vendorpresentopp);
            string milktype = dtvendor_presentopp.Rows[0]["milktype"].ToString();

            if (ddlcollectiontype.SelectedValue == "Collection")
            {
                cmd = new SqlCommand("  SELECT milktransactions.dcno, milktransactions.inwardno AS InwardNo, milktransactions.vehicleno,CONVERT(VARCHAR(20),milktransactions.doe, 101) AS doe , milktransactions.transtype, milktransactions.qty_ltr, milktransactions.qty_kgs, milktransactions.percentageon, milktransactions.snf, milktransactions.fat, milktransactions.clr, milktransaction_logs.milktype, milktransaction_logs.rate_on, milktransaction_logs.cost, milktransaction_logs.calc_on, milktransaction_logs.overheadon, milktransaction_logs.overheadcost, milktransaction_logs.m_std_snf, milktransaction_logs.p_std_snf, milktransaction_logs.snfplus_on, milktransaction_logs.m_snfpluscost, milktransaction_logs.p_snfpluscost, milktransaction_logs.transport_on, milktransaction_logs.transportcost, milktransaction_logs.transport, vendors.vendorname FROM milktransactions INNER JOIN milktransaction_logs ON milktransactions.sno = milktransaction_logs.milktransaction_sno INNER JOIN vendors ON milktransactions.sectionid = vendors.sno WHERE  (milktransactions.doe BETWEEN @d1 AND @d2) AND (milktransactions.transtype = @transtype)  AND (milktransactions.sectionid=@sectionid)");
                cmd.Parameters.Add("@d1", GetLowDate(fromdate));
                cmd.Parameters.Add("@d2", ServerDateCurrentdate);
                Session["filename"] = "Collection";
                Session["title"]    = "Collection Details";
                cmd.Parameters.Add("@transtype", "Out");
            }
            if (ddlcollectiontype.SelectedValue == "Payment")
            {
                cmd = new SqlCommand("  SELECT milktransactions.dcno, milktransactions.inwardno AS InwardNo,milktransaction_logs.m_std_fat,milktransaction_logs.fatplus_on,milktransaction_logs.p_std_fat,milktransaction_logs.m_fatpluscost,milktransaction_logs.p_fatpluscost, milktransaction_logs.p_std_snf, milktransactions.vehicleno,CONVERT(VARCHAR(20),milktransactions.doe, 101) AS doe , milktransactions.transtype, milktransactions.qty_ltr, milktransactions.qty_kgs, milktransactions.percentageon, milktransactions.snf, milktransactions.fat, milktransactions.clr, milktransaction_logs.milktype, milktransaction_logs.rate_on, milktransaction_logs.cost, milktransaction_logs.calc_on, milktransaction_logs.overheadon, milktransaction_logs.overheadcost, milktransaction_logs.m_std_snf, milktransaction_logs.p_std_snf, milktransaction_logs.snfplus_on, milktransaction_logs.m_snfpluscost, milktransaction_logs.p_snfpluscost, milktransaction_logs.transport_on, milktransaction_logs.transportcost, milktransaction_logs.transport, vendors.vendorname FROM milktransactions INNER JOIN milktransaction_logs ON milktransactions.sno = milktransaction_logs.milktransaction_sno INNER JOIN vendors ON milktransactions.sectionid = vendors.sno WHERE  (milktransactions.doe BETWEEN @d1 AND @d2) AND (milktransactions.transtype = @transtype)  AND (milktransactions.sectionid=@sectionid)");
                cmd.Parameters.Add("@d1", GetLowDate(fromdate));
                cmd.Parameters.Add("@d2", ServerDateCurrentdate);
                Session["filename"] = "Payment";
                Session["title"]    = "Payment Details";
                cmd.Parameters.Add("@transtype", "In");
            }
            cmd.Parameters.Add("@sectionid", vendorid);
            DataTable dtcow         = new DataTable();
            DataTable dttankersales = SalesDB.SelectQuery(cmd).Tables[0];
            if (ddlcollectiontype.SelectedValue == "Collection")
            {
                cmd = new SqlCommand("  SELECT directsales_purchase.dcno, directsales_purchase.partydcno, directsales_purchase.inwardno AS InwardNo, directsales_purchase.vehicleno, CONVERT(VARCHAR(20),directsales_purchase.doe, 101) AS doe, directsales_purchase.transtype, directsales_purchase.qty_ltr, directsales_purchase.qty_kgs, directsales_purchase.percentageon, directsales_purchase.snf, directsales_purchase.fat, directsales_purchase.clr, directsales_purchaselogs.milktype, directsales_purchaselogs.rate_on, directsales_purchaselogs.cost, directsales_purchaselogs.calc_on, directsales_purchaselogs.overheadon, directsales_purchaselogs.overheadcost, directsales_purchaselogs.m_std_snf, directsales_purchaselogs.p_std_snf, directsales_purchaselogs.snfplus_on, directsales_purchaselogs.m_snfpluscost, directsales_purchaselogs.p_snfpluscost, directsales_purchaselogs.transport_on, directsales_purchaselogs.transportcost, directsales_purchaselogs.transport, vendors.vendorname FROM directsales_purchase INNER JOIN directsales_purchaselogs ON directsales_purchase.sno = directsales_purchaselogs.purchaserefno INNER JOIN vendors ON directsales_purchase.sectionid = vendors.sno WHERE  (directsales_purchase.doe BETWEEN @d1 AND @d2) AND (directsales_purchase.transtype = @transtype) AND (directsales_purchaselogs.milktype='Cow') AND (directsales_purchase.sectionid=@sectionid)");
                cmd.Parameters.Add("@d1", GetLowDate(fromdate));
                cmd.Parameters.Add("@d2", GetHighDate(todate));
                cmd.Parameters.Add("@transtype", "Out");
                cmd.Parameters.Add("@sectionid", ddlbranches.SelectedValue);
                dtcow = SalesDB.SelectQuery(cmd).Tables[0];
            }
            if (ddlcollectiontype.SelectedValue == "Payment")
            {
                cmd = new SqlCommand("  SELECT directsales_purchase.dcno, directsales_purchase.partydcno, directsales_purchase.inwardno AS InwardNo, directsales_purchase.vehicleno, CONVERT(VARCHAR(20),directsales_purchase.doe, 101) AS doe, directsales_purchase.transtype, directsales_purchase.qty_ltr, directsales_purchase.qty_kgs, directsales_purchase.percentageon, directsales_purchase.snf, directsales_purchase.fat, directsales_purchase.clr, directsales_purchaselogs.milktype, directsales_purchaselogs.rate_on, directsales_purchaselogs.cost, directsales_purchaselogs.calc_on, directsales_purchaselogs.overheadon, directsales_purchaselogs.overheadcost, directsales_purchaselogs.m_std_snf, directsales_purchaselogs.p_std_snf, directsales_purchaselogs.snfplus_on, directsales_purchaselogs.m_snfpluscost, directsales_purchaselogs.p_snfpluscost, directsales_purchaselogs.transport_on, directsales_purchaselogs.transportcost, directsales_purchaselogs.transport, vendors.vendorname FROM directsales_purchase INNER JOIN directsales_purchaselogs ON directsales_purchase.sno = directsales_purchaselogs.purchaserefno INNER JOIN vendors ON directsales_purchase.sectionid = vendors.sno WHERE  (directsales_purchase.doe BETWEEN @d1 AND @d2) AND (directsales_purchase.transtype = @transtype) AND (directsales_purchaselogs.milktype='Cow') AND (directsales_purchase.sectionid=@sectionid)");
                cmd.Parameters.Add("@d1", GetLowDate(fromdate));
                cmd.Parameters.Add("@d2", GetHighDate(todate));
                cmd.Parameters.Add("@transtype", "in");
                cmd.Parameters.Add("@sectionid", ddlbranches.SelectedValue);
                dtcow = SalesDB.SelectQuery(cmd).Tables[0];
            }
            //cmd = new SqlCommand("  SELECT directsales_purchase.dcno,directsales_purchase.partydcno, directsales_purchase.inwardno AS InwardNo, directsales_purchase.vehicleno,CONVERT(VARCHAR(20),directsales_purchase.doe, 101) AS doe , directsales_purchase.transtype, directsales_purchase.qty_ltr, directsales_purchase.qty_kgs, directsales_purchase.percentageon, directsales_purchase.snf, directsales_purchase.fat, directsales_purchase.clr, directsales_purchaselogs.milktype, directsales_purchaselogs.rate_on, directsales_purchaselogs.cost, directsales_purchaselogs.calc_on, directsales_purchaselogs.overheadon, directsales_purchaselogs.overheadcost, directsales_purchaselogs.m_std_snf, directsales_purchaselogs.p_std_snf, directsales_purchaselogs.snfplus_on, directsales_purchaselogs.m_snfpluscost, directsales_purchaselogs.p_snfpluscost, directsales_purchaselogs.transport_on, directsales_purchaselogs.transportcost, directsales_purchaselogs.transport, vendors.vendorname FROM directsales_purchase INNER JOIN directsales_purchaselogs ON directsales_purchase.sno = directsales_purchaselogs.purchaserefno INNER JOIN vendors ON directsales_purchase.sectionid = vendors.sno WHERE  (directsales_purchase.doe BETWEEN @d1 AND @d2) AND (directsales_purchase.transtype = @transtype) AND (directsales_purchaselogs.milktype='Buffalo') AND (directsales_purchase.sectionid=@sectionid)");
            //cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            //cmd.Parameters.Add("@d2", GetHighDate(todate));
            //cmd.Parameters.Add("@transtype", "in");
            //cmd.Parameters.Add("@sectionid", ddlbranches.SelectedValue);
            //DataTable dtBufello = SalesDB.SelectQuery(cmd).Tables[0];
            DataTable dtAll = dttankersales.Copy();
            dtAll.Merge(dtcow);

            int      i          = 1;
            string   closingbal = "";
            TimeSpan dateSpan   = todate.Subtract(fromdate);
            int      NoOfdays   = dateSpan.Days;
            NoOfdays = NoOfdays + 1;
            double totdebitedamount = 0;
            double totsalesamount   = 0;
            double oppcarry         = 0;
            foreach (DataRow dr in dtAll.Rows)
            {
                string milktypes = dr["milktype"].ToString();
                {
                    if (milktypes == "Buffalo")
                    {
                        double qty_ltr = 0;
                        double.TryParse(dr["qty_ltr"].ToString(), out qty_ltr);
                        double FAT = 0;
                        double.TryParse(dr["fat"].ToString(), out FAT);
                        FAT = Math.Round(FAT, 2);
                        double SNF = 0;
                        double.TryParse(dr["snf"].ToString(), out SNF);
                        string Rateon = dr["rate_on"].ToString();
                        double weight = 0;
                        double KGFAT  = 0;
                        double KGSNF  = 0;
                        double ltrs   = 0;
                        double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
                        double Kgs = 0;
                        double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
                        double tstotal = 0;
                        tstotal = FAT + SNF;
                        if (Rateon == "TS")
                        {
                            double TS = 0;
                            TS     = FAT + SNF;
                            weight = (TS * Kgs) / 100;
                            KGFAT  = (FAT * Kgs) / 100;
                            KGSNF  = (SNF * Kgs) / 100;
                        }
                        else if (Rateon == "KGFAT")
                        {
                            weight = (FAT * Kgs) / 100;
                            KGFAT  = (FAT * Kgs) / 100;
                            KGSNF  = (SNF * Kgs) / 100;
                        }
                        else if (Rateon == "PerLtr" || Rateon == "PerKg")
                        {
                            string CalOn = dr["calc_on"].ToString();
                            if (CalOn == "Ltrs")
                            {
                                weight = ltrs;
                                KGFAT  = (FAT * ltrs) / 100;
                                KGSNF  = (SNF * ltrs) / 100;
                            }
                            else
                            {
                                weight = Kgs;
                                KGFAT  = (FAT * Kgs) / 100;
                                KGSNF  = (SNF * Kgs) / 100;
                            }
                        }
                        double cost = 0;
                        double.TryParse(dr["cost"].ToString(), out cost);
                        KGFAT = Math.Round(KGFAT, 2);
                        KGSNF = Math.Round(KGSNF, 2);
                        double MValue = 0;
                        MValue = KGFAT * cost;
                        //MValue = MValue / 100;
                        MValue = Math.Round(MValue, 2);
                        string OverheadOn   = dr["overheadon"].ToString();
                        double OHcost       = 0;
                        double overheadcost = 0;
                        double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
                        if (OverheadOn == "Ltrs")
                        {
                            OHcost = overheadcost * ltrs;
                        }
                        else
                        {
                            OHcost = overheadcost * Kgs;
                        }
                        double MSnf = 0;
                        double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
                        double m_snfpluscost = 0;
                        double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
                        double DiffSNFCost = 0;
                        if (SNF < MSnf)
                        {
                            string SNFOn   = dr["snfplus_on"].ToString();
                            double diffSNF = 0;
                            diffSNF = SNF - MSnf;
                            diffSNF = Math.Round(diffSNF, 2);
                            if (SNFOn == "Ltrs")
                            {
                                DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
                            }
                            else
                            {
                                DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
                            }
                        }
                        double p_snfpluscost = 0;
                        double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
                        double PSnf = 0;
                        double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
                        if (SNF > PSnf)
                        {
                            string SNFOn   = dr["snfplus_on"].ToString();
                            double diffSNF = 0;
                            diffSNF = SNF - MSnf;
                            if (SNFOn == "Ltrs")
                            {
                                DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
                            }
                            else
                            {
                                DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
                            }
                        }
                        double OHandMvalue = 0;
                        OHandMvalue     = MValue + OHcost + DiffSNFCost;
                        OHandMvalue     = Math.Round(OHandMvalue, 2);
                        totsalesamount += OHandMvalue;
                    }
                    else
                    {
                        DateTime dtdoe = Convert.ToDateTime(dr["doe"].ToString());
                        string   date  = dtdoe.ToString("dd/MM/yyyy");
                        double   FAT   = 0;
                        double.TryParse(dr["fat"].ToString(), out FAT);
                        FAT = Math.Round(FAT, 2);
                        double SNF = 0;
                        double.TryParse(dr["snf"].ToString(), out SNF);
                        string Rateon = dr["rate_on"].ToString();
                        double weight = 0;
                        double KGFAT  = 0;
                        double KGSNF  = 0;
                        double ltrs   = 0;
                        double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
                        double Kgs = 0;
                        double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
                        double tstotal = 0;
                        tstotal = FAT + SNF;
                        tstotal = Math.Round(tstotal, 2);
                        if (Rateon == "TS")
                        {
                            double TS = 0;
                            TS     = FAT + SNF;
                            weight = (TS * Kgs) / 100;
                            KGFAT  = (FAT * Kgs) / 100;
                            KGSNF  = (SNF * Kgs) / 100;
                        }
                        else if (Rateon == "KGFAT")
                        {
                            weight = (FAT * Kgs) / 100;
                            KGFAT  = (FAT * Kgs) / 100;
                            KGSNF  = (SNF * Kgs) / 100;
                        }
                        else if (Rateon == "PerLtr" || Rateon == "PerKg")
                        {
                            string CalOn = dr["calc_on"].ToString();
                            if (CalOn == "Ltrs")
                            {
                                weight = ltrs;
                                KGFAT  = (FAT * ltrs) / 100;
                                KGSNF  = (SNF * ltrs) / 100;
                            }
                            else
                            {
                                weight = Kgs;
                                KGFAT  = (FAT * Kgs) / 100;
                                KGSNF  = (SNF * Kgs) / 100;
                            }
                        }
                        double cost = 0;
                        double.TryParse(dr["cost"].ToString(), out cost);
                        KGFAT = Math.Round(KGFAT, 2);
                        KGSNF = Math.Round(KGSNF, 2);
                        double MValue = 0;
                        if (Rateon == "PerLtr")
                        {
                            MValue = cost * ltrs;
                        }
                        else if (Rateon == "PerKg")
                        {
                            MValue = cost * Kgs;
                        }
                        else
                        {
                            string CalOn = dr["calc_on"].ToString();
                            if (CalOn == "Ltrs")
                            {
                                MValue = tstotal * cost * ltrs;
                                MValue = MValue / 100;
                            }
                            else
                            {
                                MValue = tstotal * cost * Kgs;
                                MValue = MValue / 100;
                            }
                        }
                        MValue = Math.Round(MValue, 2);
                        string OverheadOn   = dr["overheadon"].ToString();
                        double OHcost       = 0;
                        double overheadcost = 0;
                        double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
                        if (OverheadOn == "Ltrs")
                        {
                            OHcost = overheadcost * ltrs;
                        }
                        else
                        {
                            OHcost = overheadcost * Kgs;
                        }
                        double MFat = 0;
                        double.TryParse(dr["m_std_fat"].ToString(), out MFat);
                        double m_fatpluscost = 0;
                        double.TryParse(dr["m_fatpluscost"].ToString(), out m_fatpluscost);
                        double DiffFATCost = 0;
                        if (FAT < MFat)
                        {
                            string FATOn   = dr["fatplus_on"].ToString();
                            double diffFAT = 0;
                            diffFAT = FAT - MFat;
                            diffFAT = Math.Round(diffFAT, 2);
                            if (FATOn == "Ltrs")
                            {
                                DiffFATCost = diffFAT * ltrs * m_fatpluscost * 10;
                            }
                            else
                            {
                                DiffFATCost = diffFAT * Kgs * m_fatpluscost * 10;
                            }
                        }
                        double p_fatpluscost = 0;
                        double.TryParse(dr["p_fatpluscost"].ToString(), out p_fatpluscost);
                        double PFat = 0;
                        double.TryParse(dr["p_std_fat"].ToString(), out PFat);
                        if (FAT > PFat)
                        {
                            string FATOn   = dr["fatplus_on"].ToString();
                            double diffFAT = 0;
                            diffFAT = FAT - MFat;
                            if (FATOn == "Ltrs")
                            {
                                DiffFATCost = diffFAT * ltrs * p_fatpluscost * 10;
                            }
                            else
                            {
                                DiffFATCost = diffFAT * Kgs * p_fatpluscost * 10;
                            }
                        }
                        DiffFATCost = Math.Round(DiffFATCost, 2);
                        double MSnf = 0;
                        double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
                        double m_snfpluscost = 0;
                        double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
                        double DiffSNFCost = 0;
                        if (SNF < MSnf)
                        {
                            string SNFOn   = dr["snfplus_on"].ToString();
                            double diffSNF = 0;
                            diffSNF = SNF - MSnf;
                            diffSNF = Math.Round(diffSNF, 2);
                            if (SNFOn == "Ltrs")
                            {
                                DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
                            }
                            else
                            {
                                DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
                            }
                        }
                        double p_snfpluscost = 0;
                        double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
                        double PSnf = 0;
                        double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
                        if (SNF > PSnf)
                        {
                            string SNFOn   = dr["snfplus_on"].ToString();
                            double diffSNF = 0;
                            diffSNF = SNF - MSnf;
                            if (SNFOn == "Ltrs")
                            {
                                DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
                            }
                            else
                            {
                                DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
                            }
                        }

                        double OHandMvalue = 0;
                        OHandMvalue     = MValue + OHcost + DiffSNFCost + DiffFATCost;
                        DiffSNFCost     = Math.Round(DiffSNFCost, 2);
                        OHandMvalue     = Math.Round(OHandMvalue, 2);
                        totsalesamount += OHandMvalue;
                        totsalesamount  = Math.Ceiling(totsalesamount);
                    }
                }
            }
            //formula as per ERP
            vendorpresentopp       = (Math.Round(debitprice - totsalesamount)) + vendorpresentopp;
            lblopeningbalance.Text = vendorpresentopp.ToString();
            for (int j = 0; j < NoOfdays; j++)
            {
                DataRow newrow = Report.NewRow();
                newrow["Sno"] = i;
                string   dtcount = fromdate.AddDays(j).ToString();
                DateTime dtDOE   = Convert.ToDateTime(dtcount);
                //string dtdate1 = branch["IndentDate"].ToString();
                string   dtdate1      = dtDOE.AddDays(-1).ToString();
                DateTime dtDOE1       = Convert.ToDateTime(dtdate1).AddDays(1);
                string   ChangedTime1 = dtDOE1.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
                string   ChangedTime2 = dtDOE.AddDays(-1).ToString("dd MMM yy");
                newrow["Date"] = ChangedTime1;
                double    totsale = 0;
                DataTable dtin    = new DataTable();
                DataRow[] drr     = dtAll.Select("doe='" + ChangedTime1 + "'");
                if (drr.Length > 0)
                {
                    dtin = drr.CopyToDataTable();
                }
                foreach (DataRow dr in dtin.Rows)
                {
                    string milktypes = dr["milktype"].ToString();
                    {
                        if (milktypes == "Buffalo")
                        {
                            double qty_ltr = 0;
                            double.TryParse(dr["qty_ltr"].ToString(), out qty_ltr);
                            double FAT = 0;
                            double.TryParse(dr["fat"].ToString(), out FAT);
                            FAT = Math.Round(FAT, 2);
                            double SNF = 0;
                            double.TryParse(dr["snf"].ToString(), out SNF);
                            string Rateon = dr["rate_on"].ToString();
                            double weight = 0;
                            double KGFAT  = 0;
                            double KGSNF  = 0;
                            double ltrs   = 0;
                            double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
                            double Kgs = 0;
                            double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
                            double tstotal = 0;
                            tstotal = FAT + SNF;
                            if (Rateon == "TS")
                            {
                                double TS = 0;
                                TS     = FAT + SNF;
                                weight = (TS * Kgs) / 100;
                                KGFAT  = (FAT * Kgs) / 100;
                                KGSNF  = (SNF * Kgs) / 100;
                            }
                            else if (Rateon == "KGFAT")
                            {
                                weight = (FAT * Kgs) / 100;
                                KGFAT  = (FAT * Kgs) / 100;
                                KGSNF  = (SNF * Kgs) / 100;
                            }
                            else if (Rateon == "PerLtr" || Rateon == "PerKg")
                            {
                                string CalOn = dr["calc_on"].ToString();
                                if (CalOn == "Ltrs")
                                {
                                    weight = ltrs;
                                    KGFAT  = (FAT * ltrs) / 100;
                                    KGSNF  = (SNF * ltrs) / 100;
                                }
                                else
                                {
                                    weight = Kgs;
                                    KGFAT  = (FAT * Kgs) / 100;
                                    KGSNF  = (SNF * Kgs) / 100;
                                }
                            }
                            double cost = 0;
                            double.TryParse(dr["cost"].ToString(), out cost);
                            KGFAT = Math.Round(KGFAT, 2);
                            KGSNF = Math.Round(KGSNF, 2);
                            double MValue = 0;
                            MValue = KGFAT * cost;
                            //MValue = MValue / 100;
                            MValue = Math.Round(MValue, 2);
                            string OverheadOn   = dr["overheadon"].ToString();
                            double OHcost       = 0;
                            double overheadcost = 0;
                            double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
                            if (OverheadOn == "Ltrs")
                            {
                                OHcost = overheadcost * ltrs;
                            }
                            else
                            {
                                OHcost = overheadcost * Kgs;
                            }
                            double MSnf = 0;
                            double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
                            double m_snfpluscost = 0;
                            double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
                            double DiffSNFCost = 0;
                            if (SNF < MSnf)
                            {
                                string SNFOn   = dr["snfplus_on"].ToString();
                                double diffSNF = 0;
                                diffSNF = SNF - MSnf;
                                diffSNF = Math.Round(diffSNF, 2);
                                if (SNFOn == "Ltrs")
                                {
                                    DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
                                }
                                else
                                {
                                    DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
                                }
                            }
                            double p_snfpluscost = 0;
                            double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
                            double PSnf = 0;
                            double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
                            if (SNF > PSnf)
                            {
                                string SNFOn   = dr["snfplus_on"].ToString();
                                double diffSNF = 0;
                                diffSNF = SNF - MSnf;
                                if (SNFOn == "Ltrs")
                                {
                                    DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
                                }
                                else
                                {
                                    DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
                                }
                            }
                            double OHandMvalue = 0;
                            OHandMvalue         = MValue + OHcost + DiffSNFCost;
                            OHandMvalue         = Math.Round(OHandMvalue, 2);
                            totsale            += OHandMvalue;
                            totsale             = Math.Ceiling(totsale);
                            newrow["Dc No"]     = dr["dcno"].ToString();
                            newrow["Dc Amount"] = totsale;
                        }
                        else
                        {
                            DateTime dtdoe = Convert.ToDateTime(dr["doe"].ToString());
                            string   date  = dtdoe.ToString("dd/MM/yyyy");
                            double   FAT   = 0;
                            double.TryParse(dr["fat"].ToString(), out FAT);
                            FAT = Math.Round(FAT, 2);
                            double SNF = 0;
                            double.TryParse(dr["snf"].ToString(), out SNF);
                            string Rateon = dr["rate_on"].ToString();
                            double weight = 0;
                            double KGFAT  = 0;
                            double KGSNF  = 0;
                            double ltrs   = 0;
                            double.TryParse(dr["qty_ltr"].ToString(), out ltrs);
                            double Kgs = 0;
                            double.TryParse(dr["qty_kgs"].ToString(), out Kgs);
                            double tstotal = 0;
                            tstotal = FAT + SNF;
                            tstotal = Math.Round(tstotal, 2);
                            if (Rateon == "TS")
                            {
                                double TS = 0;
                                TS     = FAT + SNF;
                                weight = (TS * Kgs) / 100;
                                KGFAT  = (FAT * Kgs) / 100;
                                KGSNF  = (SNF * Kgs) / 100;
                            }
                            else if (Rateon == "KGFAT")
                            {
                                weight = (FAT * Kgs) / 100;
                                KGFAT  = (FAT * Kgs) / 100;
                                KGSNF  = (SNF * Kgs) / 100;
                            }
                            else if (Rateon == "PerLtr" || Rateon == "PerKg")
                            {
                                string CalOn = dr["calc_on"].ToString();
                                if (CalOn == "Ltrs")
                                {
                                    weight = ltrs;
                                    KGFAT  = (FAT * ltrs) / 100;
                                    KGSNF  = (SNF * ltrs) / 100;
                                }
                                else
                                {
                                    weight = Kgs;
                                    KGFAT  = (FAT * Kgs) / 100;
                                    KGSNF  = (SNF * Kgs) / 100;
                                }
                            }
                            double cost = 0;
                            double.TryParse(dr["cost"].ToString(), out cost);
                            KGFAT = Math.Round(KGFAT, 2);
                            KGSNF = Math.Round(KGSNF, 2);
                            double MValue = 0;
                            if (Rateon == "PerLtr")
                            {
                                MValue = cost * ltrs;
                            }
                            else if (Rateon == "PerKg")
                            {
                                MValue = cost * Kgs;
                            }
                            else
                            {
                                string CalOn = dr["calc_on"].ToString();
                                if (CalOn == "Ltrs")
                                {
                                    MValue = tstotal * cost * ltrs;
                                    MValue = MValue / 100;
                                }
                                else
                                {
                                    MValue = tstotal * cost * Kgs;
                                    MValue = MValue / 100;
                                }
                            }
                            MValue = Math.Round(MValue, 2);
                            string OverheadOn   = dr["overheadon"].ToString();
                            double OHcost       = 0;
                            double overheadcost = 0;
                            double.TryParse(dr["overheadcost"].ToString(), out overheadcost);
                            if (OverheadOn == "Ltrs")
                            {
                                OHcost = overheadcost * ltrs;
                            }
                            else
                            {
                                OHcost = overheadcost * Kgs;
                            }
                            double MFat = 0;
                            double.TryParse(dr["m_std_fat"].ToString(), out MFat);
                            double m_fatpluscost = 0;
                            double.TryParse(dr["m_fatpluscost"].ToString(), out m_fatpluscost);
                            double DiffFATCost = 0;
                            if (FAT < MFat)
                            {
                                string FATOn   = dr["fatplus_on"].ToString();
                                double diffFAT = 0;
                                diffFAT = FAT - MFat;
                                diffFAT = Math.Round(diffFAT, 2);
                                if (FATOn == "Ltrs")
                                {
                                    DiffFATCost = diffFAT * ltrs * m_fatpluscost * 10;
                                }
                                else
                                {
                                    DiffFATCost = diffFAT * Kgs * m_fatpluscost * 10;
                                }
                            }
                            double p_fatpluscost = 0;
                            double.TryParse(dr["p_fatpluscost"].ToString(), out p_fatpluscost);
                            double PFat = 0;
                            double.TryParse(dr["p_std_fat"].ToString(), out PFat);
                            if (FAT > PFat)
                            {
                                string FATOn   = dr["fatplus_on"].ToString();
                                double diffFAT = 0;
                                diffFAT = FAT - MFat;
                                if (FATOn == "Ltrs")
                                {
                                    DiffFATCost = diffFAT * ltrs * p_fatpluscost * 10;
                                }
                                else
                                {
                                    DiffFATCost = diffFAT * Kgs * p_fatpluscost * 10;
                                }
                            }
                            DiffFATCost = Math.Round(DiffFATCost, 2);
                            double MSnf = 0;
                            double.TryParse(dr["m_std_snf"].ToString(), out MSnf);
                            double m_snfpluscost = 0;
                            double.TryParse(dr["m_snfpluscost"].ToString(), out m_snfpluscost);
                            double DiffSNFCost = 0;
                            if (SNF < MSnf)
                            {
                                string SNFOn   = dr["snfplus_on"].ToString();
                                double diffSNF = 0;
                                diffSNF = SNF - MSnf;
                                diffSNF = Math.Round(diffSNF, 2);
                                if (SNFOn == "Ltrs")
                                {
                                    DiffSNFCost = diffSNF * ltrs * m_snfpluscost * 10;
                                }
                                else
                                {
                                    DiffSNFCost = diffSNF * Kgs * m_snfpluscost * 10;
                                }
                            }
                            double p_snfpluscost = 0;
                            double.TryParse(dr["p_snfpluscost"].ToString(), out p_snfpluscost);
                            double PSnf = 0;
                            double.TryParse(dr["p_std_snf"].ToString(), out PSnf);
                            if (SNF > PSnf)
                            {
                                string SNFOn   = dr["snfplus_on"].ToString();
                                double diffSNF = 0;
                                diffSNF = SNF - MSnf;
                                if (SNFOn == "Ltrs")
                                {
                                    DiffSNFCost = diffSNF * ltrs * p_snfpluscost * 10;
                                }
                                else
                                {
                                    DiffSNFCost = diffSNF * Kgs * p_snfpluscost * 10;
                                }
                            }

                            double OHandMvalue = 0;
                            OHandMvalue         = MValue + OHcost + DiffSNFCost + DiffFATCost;
                            DiffSNFCost         = Math.Round(DiffSNFCost, 2);
                            OHandMvalue         = Math.Round(OHandMvalue, 2);
                            totsale            += OHandMvalue;
                            totsale             = Math.Ceiling(totsale);
                            newrow["Dc No"]     = dr["dcno"].ToString();
                            newrow["Dc Amount"] = totsale;
                        }
                    }
                }
                double    amtpaid      = 0;
                DataTable dtamountcol  = new DataTable();
                DataRow[] drramountcol = dtcollection.Select("doe='" + ChangedTime1 + "'");
                if (drramountcol.Length > 0)
                {
                    dtamountcol = drramountcol.CopyToDataTable();
                }
                double amount = 0;

                foreach (DataRow dr in dtamountcol.Rows)
                {
                    double.TryParse(dr["amount"].ToString(), out amount);
                    amtpaid += amount;
                }
                ///Get Opp Bal according to Erp Formula ..By Srinu
                double aopp = 0;
                if (i == 1)
                {
                    aopp = vendorpresentopp;
                }
                else
                {
                    aopp = vendorpresentopp;
                    if (totsale == 0)
                    {
                        aopp = oppcarry;
                    }
                }
                newrow["Opp Bal"] = Math.Ceiling(aopp);
                double totalamt = aopp + totsale;

                totalamt = Math.Ceiling(totalamt);
                newrow["Total Amount"] = totalamt;
                amtpaid = Math.Ceiling(amtpaid);
                if (ddlcollectiontype.SelectedValue == "Collection")
                {
                    newrow["Received Amount"] = amtpaid;
                }
                else
                {
                    newrow["Paid Amount"] = amtpaid;
                }
                totalpaidamount += amtpaid;
                double totalbalance = totalamt - amtpaid;
                totalbalance      = Math.Ceiling(totalbalance);
                newrow["Balance"] = totalbalance;
                oppcarry          = totalbalance;
                vendorpresentopp  = totalbalance;
                Report.Rows.Add(newrow);
                i++;
                lblclosingbalance.Text = totalbalance.ToString();
            }
            DataRow newrow2 = Report.NewRow();
            newrow2["Date"]      = "Total";
            newrow2["Dc Amount"] = totsalesamount;
            if (ddlcollectiontype.SelectedValue == "Collection")
            {
                newrow2["Received Amount"] = totalpaidamount;
            }
            else
            {
                newrow2["Paid Amount"] = totalpaidamount;
            }
            Report.Rows.Add(newrow2);
            grdReports.DataSource = Report;
            grdReports.DataBind();
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.Message;
        }
    }
示例#23
0
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        try
        {
            vdm = new SalesDBManager();
            DateTime    CreateDate    = SalesDBManager.GetTime(vdm.conn);
            SAPdbmanger SAPvdm        = new SAPdbmanger();
            DateTime    fromdate      = DateTime.Now;
            DataTable   dt            = (DataTable)Session["xportdata"];
            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);
                }
            }
            foreach (DataRow dr in dt.Rows)
            {
                string PaymentMode = "CASH";
                double amount      = 0;
                double.TryParse(dr["Amount"].ToString(), out amount);
                string   B1Upload  = "N";
                string   Processed = "N";
                string   date      = dr["Voucher Date"].ToString();
                DateTime from_date = Convert.ToDateTime(date);
                sqlcmd = new SqlCommand("Insert into EMROJDTP (CreateDate, RefDate, DocDate, TransNo,TransCode, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,Remarks) values (@CreateDate, @RefDate, @DocDate,@TransNo,@TransCode, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@Remarks)");
                sqlcmd.Parameters.Add("@CreateDate", CreateDate);
                sqlcmd.Parameters.Add("@RefDate", GetLowDate(from_date));
                sqlcmd.Parameters.Add("@docdate", GetLowDate(from_date));
                sqlcmd.Parameters.Add("@Ref1", dr["Voucher No"].ToString());
                string TransCode = "T1";
                sqlcmd.Parameters.Add("@TransNo", dr["Voucher No"].ToString());
                sqlcmd.Parameters.Add("@TransCode", TransCode);
                sqlcmd.Parameters.Add("@AcctCode", dr["Debit Code"].ToString());
                sqlcmd.Parameters.Add("@AcctName", dr["Ledger (Dr)"].ToString());
                double.TryParse(dr["Amount"].ToString(), out amount);
                sqlcmd.Parameters.Add("@Debit", amount);
                string Creditamount = "0";
                sqlcmd.Parameters.Add("@Credit", Creditamount);
                sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                sqlcmd.Parameters.Add("@Processed", Processed);
                sqlcmd.Parameters.Add("@OcrCode", dr["whcode"].ToString());
                sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
                SAPvdm.insert(sqlcmd);

                sqlcmd = new SqlCommand("Insert into EMROJDTP (CreateDate, RefDate, DocDate, TransNo,TransCode, AcctCode, AcctName, Debit, Credit, B1Upload, Processed,Ref1,OcrCode,Remarks) values (@CreateDate, @RefDate, @DocDate,@TransNo,@TransCode, @AcctCode, @AcctName, @Debit, @Credit, @B1Upload, @Processed,@Ref1,@OcrCode,@Remarks)");
                sqlcmd.Parameters.Add("@CreateDate", CreateDate);
                sqlcmd.Parameters.Add("@RefDate", GetLowDate(from_date));
                sqlcmd.Parameters.Add("@docdate", GetLowDate(from_date));
                sqlcmd.Parameters.Add("@Ref1", dr["Voucher No"].ToString());
                sqlcmd.Parameters.Add("@TransNo", dr["Voucher No"].ToString());
                sqlcmd.Parameters.Add("@TransCode", TransCode);
                sqlcmd.Parameters.Add("@AcctCode", dr["Credit Code"].ToString());
                sqlcmd.Parameters.Add("@AcctName", dr["Ledger (Cr)"].ToString());
                string Debitamount = "0";
                sqlcmd.Parameters.Add("@Debit", Debitamount);
                sqlcmd.Parameters.Add("@Credit", amount);
                sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                sqlcmd.Parameters.Add("@Processed", Processed);
                sqlcmd.Parameters.Add("@OcrCode", dr["whcode"].ToString());
                sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
                SAPvdm.insert(sqlcmd);
            }
            pnlHide.Visible = false;
            DataTable dtempty = new DataTable();
            grdReports.DataSource = dtempty;
            grdReports.DataBind();
            lblmsg.Text = "Successfully Saved";
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }
    protected void btn_Generate_Click(object sender, EventArgs e)
    {
        try
        {
            Report.Columns.Add("Ledger Type");
            Report.Columns.Add("Customer Name");
            Report.Columns.Add("Invoce No.");
            Report.Columns.Add("Invoice Date");
            Report.Columns.Add("Item Name");
            Report.Columns.Add("Qty");
            Report.Columns.Add("Rate");
            Report.Columns.Add("Taxable Value");
            Report.Columns.Add("SGST%");
            Report.Columns.Add("SGST Amount");
            Report.Columns.Add("CGST%");
            Report.Columns.Add("CGST Amount");
            Report.Columns.Add("IGST%");
            Report.Columns.Add("IGST Amount");
            Report.Columns.Add("Net Value");
            Report.Columns.Add("Narration");
            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");
            string branch_id = Session["Po_BranchID"].ToString();
            string bid       = ddlbranch.SelectedItem.Value;

            cmd = new SqlCommand("SELECT stocktransferdetails.salesinvoiceno, stocktransfersubdetails.igst, stocktransfersubdetails.cgst, stocktransfersubdetails.sgst, stocktransferdetails.salesstno, stocktransferdetails.transportname, stocktransferdetails.remarks, stocktransferdetails.invoicetype,stocktransferdetails.invoiceno, stocktransferdetails.invoicedate AS invoicedate, stocktransferdetails.vehicleno,stocktransfersubdetails.price,stocktransfersubdetails.taxvalue,stocktransfersubdetails.taxtype,stocktransfersubdetails.freigtamt,stocktransferdetails.sno as refno,stocktransfersubdetails.sno,branchmaster.branchname, branchmaster_1.branchname AS BranchName, productmaster.productname, stocktransferdetails.frombranch, stocktransferdetails.tobranch,  stocktransfersubdetails.quantity, stocktransfersubdetails.stock_refno, stocktransfersubdetails.productid, stocktransferdetails.doe FROM  stocktransferdetails INNER JOIN stocktransfersubdetails ON stocktransferdetails.sno = stocktransfersubdetails.stock_refno LEFT OUTER JOIN  branchmaster ON stocktransferdetails.frombranch = branchmaster.branchid LEFT OUTER JOIN branchmaster AS branchmaster_1 ON stocktransferdetails.tobranch = branchmaster_1.branchid INNER JOIN productmaster ON stocktransfersubdetails.productid = productmaster.productid where (stocktransferdetails.invoicedate between @fromdate and @todate) AND (stocktransferdetails.tobranch=@branch_id) AND (stocktransferdetails.branchid=@MAINBRANCH) and (stocktransfersubdetails.quantity>0)");
            cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
            cmd.Parameters.Add("@todate", GetHighDate(todate));
            cmd.Parameters.Add("@branch_id", bid);
            cmd.Parameters.Add("@MAINBRANCH", branch_id);

            DataTable dtstocktransfer = SalesDB.SelectQuery(cmd).Tables[0];
            if (dtstocktransfer.Rows.Count > 0)
            {
                double total_amount = 0, totamt = 0, totalprice = 0, totalqty = 0, totaltaxamt = 0, gtotaltaxamt = 0, taxamount = 0, ttaxamount = 0, ttotalamount = 0;
                int    count = 1;
                int    rowcount = 1;
                double tfreight = 0, gtotalqty = 0, gtotalamount = 0, gfrightamt = 0, gtaxamount = 0;
                string previnvoiceno = "";


                DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
                DateTime dtapril       = new DateTime();
                DateTime dtmarch       = new DateTime();
                int      currentyear   = ServerDateCurrentdate.Year;
                int      nextyear      = ServerDateCurrentdate.Year + 1;
                int      currntyearnum = 0;
                int      nextyearnum   = 0;
                if (ServerDateCurrentdate.Month > 3)
                {
                    string apr = "4/1/" + currentyear;
                    dtapril = DateTime.Parse(apr);
                    string march = "3/31/" + nextyear;
                    dtmarch       = DateTime.Parse(march);
                    currntyearnum = currentyear;
                    nextyearnum   = nextyear;
                }
                if (ServerDateCurrentdate.Month <= 3)
                {
                    string apr = "4/1/" + (currentyear - 1);
                    dtapril = DateTime.Parse(apr);
                    string march = "3/31/" + (nextyear - 1);
                    dtmarch       = DateTime.Parse(march);
                    currntyearnum = currentyear - 1;
                    nextyearnum   = nextyear - 1;
                }
                cmd = new SqlCommand("select statemaster.sno,statemaster.code AS statecode,statemaster.gststatecode,statemaster.statename,branchmaster.GSTIN,branchmaster.address,branchmaster.branchname from statemaster INNER JOIN branchmaster ON statemaster.sno = branchmaster.statename WHERE branchmaster.branchid = @branch_id");
                cmd.Parameters.Add("@branch_id", branch_id);
                DataTable dt_branch            = vdm.SelectQuery(cmd).Tables[0];
                string    fromstate            = dt_branch.Rows[0]["statename"].ToString();
                string    frombranch_gstin     = dt_branch.Rows[0]["GSTIN"].ToString();
                string    frombranch_address   = dt_branch.Rows[0]["address"].ToString();
                string    frombranch_stateid   = dt_branch.Rows[0]["gststatecode"].ToString();
                string    frombranch_statesno  = dt_branch.Rows[0]["sno"].ToString();
                string    frombranch_name      = dt_branch.Rows[0]["branchname"].ToString();
                string    frombranch_statecode = dt_branch.Rows[0]["statecode"].ToString();
                foreach (DataRow dr in dtstocktransfer.Rows)
                {
                    string date = dr["invoicedate"].ToString();
                    if (date != "")
                    {
                        DataRow newrow     = Report.NewRow();
                        string  ledgertype = "Sales Accounts";
                        string  bname      = dr["BranchName1"].ToString();
                        newrow["Ledger Type"]   = ledgertype;
                        newrow["Customer Name"] = bname;
                        string invoiceno      = dr["invoiceno"].ToString();
                        string salesinvoiceno = dr["salesinvoiceno"].ToString();
                        string stnumber       = "";

                        double igst = 0;
                        double.TryParse(dr["igst"].ToString(), out igst);
                        double cgst = 0;
                        double.TryParse(dr["cgst"].ToString(), out cgst);
                        double sgst = 0;
                        double.TryParse(dr["sgst"].ToString(), out sgst);

                        if (salesinvoiceno == "0")
                        {
                            salesinvoiceno = dr["salesstno"].ToString();
                            string newreceipt = "0";
                            int    countdc    = 0;
                            int.TryParse(salesinvoiceno, out countdc);
                            if (countdc < 10)
                            {
                                newreceipt = "000" + countdc;
                            }
                            if (countdc >= 10 && countdc <= 99)
                            {
                                newreceipt = "00" + countdc;
                            }
                            if (countdc >= 99 && countdc <= 999)
                            {
                                newreceipt = "0" + countdc;
                            }
                            if (countdc > 999)
                            {
                                newreceipt = "" + countdc;
                            }
                            stnumber = frombranch_statecode + "/ST" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newreceipt;
                        }
                        else
                        {
                            string newreceipt = "0";
                            int    countdc    = 0;
                            int.TryParse(salesinvoiceno, out countdc);
                            if (countdc < 10)
                            {
                                newreceipt = "000" + countdc;
                            }
                            if (countdc >= 10 && countdc <= 99)
                            {
                                newreceipt = "00" + countdc;
                            }
                            if (countdc >= 99 && countdc <= 999)
                            {
                                newreceipt = "0" + countdc;
                            }
                            if (countdc > 999)
                            {
                                newreceipt = "" + countdc;
                            }
                            double tax = igst + sgst + cgst;
                            if (tax > 0)
                            {
                                stnumber = frombranch_statecode + "/T" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newreceipt;
                            }
                            else
                            {
                                stnumber = frombranch_statecode + "/N" + dtapril.ToString("yy") + "-" + dtmarch.ToString("yy") + "/" + newreceipt;
                            }
                        }
                        DateTime dtdate       = Convert.ToDateTime(date);
                        string   invoicedate  = dtdate.ToString("dd-MMM-yyyy");
                        string   invoicedate1 = dtdate.ToString("dd/MM/yyyy");
                        newrow["Invoce No."]   = stnumber;
                        newrow["Invoice Date"] = invoicedate;
                        newrow["Item Name"]    = dr["productname"].ToString();
                        double price = 0;
                        double.TryParse(dr["price"].ToString(), out price);
                        totalprice    += price;
                        newrow["Rate"] = Math.Round(price, 2).ToString("f2"); //dr["price"].ToString();
                        double qty = 0;
                        double.TryParse(dr["quantity"].ToString(), out qty);
                        totalqty     += qty;
                        newrow["Qty"] = dr["quantity"].ToString();
                        string taxper = dr["taxvalue"].ToString();

                        double total = 0; double totamount = 0;
                        total = qty * price;
                        newrow["Taxable Value"] = Math.Round(total, 2).ToString("f2");
                        newrow["SGST%"]         = sgst;
                        newrow["CGST%"]         = cgst;
                        newrow["IGST%"]         = igst;
                        double igstamount = (total * igst) / 100;
                        double cgstamount = (total * cgst) / 100;
                        double sgstamount = (total * sgst) / 100;
                        newrow["SGST Amount"] = Math.Round(sgstamount, 2).ToString("f2");
                        newrow["CGST Amount"] = Math.Round(cgstamount, 2).ToString("f2");
                        newrow["IGST Amount"] = Math.Round(igstamount, 2).ToString("f2");
                        double netvalue = total + sgstamount + cgstamount + igstamount;
                        totamount += total;
                        double freight = 0;
                        double.TryParse(dr["freigtamt"].ToString(), out freight);
                        tfreight += freight;
                        totamount = netvalue + freight;
                        double totalamount = 0;
                        totalamount         = totamount;
                        newrow["Net Value"] = Math.Round(totalamount, 2).ToString("f2");
                        ttotalamount       += totalamount;
                        newrow["Narration"] = "Being  " + dr["remarks"].ToString() + "  From SVDS Punabaka,Invoice No: " + stnumber + ",Inv Dt" + invoicedate1 + ",Amount" + totalamount + "";//dr["invoicedate"].ToString()
                        Report.Rows.Add(newrow);
                    }
                }
                Session["xportdata"]  = Report;
                Session["filename"]   = "Stores Branch Transfer report";
                grdReports.DataSource = Report;
                grdReports.DataBind();
                hidepanel.Visible = true;
            }
            else
            {
                grdReports.DataSource = null;
                grdReports.DataBind();
            }
        }
        catch (Exception ex)
        {
        }
    }
示例#25
0
    void GetReport()
    {
        try
        {
            lblmsg.Text          = "";
            pnlHide.Visible      = true;
            Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
            Session["IDate"]     = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
            vdm = new SalesDBManager();
            DateTime fromdate = DateTime.Now;
            DateTime todate   = 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);
                }
            }
            dateFromstrig = dtp_Todate.Text.Split(' ');
            if (dateFromstrig.Length > 1)
            {
                if (dateFromstrig[0].Split('-').Length > 0)
                {
                    string[] dates = dateFromstrig[0].Split('-');
                    string[] times = dateFromstrig[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);
                }
            }
            DataTable Report = new DataTable();
            Report.Columns.Add("DOE");
            Report.Columns.Add("Ref Receipt");
            Report.Columns.Add("Receipt");
            Report.Columns.Add("Type");
            Report.Columns.Add("Name");
            Report.Columns.Add("Amount").DataType = typeof(Double);
            lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
            lblRoutName.Text     = ddlSalesOffice.SelectedItem.Text;
            DateTime ReportDate  = SalesDBManager.GetTime(vdm.conn);
            DateTime dtapril     = new DateTime();
            DateTime dtmarch     = new DateTime();
            int      currentyear = ReportDate.Year;
            int      nextyear    = ReportDate.Year + 1;
            if (ReportDate.Month > 3)
            {
                string apr = "4/1/" + currentyear;
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + nextyear;
                dtmarch = DateTime.Parse(march);
            }
            if (ReportDate.Month <= 3)
            {
                string apr = "4/1/" + (currentyear - 1);
                dtapril = DateTime.Parse(apr);
                string march = "3/31/" + (nextyear - 1);
                dtmarch = DateTime.Parse(march);
            }
            Session["xporttype"] = "TallyPayments";
            string ledger = "";
            Session["filename"] = ddlSalesOffice.SelectedItem.Text + " Tally Payments" + fromdate.ToString("dd/MM/yyyy");
            cmd = new SqlCommand("SELECT cashpayables.DOE, cashpayables.VocherID, subpayable.vouchercode, cashpayables.Sno, subpayable.HeadSno, cashpayables.Remarks,cashpayables.Remarks AS Expr1, subpayable.sno AS Expr2, subpayable.Amount, accountheads.HeadName,accountheads.ledger_code,  cashpayables.BranchID, Plant_Master.ladger_dr,Plant_Master.WHcode, Plant_Master.ladger_dr_code FROM cashpayables INNER JOIN subpayable ON cashpayables.Sno = subpayable.RefNo INNER JOIN accountheads ON subpayable.HeadSno = accountheads.Sno INNER JOIN Plant_Master ON cashpayables.BranchID = Plant_Master.Plant_Code WHERE (cashpayables.BranchID = @BranchID) AND (cashpayables.DOE BETWEEN @d1 AND @d2) AND (cashpayables.VoucherType = 'Debit') AND  (cashpayables.Status = 'P')");
            cmd.Parameters.Add("@BranchID", ddlSalesOffice.SelectedValue);
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", GetHighDate(todate));
            DataTable dtAgent = vdm.SelectQuery(cmd).Tables[0];
            if (dtAgent.Rows.Count > 0)
            {
                DataView view = new DataView(dtAgent);
                dtReport = new DataTable();
                dtReport.Columns.Add("Voucher Date");
                dtReport.Columns.Add("Voucher No");
                dtReport.Columns.Add("Voucher Type");
                dtReport.Columns.Add("whcode");
                //dtReport.Columns.Add("ApprovedBy");
                dtReport.Columns.Add("Credit Code");
                dtReport.Columns.Add("Ledger (Cr)");
                dtReport.Columns.Add("Debit Code");
                dtReport.Columns.Add("Ledger (Dr)");
                dtReport.Columns.Add("Amount");
                dtReport.Columns.Add("Narration");
                int i = 1;
                foreach (DataRow branch in dtAgent.Rows)
                {
                    string VoucherNo = "";
                    cmd = new SqlCommand("SELECT  vouchercode,RefNo, HeadDesc, Amount, HeadSno, sno, branchid, paiddate FROM subpayable  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", GetLowDate(fromdate));
                    DataTable dtVoucher = vdm.SelectQuery(cmd).Tables[0];
                    if (dtVoucher.Rows.Count > 0)
                    {
                        DataRow[] drvoucher = dtVoucher.Select("branchid='" + ddlSalesOffice.SelectedValue + "' and RefNo='" + branch["sno"].ToString() + "' and HeadSno='" + branch["HeadSno"].ToString() + "'");
                        if (drvoucher.Length > 0)
                        {
                            foreach (DataRow drv in drvoucher)
                            {
                                VoucherNo = drv.ItemArray[0].ToString();
                            }
                        }
                        else
                        {
                            cmd = new SqlCommand("SELECT  ISNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid)  AND (paiddate BETWEEN @d1 AND @d2)");
                            cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
                            cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
                            cmd.Parameters.Add("@d1", GetLowDate(dtapril.AddDays(-1)));
                            cmd.Parameters.Add("@d2", GetHighDate(dtmarch.AddDays(-1)));
                            DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
                            VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
                            cmd       = new SqlCommand("update  subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid  where (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
                            cmd.Parameters.Add("@vouchercode", VoucherNo);
                            cmd.Parameters.Add("@paiddate", fromdate);
                            cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
                            cmd.Parameters.Add("@RefNo", branch["sno"].ToString());
                            cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
                            vdm.Update(cmd);
                        }
                    }
                    else
                    {
                        cmd = new SqlCommand("SELECT ISNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid)  AND (paiddate BETWEEN @d1 AND @d2)");
                        cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
                        cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
                        cmd.Parameters.Add("@d1", GetLowDate(dtapril.AddDays(-1)));
                        cmd.Parameters.Add("@d2", GetHighDate(dtmarch.AddDays(-1)));
                        DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
                        VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
                        cmd       = new SqlCommand("update  subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid  where  (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
                        cmd.Parameters.Add("@vouchercode", VoucherNo);
                        cmd.Parameters.Add("@paiddate", fromdate);
                        cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
                        cmd.Parameters.Add("@RefNo", branch["sno"].ToString());
                        cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
                        vdm.Update(cmd);
                    }
                    if (VoucherNo == "0")
                    {
                        cmd = new SqlCommand("SELECT IFNULL(MAX(vouchercode), 0) + 1 AS Sno FROM subpayable WHERE (branchid = @branchid)  AND (paiddate BETWEEN @d1 AND @d2)");
                        cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
                        cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
                        cmd.Parameters.Add("@d1", GetLowDate(dtapril.AddDays(-1)));
                        cmd.Parameters.Add("@d2", GetHighDate(dtmarch.AddDays(-1)));
                        DataTable dtvoucherno = vdm.SelectQuery(cmd).Tables[0];
                        VoucherNo = dtvoucherno.Rows[0]["Sno"].ToString();
                        cmd       = new SqlCommand("update  subpayable set vouchercode=@vouchercode, paiddate=@paiddate,branchid=@branchid  where  (RefNo=@RefNo) AND (HeadSno = @HeadSno)");;
                        cmd.Parameters.Add("@vouchercode", VoucherNo);
                        cmd.Parameters.Add("@paiddate", fromdate);
                        cmd.Parameters.Add("@branchid", ddlSalesOffice.SelectedValue);
                        cmd.Parameters.Add("@RefNo", branch["sno"].ToString());
                        cmd.Parameters.Add("@HeadSno", branch["HeadSno"].ToString());
                        vdm.Update(cmd);
                    }
                    string NewVoucherNo = "0";
                    int    countdc      = 0;
                    int.TryParse(VoucherNo, out countdc);
                    if (countdc <= 10)
                    {
                        NewVoucherNo = "000" + countdc;
                    }
                    if (countdc >= 10 && countdc <= 99)
                    {
                        NewVoucherNo = "00" + countdc;
                    }
                    if (countdc >= 99 && countdc <= 999)
                    {
                        NewVoucherNo = "0" + countdc;
                    }
                    DataRow newrow = dtReport.NewRow();

                    string   Date      = branch["DOE"].ToString();
                    DateTime from_date = Convert.ToDateTime(Date);
                    newrow["Voucher Date"] = from_date.ToString("dd-MMM-yyyy");
                    newrow["Voucher No"]   = ddlSalesOffice.SelectedValue + dtapril.ToString("yy") + dtmarch.ToString("yy") + NewVoucherNo;
                    newrow["Voucher Type"] = "Cash Payment Import";
                    newrow["Ledger (Cr)"]  = branch["ladger_dr"].ToString();
                    newrow["Debit Code"]   = branch["ledger_code"].ToString();
                    newrow["Credit Code"]  = branch["ladger_dr_code"].ToString();
                    newrow["Ledger (Dr)"]  = branch["HeadName"].ToString();
                    newrow["Amount"]       = branch["Amount"].ToString();
                    newrow["whcode"]       = branch["WHcode"].ToString();
                    double invval = 0;
                    newrow["Narration"] = branch["Remarks"].ToString() + ",VoucherID  " + VoucherNo + ",Emp Name  " + Session["EmpName"].ToString();
                    dtReport.Rows.Add(newrow);
                    i++;
                }
                grdReports.DataSource = dtReport;
                grdReports.DataBind();
                Session["xportdata"] = dtReport;
            }
            else
            {
                pnlHide.Visible       = false;
                lblmsg.Text           = "No Indent Found";
                grdReports.DataSource = dtReport;
                grdReports.DataBind();
            }
        }
        catch (Exception ex)
        {
            lblmsg.Text           = ex.Message;
            grdReports.DataSource = dtReport;
            grdReports.DataBind();
        }
    }
示例#26
0
    private void report()
    {
        try
        {
            lblmsg.Text = "";
            string         milkopeningbal        = string.Empty;
            string         milkclosingbal        = string.Empty;
            SalesDBManager SalesDB               = new SalesDBManager();
            DateTime       fromdate              = DateTime.Now;
            DateTime       todate                = DateTime.Now;
            string         idcno                 = string.Empty;
            string         inworddate            = string.Empty;
            double         totalinwardqty        = 0;
            double         totalissueqty         = 0;
            double         totaltransferquantity = 0;
            double         tranqtydateqty        = 0;
            double         totalReturnqty        = 0;
            double         returndateqty         = 0;
            double         totinwardvalue        = 0;
            double         totoutwardvalue       = 0;
            double         tottransfervalue      = 0;
            double         totreturnvalue        = 0;
            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");
            string productid = TextBox1.Text;
            Report.Columns.Add("Sno");
            Report.Columns.Add("Date");
            Report.Columns.Add("Opp Qty");
            Report.Columns.Add("Opp Price");
            Report.Columns.Add("Opp Value");
            Report.Columns.Add("ReceiptsQty").DataType = typeof(double);
            Report.Columns.Add("ReceiptsPrice");
            Report.Columns.Add("ReceiptsValue").DataType = typeof(double);
            Report.Columns.Add("ReturnQty").DataType     = typeof(double);
            Report.Columns.Add("ReturnPrice");
            Report.Columns.Add("ReturnValue").DataType = typeof(double);

            Report.Columns.Add("IssuesQty").DataType = typeof(double);
            Report.Columns.Add("IssuesPrice");
            Report.Columns.Add("IssuesValue").DataType = typeof(double);

            Report.Columns.Add("IssuesQty1").DataType = typeof(double);
            Report.Columns.Add("IssuesPrice1");
            Report.Columns.Add("IssuesValue1").DataType = typeof(double);

            Report.Columns.Add("TransferQty").DataType = typeof(double);
            Report.Columns.Add("TransferPrice");
            Report.Columns.Add("TransferValue").DataType = typeof(double);

            Report.Columns.Add("TransferQty1").DataType = typeof(double);
            Report.Columns.Add("Transferprice1");
            Report.Columns.Add("Transfervale1").DataType = typeof(double);


            Report.Columns.Add("Clo Qty");
            Report.Columns.Add("Clo Price");
            Report.Columns.Add("Clo Value");
            hidepanel.Visible   = true;
            Session["filename"] = "Ledger";
            Session["title"]    = "Ledger Details";
            TimeSpan dateSpan = todate.Subtract(fromdate);
            int      NoOfdays = dateSpan.Days;
            NoOfdays = NoOfdays + 1;
            DateTime ServerDateCurrentdate = SalesDBManager.GetTime(vdm.conn);
            string   branchid = Session["Po_BranchID"].ToString();

            // inward
            cmd = new SqlCommand("SELECT SUM(subinwarddetails.quantity) AS inwardqty,subinwarddetails.perunit,SUM(subinwarddetails.quantity*subinwarddetails.perunit) AS inwardvalue, productmaster.productname, subinwarddetails.tax, subinwarddetails.edtax,  inwarddetails.inwarddate AS inwarddate  FROM inwarddetails INNER JOIN subinwarddetails ON inwarddetails.sno = subinwarddetails.in_refno INNER JOIN productmaster ON subinwarddetails.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (inwarddetails.inwarddate BETWEEN @d1 AND @d2) AND (inwarddetails.branchid=@branchid) AND (subinwarddetails.quantity>0) AND (inwarddetails.status='A') GROUP BY productmaster.productname,subinwarddetails.perunit, inwarddetails.inwarddate, subinwarddetails.tax, subinwarddetails.edtax");
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", ServerDateCurrentdate);
            cmd.Parameters.Add("@ProductID", productid);
            cmd.Parameters.Add("@branchid", branchid);
            DataTable dtinward = SalesDB.SelectQuery(cmd).Tables[0];
            double    inquantity = 0; double suminvalue = 0;
            foreach (DataRow drin in dtinward.Rows)
            {
                double.TryParse(drin["inwardqty"].ToString(), out inquantity);
                double.TryParse(drin["inwardvalue"].ToString(), out suminvalue);
                totalinwardqty += inquantity;
                totinwardvalue += suminvalue;
            }

            // outward (or) issue
            cmd = new SqlCommand("SELECT SUM(suboutwarddetails.quantity) AS issueqty,suboutwarddetails.perunit,SUM(suboutwarddetails.quantity*suboutwarddetails.perunit) AS outwardvalue,productmaster.productname, outwarddetails.inwarddate AS outwarddate FROM outwarddetails INNER JOIN suboutwarddetails ON outwarddetails.sno = suboutwarddetails.in_refno INNER JOIN productmaster ON suboutwarddetails.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (outwarddetails.inwarddate BETWEEN @d1 AND @d2) AND (outwarddetails.branchid=@branchid) AND (suboutwarddetails.quantity>0) AND (outwarddetails.status='A') GROUP BY productmaster.productname,suboutwarddetails.perunit, outwarddetails.inwarddate");
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", GetLowDate(todate));
            cmd.Parameters.Add("@ProductID", productid);
            cmd.Parameters.Add("@branchid", branchid);
            DataTable dtoutward = SalesDB.SelectQuery(cmd).Tables[0];
            double    outquantity = 0; string outwarddate; double sumoutvalue = 0;
            foreach (DataRow drout in dtoutward.Rows)
            {
                double.TryParse(drout["issueqty"].ToString(), out outquantity);
                double.TryParse(drout["outwardvalue"].ToString(), out sumoutvalue);
                totalissueqty   += outquantity;
                totoutwardvalue += sumoutvalue;
            }

            // stocktransfer

            cmd = new SqlCommand("SELECT SUM(stocktransfersubdetails.quantity) AS transferqty,stocktransfersubdetails.price, productmaster.productname,SUM(stocktransfersubdetails.quantity*stocktransfersubdetails.price) AS transfervalue,  stocktransferdetails.invoicedate  FROM stocktransferdetails INNER JOIN stocktransfersubdetails ON stocktransferdetails.sno = stocktransfersubdetails.stock_refno INNER JOIN productmaster ON stocktransfersubdetails.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (stocktransferdetails.invoicedate BETWEEN @d1 AND @d2) AND (stocktransferdetails.branchid=@branchid) and (stocktransfersubdetails.quantity>0) and (stocktransferdetails.status='A') GROUP BY productmaster.productname,stocktransfersubdetails.price,stocktransfersubdetails.price, stocktransferdetails.invoicedate");
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", ServerDateCurrentdate);
            cmd.Parameters.Add("@ProductID", productid);
            cmd.Parameters.Add("@branchid", branchid);
            DataTable dttransfer = SalesDB.SelectQuery(cmd).Tables[0];
            double    transferquantity = 0; string invoicedate; double sumtransfervalue = 0;
            foreach (DataRow drtran in dttransfer.Rows)
            {
                double.TryParse(drtran["transferqty"].ToString(), out transferquantity);
                double.TryParse(drtran["transfervalue"].ToString(), out sumtransfervalue);
                totaltransferquantity += transferquantity;
                tottransfervalue      += sumtransfervalue;
                // outwarddate = drout["transferqty"].ToString();
            }

            // returns
            cmd = new SqlCommand("SELECT SUM(sub_stores_return.quantity) AS returnqty,sub_stores_return.perunit,SUM(sub_stores_return.quantity*sub_stores_return.perunit) AS returnvalue, productmaster.productname, stores_return.doe  FROM stores_return INNER JOIN sub_stores_return ON stores_return.sno = sub_stores_return.storesreturn_sno INNER JOIN productmaster ON sub_stores_return.productid = productmaster.productid WHERE (productmaster.productname = @ProductID) AND (stores_return.doe BETWEEN @d1 AND @d2) AND (stores_return.branchid=@branchid) AND (sub_stores_return.quantity>0) GROUP BY productmaster.productname, stores_return.doe,sub_stores_return.perunit");
            cmd.Parameters.Add("@d1", GetLowDate(fromdate));
            cmd.Parameters.Add("@d2", ServerDateCurrentdate);
            cmd.Parameters.Add("@ProductID", productid);
            cmd.Parameters.Add("@branchid", branchid);
            DataTable dtreturn = SalesDB.SelectQuery(cmd).Tables[0];
            double    Returnqty = 0; double sumreturnvalue = 0;
            foreach (DataRow drreturn in dtreturn.Rows)
            {
                double.TryParse(drreturn["returnqty"].ToString(), out Returnqty);
                double.TryParse(drreturn["returnvalue"].ToString(), out sumreturnvalue);
                totalReturnqty += Returnqty;
                totreturnvalue += sumreturnvalue;
            }

            cmd = new SqlCommand("SELECT Distinct stockclosingdetails.productid,stockclosingdetails.qty, stockclosingdetails.price, (stockclosingdetails.qty * stockclosingdetails.price) opvalue, stockclosingdetails.branchid, productmaster.productname FROM  stockclosingdetails INNER JOIN productmaster ON stockclosingdetails.productid = productmaster.productid WHERE (productmaster.productname = @productid) AND (stockclosingdetails.branchid = @branchid) AND (stockclosingdetails.doe BETWEEN @d11 and @d12) GROUP BY stockclosingdetails.productid,stockclosingdetails.qty, stockclosingdetails.branchid, stockclosingdetails.price, productmaster.productname");
            cmd.Parameters.Add("@productid", productid);
            cmd.Parameters.Add("@branchid", branchid);
            cmd.Parameters.Add("@d11", GetLowDate(fromdate));
            cmd.Parameters.Add("@d12", GetHighDate(fromdate));
            DataTable dtProduct_presentopp = vdm.SelectQuery(cmd).Tables[0];
            double    productpresentopp = 0; double productpresenvalue = 0; double openingprice = 0;
            if (dtProduct_presentopp.Rows.Count > 0)
            {
                double.TryParse(dtProduct_presentopp.Rows[0]["qty"].ToString(), out productpresentopp);
                double.TryParse(dtProduct_presentopp.Rows[0]["opvalue"].ToString(), out productpresenvalue);
                double.TryParse(dtProduct_presentopp.Rows[0]["price"].ToString(), out openingprice);
            }
            int    i = 1;
            double openbal = productpresentopp;
            double opevalue = productpresenvalue;
            double openprice = openingprice;
            double opbal = productpresentopp;
            //double openbal = (productpresentopp + totalissueqty + totaltransferquantity) - (totalinwardqty + totalReturnqty);
            // double opevalue = (productpresenvalue + totoutwardvalue + tottransfervalue) - (totinwardvalue + totreturnvalue);
            double oprate = 0; double rate = 0;
            double Prevqty = 0; double Prevalue = 0; double recipttotal = 0; double prevprice = 0; double outreciptprice = 0; double reciptprice = 0; double diffoutvalue = 0; double diffob = 0; double transdiffoutvalue = 0; double transdiffob = 0;
            for (int j = 0; j < NoOfdays; j++)
            {
                double  inwardquantity    = 0;
                double  outwarquantity    = 0;
                double  inwardvalue       = 0;
                double  outwardvalue      = 0;
                double  transfervalue     = 0;
                double  transoutwardvalue = 0;
                double  returnvalue       = 0;
                DataRow newrow            = Report.NewRow();
                newrow["Sno"] = i;
                string   dtcount      = fromdate.AddDays(j).ToString();
                DateTime dtDOE        = Convert.ToDateTime(dtcount);
                string   dtdate1      = dtDOE.AddDays(-1).ToString();
                DateTime dtDOE1       = Convert.ToDateTime(dtdate1).AddDays(1);
                string   ChangedTime1 = dtDOE1.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture);
                DateTime dateinward   = DateTime.ParseExact(ChangedTime1, "MM/dd/yyyy", CultureInfo.InvariantCulture);
                dateinward     = dateinward.Date;
                newrow["Date"] = dtDOE1.ToString("dd-MM-yyyy"); //ChangedTime1;
                DataTable dtin  = new DataTable();
                DataRow[] drrin = dtinward.Select("inwarddate = '" + dateinward + "'");
                if (drrin.Length > 0)
                {
                    dtin = drrin.CopyToDataTable();
                }
                double indateqty = 0; double invalue = 0; double inprice = 0; double tax = 0; double edtax = 0; double recprice = 0;
                foreach (DataRow drinward in dtin.Rows)
                {
                    double.TryParse(drinward["inwardqty"].ToString(), out inwardquantity);
                    double.TryParse(drinward["inwardvalue"].ToString(), out inwardvalue);
                    double.TryParse(drinward["perunit"].ToString(), out inprice);
                    indateqty += inwardquantity;
                    invalue   += inwardvalue;
                    if (inprice > 0)
                    {
                        double.TryParse(drinward["tax"].ToString(), out tax);
                        double.TryParse(drinward["edtax"].ToString(), out edtax);
                        double edvalue  = (inprice * edtax) / 100;
                        double taxvalue = (inprice * tax) / 100;
                        reciptprice = inprice + edvalue + taxvalue;
                        reciptprice = Math.Round(reciptprice, 2);
                        recprice    = reciptprice;
                        inwardvalue = inwardquantity * reciptprice;
                        inwardvalue = Math.Round(inwardvalue, 2);
                    }
                }
                DataTable dtout  = new DataTable();
                DataRow[] drrout = dtoutward.Select("outwarddate ='" + dateinward + "'");
                if (drrout.Length > 0)
                {
                    dtout = drrout.CopyToDataTable();
                }
                double outdateqty = 0; double outvalue = 0; double outprice = 0; double issueqty1 = 0; double issueprice = 0; double issuevalue1 = 0; double diffobvalue = 0;
                foreach (DataRow droutward in dtout.Rows)
                {
                    double.TryParse(droutward["issueqty"].ToString(), out outwarquantity);
                    double.TryParse(droutward["perunit"].ToString(), out outprice);
                    outdateqty  += outwarquantity;
                    recipttotal += outwarquantity;
                    if (recipttotal > opbal)
                    {
                        if (opbal > 0)
                        {
                            diffob = opbal - outwarquantity;
                        }
                        else
                        {
                            diffob = outwarquantity;
                        }
                        diffoutvalue   = outwarquantity - diffob;
                        issueprice     = reciptprice;
                        issueqty1      = diffoutvalue;
                        outreciptprice = issueprice;
                        if (issueqty1 < 0)
                        {
                            diffob     = outwarquantity;
                            issueprice = 0;
                            issueqty1  = 0;
                            outprice   = reciptprice;
                        }
                        recipttotal = 0;
                        if (opbal > 0)
                        {
                            diffobvalue = diffob * outprice;
                        }
                        else
                        {
                            diffobvalue = diffob * reciptprice;
                            outprice    = reciptprice;
                        }
                        outwardvalue = diffob * reciptprice;
                        opbal        = Prevqty;
                    }
                    else
                    {
                        if (outreciptprice > 0)
                        {
                            outprice     = reciptprice;
                            outwardvalue = outwarquantity * outprice;
                        }
                        else
                        {
                            outwardvalue = outwarquantity * outprice;
                        }
                    }
                    outvalue += outwardvalue;
                }
                DataTable drtran   = new DataTable();
                DataRow[] drrtrans = dttransfer.Select("invoicedate ='" + dateinward + "'");
                if (drrtrans.Length > 0)
                {
                    drtran = drrtrans.CopyToDataTable();
                }
                double tranqty = 0; double negative = 0; double outtransvalue = 0; double transoutreciptprice = 0; double transdiffobvalue = 0; double transvalue = 0; double transferprice = 0; double transoutprice = 0; double transissueprice = 0; double transissueqty1 = 0;

                foreach (DataRow drr1tran in drtran.Rows)
                {
                    double.TryParse(drr1tran["transferqty"].ToString(), out tranqty);
                    double.TryParse(drr1tran["transfervalue"].ToString(), out transfervalue);
                    double.TryParse(drr1tran["price"].ToString(), out transferprice);
                    tranqtydateqty += tranqty;
                    transvalue     += transfervalue;
                    if (tranqtydateqty > opbal)
                    {
                        if (opbal > 0)
                        {
                            transdiffob = opbal - tranqty;
                            negative    = transdiffob;
                            if (transdiffob < 0)
                            {
                                transdiffob = tranqty - opbal;
                            }
                        }
                        else
                        {
                            transdiffob = tranqty;
                        }
                        transdiffoutvalue   = tranqty - transdiffob;
                        transissueprice     = reciptprice;
                        transissueqty1      = transdiffoutvalue;
                        transoutreciptprice = transissueprice;
                        if (transissueqty1 < 0)
                        {
                            transdiffob   = tranqty;
                            issueprice    = 0;
                            issueqty1     = 0;
                            transferprice = reciptprice;
                        }
                        recipttotal = 0;
                        if (opbal > 0)
                        {
                            transdiffobvalue = transdiffob * transferprice;
                        }
                        else
                        {
                            transdiffobvalue = transdiffob * reciptprice;
                            transferprice    = reciptprice;
                        }
                        transoutwardvalue = transdiffob * reciptprice;
                        opbal             = Prevqty;
                    }
                    else
                    {
                        if (transoutreciptprice > 0)
                        {
                            transferprice     = reciptprice;
                            transoutwardvalue = tranqty * transferprice;
                        }
                        else
                        {
                            transoutwardvalue = tranqty * transferprice;
                        }
                    }
                    outtransvalue += transoutwardvalue;
                }



                DataTable dttreturn = new DataTable();
                DataRow[] drrreturn = dtreturn.Select("doe ='" + dateinward + "'");
                if (drrreturn.Length > 0)
                {
                    dttreturn = drrreturn.CopyToDataTable();
                }
                double returnqty = 0; double retvalue = 0; double returnprice = 0;
                foreach (DataRow drrreturn1 in dttreturn.Rows)
                {
                    double.TryParse(drrreturn1["returnqty"].ToString(), out returnqty);
                    double.TryParse(drrreturn1["returnvalue"].ToString(), out returnvalue);
                    double.TryParse(drrreturn1["perunit"].ToString(), out returnprice);
                    returndateqty += returnqty;
                    retvalue      += returnvalue;
                }
                if (i == 1)
                {
                    openbal      = openbal;
                    opevalue     = opevalue;
                    openingprice = openingprice;
                }
                else
                {
                    openbal      = Prevqty;
                    opevalue     = Prevalue;
                    openingprice = prevprice;
                }
                double openamt      = 0;
                double openval      = 0;
                double closingqty   = 0;
                double closingvalue = 0;
                double opprice      = 0;
                productpresentopp = 0;
                issuevalue1       = issueqty1 * issueprice;
                if (diffobvalue > 0)
                {
                    outwardvalue = diffobvalue;
                    closingvalue = (opevalue - outwardvalue - transfervalue - issuevalue1) + (inwardvalue + returnvalue);
                }
                else
                {
                    closingvalue = (opevalue - outwardvalue - transfervalue - issuevalue1) + (inwardvalue + returnvalue);
                }
                closingqty              = (openbal - outdateqty - tranqty) + (indateqty + returnqty);
                oprate                  = closingvalue / closingqty;
                newrow["Opp Qty"]       = Math.Round(openbal, 2);
                openbal                 = openamt;
                newrow["Opp Price"]     = Math.Round(openingprice, 2);
                openingprice            = opprice;
                newrow["Opp Value"]     = Math.Round(opevalue, 2);
                opevalue                = openval;
                newrow["ReceiptsQty"]   = Math.Round(indateqty, 2);
                newrow["ReceiptsPrice"] = Math.Round(recprice, 2);
                newrow["ReceiptsValue"] = Math.Round(inwardvalue, 2);
                newrow["ReturnQty"]     = Math.Round(returnqty, 2);
                newrow["ReturnPrice"]   = returnprice;
                newrow["ReturnValue"]   = Math.Round(retvalue, 2);
                if (diffob > 0)
                {
                    newrow["IssuesQty"]   = Math.Round(diffob, 2);
                    newrow["IssuesValue"] = Math.Round(diffobvalue, 2);
                }
                else
                {
                    newrow["IssuesQty"]   = Math.Round(outdateqty, 2);
                    newrow["IssuesValue"] = Math.Round(outvalue, 2);
                }
                newrow["IssuesPrice"]  = outprice;
                newrow["IssuesQty1"]   = issueqty1;
                newrow["IssuesPrice1"] = issueprice;
                double sum = issueqty1 * issueprice;

                newrow["IssuesValue1"] = Math.Round(sum, 2);

                if (transdiffob > 0)
                {
                    newrow["TransferQty"] = Math.Round(transdiffob, 2);
                    double transsum = transdiffob * transferprice;
                    newrow["TransferValue"] = Math.Round(transsum, 2);
                }
                else
                {
                    newrow["TransferQty"] = Math.Round(tranqty, 2);
                    double transsumm = tranqty * transferprice;
                    newrow["TransferValue"] = Math.Round(transsumm, 2);
                }

                newrow["TransferPrice"]  = Math.Round(transferprice, 2);
                newrow["TransferQty1"]   = transissueqty1;
                newrow["Transferprice1"] = transissueprice;
                double transssum = transissueqty1 * transissueprice;
                newrow["Transfervale1"] = Math.Round(transssum, 2);

                //if (negative > 0)
                //{
                //    if (transdiffob > 0)
                //    {
                //        newrow["TransferQty"] = Math.Round(transdiffob, 2);
                //        double transsum = transdiffob * transferprice;
                //        newrow["TransferValue"] = Math.Round(transsum, 2);
                //    }
                //    else
                //    {
                //        newrow["TransferQty"] = Math.Round(tranqty, 2);
                //        double transsumm = tranqty * transferprice;
                //        newrow["TransferValue"] = Math.Round(transsumm, 2);
                //    }

                //    newrow["TransferPrice"] = Math.Round(transferprice, 2);
                //    newrow["TransferQty1"] = transissueqty1;
                //    newrow["Transferprice1"] = transissueprice;
                //    double transssum = transissueqty1 * transissueprice;
                //    newrow["Transfervale1"] = Math.Round(transssum, 2);
                //}
                //else
                //{

                //}



                if (closingvalue < 0)
                {
                    closingqty   = 0;
                    oprate       = 0;
                    closingvalue = 0;
                }
                newrow["Clo Qty"] = Math.Round(closingqty, 2);
                string cval = oprate.ToString();
                if (cval == "NaN")
                {
                    oprate = 0;
                }
                newrow["Clo Price"] = Math.Round(oprate, 2);
                newrow["Clo Value"] = Math.Round(closingvalue, 2);
                Prevqty             = closingqty;
                Prevalue            = closingvalue;
                prevprice           = oprate;
                issueqty1           = 0;
                transissueqty1      = 0;
                transissueprice     = 0;
                issueprice          = 0;
                diffob      = 0;
                diffobvalue = 0;
                transdiffob = 0;
                inwardvalue = 0;
                recprice    = 0;
                Report.Rows.Add(newrow);
                i++;
            }
            DataRow newTotal = Report.NewRow();
            newTotal["Date"] = "Total";
            double val = 0.0;
            foreach (DataColumn dc in Report.Columns)
            {
                if (dc.DataType == typeof(Double))
                {
                    val = 0.0;
                    double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
                    newTotal[dc.ToString()] = val;
                }
            }
            Report.Rows.Add(newTotal);
            grdReports.DataSource = Report;
            grdReports.DataBind();
            Session["xportdata"] = Report;
            Session["filename"]  = "Item Ledger";
            Session["Address"]   = "Punabaka";
            hidepanel.Visible    = true;
            ScriptManager.RegisterStartupScript(Page, GetType(), "JsStatus", "get_product_details();", true);
        }
        catch (Exception ex)
        {
            lblmsg.Text       = ex.Message;
            hidepanel.Visible = false;
        }
    }
示例#27
0
    protected void btn_save_click(object sender, EventArgs e)
    {
        try
        {
            vdm = new SalesDBManager();
            DateTime    CreateDate    = SalesDBManager.GetTime(vdm.conn);
            SAPdbmanger SAPvdm        = new SAPdbmanger();
            DateTime    fromdate      = DateTime.Now;
            DataTable   dt            = (DataTable)Session["xportdata"];
            string[]    dateFromstrig = dtp_FromDate.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);
                }
            }
            fromdate = fromdate;
            foreach (DataRow dr in dt.Rows)
            {
                double amount     = 10;
                string B1Upload   = "N";
                string Processed  = "N";
                string ledgercode = dr["Item Code"].ToString();
                if (ledgercode == "")
                {
                }
                else
                {
                    sqlcmd = new SqlCommand("SELECT CreateDate, PostingDate, DocDate, ReferenceNo AS Processed, Series FROM EMROWTR WHERE (PostingDate BETWEEN @d1 AND @d2) AND (ItemCode=@ItemCode) AND (ReferenceNo = @ReferenceNo)");
                    sqlcmd.Parameters.Add("@d1", GetLowDate(fromdate));
                    sqlcmd.Parameters.Add("@d2", GetHighDate(fromdate));
                    sqlcmd.Parameters.Add("@ReferenceNo", dr["ReferenceNo"].ToString());
                    sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
                    DataTable dtST = SAPvdm.SelectQuery(sqlcmd).Tables[0];
                    if (dtST.Rows.Count > 0)
                    {
                    }
                    else
                    {
                        sqlcmd = new SqlCommand("Insert into EMROWTR (CreateDate,PostingDate,DocDate,ReferenceNo,FromWhsCode,ToWhsCode,ItemCode,ItemName,Quantity,Price,OcrCode,OcrCode2,Remarks,B1Upload,Processed,series) values (@CreateDate,@PostingDate,@DocDate,@ReferenceNo,@FromWhsCode,@ToWhsCode,@ItemCode,@ItemName,@Quantity,@Price,@OcrCode,@OcrCode2,@Remarks,@B1Upload,@Processed,@series)");
                        sqlcmd.Parameters.Add("@CreateDate", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@PostingDate", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@DocDate", GetLowDate(fromdate));
                        sqlcmd.Parameters.Add("@ReferenceNo", dr["ReferenceNo"].ToString());
                        string fromwhcode = "SVDSPP02";
                        sqlcmd.Parameters.Add("@FromWhsCode", fromwhcode);
                        sqlcmd.Parameters.Add("@ToWhsCode", dr["Wh Code"].ToString());
                        sqlcmd.Parameters.Add("@ItemCode", dr["Item Code"].ToString());
                        sqlcmd.Parameters.Add("@ItemName", dr["Item Name"].ToString());
                        sqlcmd.Parameters.Add("@Quantity", dr["Qty"].ToString());
                        sqlcmd.Parameters.Add("@Price", dr["Price"].ToString());
                        sqlcmd.Parameters.Add("@OcrCode", fromwhcode);
                        sqlcmd.Parameters.Add("@OcrCode2", "");
                        sqlcmd.Parameters.Add("@Remarks", dr["Narration"].ToString());
                        sqlcmd.Parameters.Add("@B1Upload", B1Upload);
                        sqlcmd.Parameters.Add("@Processed", Processed);
                        string series = "241";
                        sqlcmd.Parameters.Add("@series", series);
                        SAPvdm.insert(sqlcmd);
                    }
                }
            }

            DataTable dtempty = new DataTable();
            grdReports.DataSource = dtempty;
            grdReports.DataBind();
            lblmsg.Text = "Successfully Saved";
        }
        catch (Exception ex)
        {
            lblmsg.Text = ex.ToString();
        }
    }