private void get_Supcre(string supcust, string fdat, string ldat)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "	select * from v_credtsupwis where PayAcc='" + supcust +
                                  "' and  MPurDate between '" + fdat +
                                  "' and '" + ldat + "' and CompanyId = '" + Session["CompanyID"] +
                                  "' and BranchId= '" + Session["BranchID"] + "'";

                cmd.Connection = con;
                con.Open();

                DataTable      dtvencre = new DataTable();
                SqlDataAdapter adp      = new SqlDataAdapter(cmd);
                adp.Fill(dtvencre);

                if (dtvencre.Rows.Count > 0)
                {
                    lbl_Cust.Text = dtvencre.Rows[0]["suppliername"].ToString();

                    GV_CustCre.DataSource = dtvencre;
                    GV_CustCre.DataBind();

                    lbl_ttl.Text = dtvencre.Rows[0]["OutStanding"].ToString();
                }
                con.Close();
            }
        }
        private void get_custlcre(string crecust, string ldat)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "	select * from v_credtcustwis where CustomerID='" + crecust +
                                  "' and  MPurDate = '" + ldat + "' and CompanyId = '" + Session["CompanyID"] +
                                  "' and BranchId= '" + Session["BranchID"] + "'";

                cmd.Connection = con;
                con.Open();

                DataTable      dtvencre = new DataTable();
                SqlDataAdapter adp      = new SqlDataAdapter(cmd);
                adp.Fill(dtvencre);

                if (dtvencre.Rows.Count > 0)
                {
                    lbl_Cust.Text = dtvencre.Rows[0]["CustomerName"].ToString();

                    GV_CustCre.DataSource = dtvencre;
                    GV_CustCre.DataBind();

                    lbl_ttl.Text = dtvencre.Rows[0]["OutStanding"].ToString();
                }
                con.Close();
            }
        }
        private void get_custcre()
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                /*CreBKR = Request.QueryString["CreBKR"];
                 *  CreSal = Request.QueryString["CreSal"];
                 *  CreCust = Request.QueryString["CreCust"];
                 *  CreArea = Request.QueryString["CreArea"];
                 *  CreBill = Request.QueryString["CreBill"];*/
                if (CreCust == "1")
                {
                    cmd.CommandText = " select ROW_NUMBER() OVER(ORDER BY tbl_MSal.MSal_id DESC) AS [ID],tbl_MSal.CustomerID, CustomerName,Customers_.areaid, area_, Booker,SalMan,MSal_sono, CONVERT(varchar, MSal_dat, 103) as [MPurDate], isnull((OutStanding + 0.01),0.01) as [OutStanding],(GTtl + 0.01) as [GTtl],  Amt,CredAmt, ([Recovery] + 0.01) as [Recovery],  Balance = CASE WHEN LEN((Outstanding - [Recovery])) > 0    THEN '0' ELSE (Outstanding - [Recovery]) END, '' as [ChqNo],    '' as [ChqAmt], tbl_MSal.CreatedAt, tbl_MSal.CreatedBy from tbl_MSal inner join Customers_ on tbl_MSal.CustomerID = Customers_.CustomerID inner join tbl_DSal on tbl_DSal.MSal_id = tbl_MSal.MSal_id inner join tbl_area on Customers_.areaid = tbl_area.areaid inner join tbl_Salcredit on Customers_.CustomerID = tbl_Salcredit.CustomerID  where  outstanding > 0.01 and tbl_MSal.CompanyId = '" + Session["CompanyID"] + "' and tbl_MSal.BranchId= '" + Session["BranchID"] + "'";
                }
                else if (CreCust != null)
                {
                    cmd.CommandText = " select ROW_NUMBER() OVER(ORDER BY tbl_MSal.MSal_id DESC) AS [ID],tbl_MSal.CustomerID, CustomerName,Customers_.areaid, area_, Booker,SalMan,MSal_sono, CONVERT(varchar, MSal_dat, 103) as [MPurDate], isnull((OutStanding + 0.01),0.01) as [OutStanding],(GTtl + 0.01) as [GTtl],  Amt,CredAmt, ([Recovery] + 0.01) as [Recovery],  Balance = CASE WHEN LEN((Outstanding - [Recovery])) > 0    THEN '0' ELSE (Outstanding - [Recovery]) END, '' as [ChqNo],    '' as [ChqAmt], tbl_MSal.CreatedAt, tbl_MSal.CreatedBy from tbl_MSal inner join Customers_ on tbl_MSal.CustomerID = Customers_.CustomerID inner join tbl_DSal on tbl_DSal.MSal_id = tbl_MSal.MSal_id inner join tbl_area on Customers_.areaid = tbl_area.areaid inner join tbl_Salcredit on Customers_.CustomerID = tbl_Salcredit.CustomerID where CustomerID = '" + CreCust + "' and  outstanding > 0.01 and tbl_MSal.CompanyId = '" + Session["CompanyID"] + "' and tbl_MSal.BranchId= '" + Session["BranchID"] + "'";
                }
                else if (CreBKR != null)
                {
                    cmd.CommandText = " select ROW_NUMBER() OVER(ORDER BY tbl_MSal.MSal_id DESC) AS [ID],tbl_MSal.CustomerID, CustomerName,Customers_.areaid, area_, Booker,SalMan,MSal_sono, CONVERT(varchar, MSal_dat, 103) as [MPurDate], isnull((OutStanding + 0.01),0.01) as [OutStanding],(GTtl + 0.01) as [GTtl],  Amt,CredAmt, ([Recovery] + 0.01) as [Recovery],  Balance = CASE WHEN LEN((Outstanding - [Recovery])) > 0    THEN '0' ELSE (Outstanding - [Recovery]) END, '' as [ChqNo],    '' as [ChqAmt], tbl_MSal.CreatedAt, tbl_MSal.CreatedBy from tbl_MSal inner join Customers_ on tbl_MSal.CustomerID = Customers_.CustomerID inner join tbl_DSal on tbl_DSal.MSal_id = tbl_MSal.MSal_id inner join tbl_area on Customers_.areaid = tbl_area.areaid inner join tbl_Salcredit on Customers_.CustomerID = tbl_Salcredit.CustomerID where Booker = '" + CreBKR + "' and  outstanding > 0.01 and tbl_MSal.CompanyId = '" + Session["CompanyID"] + "' and tbl_MSal.BranchId= '" + Session["BranchID"] + "'";
                }
                else if (CreSal != null)
                {
                    cmd.CommandText = " select ROW_NUMBER() OVER(ORDER BY tbl_MSal.MSal_id DESC) AS [ID],tbl_MSal.CustomerID, CustomerName,Customers_.areaid, area_, Booker,SalMan,MSal_sono, CONVERT(varchar, MSal_dat, 103) as [MPurDate], isnull((OutStanding + 0.01),0.01) as [OutStanding],(GTtl + 0.01) as [GTtl],  Amt,CredAmt, ([Recovery] + 0.01) as [Recovery],  Balance = CASE WHEN LEN((Outstanding - [Recovery])) > 0    THEN '0' ELSE (Outstanding - [Recovery]) END, '' as [ChqNo],    '' as [ChqAmt], tbl_MSal.CreatedAt, tbl_MSal.CreatedBy from tbl_MSal inner join Customers_ on tbl_MSal.CustomerID = Customers_.CustomerID inner join tbl_DSal on tbl_DSal.MSal_id = tbl_MSal.MSal_id inner join tbl_area on Customers_.areaid = tbl_area.areaid inner join tbl_Salcredit on Customers_.CustomerID = tbl_Salcredit.CustomerID where SalMan = '" + CreSal + "' and  outstanding > 0.01 and tbl_MSal.CompanyId = '" + Session["CompanyID"] + "' and tbl_MSal.BranchId= '" + Session["BranchID"] + "'";
                }
                else if (CreArea != null)
                {
                    cmd.CommandText = " select ROW_NUMBER() OVER(ORDER BY tbl_MSal.MSal_id DESC) AS [ID],tbl_MSal.CustomerID, CustomerName,Customers_.areaid, area_, Booker,SalMan,MSal_sono, CONVERT(varchar, MSal_dat, 103) as [MPurDate], isnull((OutStanding + 0.01),0.01) as [OutStanding],(GTtl + 0.01) as [GTtl],  Amt,CredAmt, ([Recovery] + 0.01) as [Recovery],  Balance = CASE WHEN LEN((Outstanding - [Recovery])) > 0    THEN '0' ELSE (Outstanding - [Recovery]) END, '' as [ChqNo],    '' as [ChqAmt], tbl_MSal.CreatedAt, tbl_MSal.CreatedBy from tbl_MSal inner join Customers_ on tbl_MSal.CustomerID = Customers_.CustomerID inner join tbl_DSal on tbl_DSal.MSal_id = tbl_MSal.MSal_id inner join tbl_area on Customers_.areaid = tbl_area.areaid inner join tbl_Salcredit on Customers_.CustomerID = tbl_Salcredit.CustomerID where Customers_.areaid = '" + CreArea + "' and  outstanding > 0.01 and tbl_MSal.CompanyId = '" + Session["CompanyID"] + "' and tbl_MSal.BranchId= '" + Session["BranchID"] + "'";
                }
                else if (CreBill != null)
                {
                    cmd.CommandText = " select ROW_NUMBER() OVER(ORDER BY tbl_MSal.MSal_id DESC) AS [ID],tbl_MSal.CustomerID, CustomerName,Customers_.areaid, area_, Booker,SalMan,MSal_sono, CONVERT(varchar, MSal_dat, 103) as [MPurDate], isnull((OutStanding + 0.01),0.01) as [OutStanding],(GTtl + 0.01) as [GTtl],  Amt,CredAmt, ([Recovery] + 0.01) as [Recovery],  Balance = CASE WHEN LEN((Outstanding - [Recovery])) > 0    THEN '0' ELSE (Outstanding - [Recovery]) END, '' as [ChqNo],    '' as [ChqAmt], tbl_MSal.CreatedAt, tbl_MSal.CreatedBy from tbl_MSal inner join Customers_ on tbl_MSal.CustomerID = Customers_.CustomerID inner join tbl_DSal on tbl_DSal.MSal_id = tbl_MSal.MSal_id inner join tbl_area on Customers_.areaid = tbl_area.areaid inner join tbl_Salcredit on Customers_.CustomerID = tbl_Salcredit.CustomerID where MSal_sono = '" + CreBill + "' and  outstanding > 0.01 and tbl_MSal.CompanyId = '" + Session["CompanyID"] + "' and tbl_MSal.BranchId= '" + Session["BranchID"] + "'";
                }
                cmd.Connection = con;
                con.Open();

                DataTable      dtvencre = new DataTable();
                SqlDataAdapter adp      = new SqlDataAdapter(cmd);
                adp.Fill(dtvencre);

                if (dtvencre.Rows.Count > 0)
                {
                    if (CreCust != "1")
                    {
                        lbl_Cust.Text = dtvencre.Rows[0]["CustomerName"].ToString();
                    }
                    else if (CreCust == "1")
                    {
                        lbl_Cust.Text = "All Customers";
                    }
                    GV_CustCre.DataSource = dtvencre;
                    GV_CustCre.DataBind();

                    lbl_ttl.Text = dtvencre.Rows[0]["OutStanding"].ToString();
                }
                con.Close();
            }
        }
예제 #4
0
        private void get_custcre(string fdat, string ldat, string crbkr, string crsal, string crcust, string crarea, string crbil)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "	select * from v_credtcustwis where CreatedAt between '" + fdat + "' and '" + ldat + "'" +
                                  "	and Booker='" + crbkr + "' and SalMan='" + crsal + "' and CustomerID='" + crcust + "' and areaid='" + crarea + "' and " +
                                  "	MSal_sono = '" + crbil + "' and CompanyId = '" + Session["CompanyID"] + "' and BranchId= '" + Session["BranchID"] + "'";

                cmd.Connection = con;
                con.Open();

                DataTable      dtvencre = new DataTable();
                SqlDataAdapter adp      = new SqlDataAdapter(cmd);
                adp.Fill(dtvencre);

                if (dtvencre.Rows.Count > 0)
                {
                    lbl_Cust.Text = dtvencre.Rows[0]["CustomerName"].ToString();

                    GV_CustCre.DataSource = dtvencre;
                    GV_CustCre.DataBind();
                    //Get Total

                    double GTotal  = 0;
                    double QGTotal = 0;

                    // Out Standing
                    for (int j = 0; j < GV_CustCre.Rows.Count; j++)
                    {
                        Label total = (Label)GV_CustCre.Rows[j].FindControl("lbl_outstand");
                        if (total.Text != "")
                        {
                            GTotal += Convert.ToDouble(total.Text);
                        }
                    }

                    //Cash Amount
                    for (int j = 0; j < GV_CustCre.Rows.Count; j++)
                    {
                        Label totalqty = (Label)GV_CustCre.Rows[j].FindControl("lbl_cshamt");
                        if (totalqty.Text != "")
                        {
                            QGTotal += Convert.ToDouble(totalqty.Text);
                        }
                    }
                    ttl_qty.Text = QGTotal.ToString();
                    lbl_ttl.Text = GTotal.ToString();
                }
                con.Close();
            }
        }
예제 #5
0
        private void get_Supcre(string supcust, string fdat, string ldat)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "	select * from v_credtsupwis where PayAcc='" + supcust +
                                  "' and  MPurDate between '" + fdat +
                                  "' and '" + ldat + "' and CompanyId = '" + Session["CompanyID"] +
                                  "' and BranchId= '" + Session["BranchID"] + "'";

                cmd.Connection = con;
                con.Open();

                DataTable      dtvencre = new DataTable();
                SqlDataAdapter adp      = new SqlDataAdapter(cmd);
                adp.Fill(dtvencre);

                if (dtvencre.Rows.Count > 0)
                {
                    lbl_Cust.Text = dtvencre.Rows[0]["suppliername"].ToString();

                    GV_CustCre.DataSource = dtvencre;
                    GV_CustCre.DataBind();

                    //Get Total
                    double QGTotal = 0;


                    //Cash Amount
                    for (int j = 0; j < GV_CustCre.Rows.Count; j++)
                    {
                        Label totalqty = (Label)GV_CustCre.Rows[j].FindControl("lbl_cshamt");
                        if (totalqty.Text != "")
                        {
                            QGTotal += Convert.ToDouble(totalqty.Text);
                        }
                    }
                    ttl_qty.Text = QGTotal.ToString();
                    lbl_ttl.Text = dtvencre.Rows[0]["CredAmt"].ToString();
                }
                con.Close();
            }
        }