using System;
using System.Collections.Generic;
using System.Text;
using DAL;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace BLL
{
    public class SalesOrderHandler
    {

        SalesOrderDBAccess salesOrderDBAccess = null;

        public SalesOrderHandler()
            {
                salesOrderDBAccess = new SalesOrderDBAccess();
             }

        public SqlConnection Con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString); 
        public string StrCon = Convert.ToString(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        public SqlDataReader Sdr; public SqlCommand Cmd;

        protected string ConnectString
        {
            get { return StrCon; }
        }

        /// <summary>
        /// Return Dataset
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        public DataSet ReturnDataSet(string qry)
        {
            DataSet ds = new DataSet();
            OpenConnection();
            if (qry != string.Empty)
            {
                SqlDataAdapter da = new SqlDataAdapter(qry, Con);
                da.Fill(ds);
            }
            CloseConnection();
            return (ds);
        }

        /// <summary>
        /// Open Connection String
        /// </summary>
        public void OpenConnection()
        {
            if (Con.State == ConnectionState.Closed)
            { Con.Open(); }
        }
        /// <summary>
        /// Close Connection String
        /// </summary>
        public void CloseConnection()
        {
            if (Con.State == ConnectionState.Open)
            { Con.Close(); }
        }

        /// <summary>
        /// Insert Query into eCTradeeCTradeDatabase
        /// </summary>
        /// <param name="qry"></param>
        public void ExecuteQuery(string qry)
        {
            OpenConnection();
            SqlCommand cmd = new SqlCommand(qry, Con);
            cmd.ExecuteNonQuery();
            CloseConnection();
        }

        public void SavePO(string title, string docNumber, byte[] docbuffer, string strDocType, string strSoid, string uploadBy)
        {
            Cmd = new SqlCommand("SP_POUploadFile", Con);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.Add("@Title ", SqlDbType.VarChar, 400);
            Cmd.Parameters.Add("@DocNumber", SqlDbType.VarChar, 200);
            Cmd.Parameters.Add("@Doc", SqlDbType.Image);
            Cmd.Parameters.Add("@DocType", SqlDbType.VarChar, 4);
            Cmd.Parameters.Add("@SOID", SqlDbType.VarChar, 50);
            Cmd.Parameters.Add("@UploadBy", SqlDbType.VarChar, 15);

            Cmd.Parameters[0].Value = title;
            Cmd.Parameters[1].Value = docNumber;
            Cmd.Parameters[2].Value = docbuffer;
            Cmd.Parameters[3].Value = strDocType;
            Cmd.Parameters[4].Value = strSoid;
            Cmd.Parameters[5].Value = uploadBy;

            OpenConnection();
            Cmd.ExecuteNonQuery();
            CloseConnection();
        }

        //public DataTable GetRegion()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Region'";
        //    return salesOrderDBAccess.RetrunData(query);

        //}

        //public DataTable GetOffice()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Office'";
        //    return salesOrderDBAccess.RetrunData(query);

        //}

        //public DataTable GetOffice(int regionId)
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Office' and ";
        //    return salesOrderDBAccess.RetrunData(query);

        //}

        //public DataTable GetSalesType()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'SalesType'";
        //    return salesOrderDBAccess.RetrunData(query);

        //}

        //public DataTable GetTransportMode()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'TransportMode'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetCounterpart()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'CounterPart'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetTransporter()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Transporter'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetSamplingAgency()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Sampling Agency'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetTransportResponsibility()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Transport Responsibility'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetSamplingAt()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Sampling At'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetPort()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Port'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetIndustryType()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Industry Type'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetAccountVessel()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Vessel'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetCargo()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Cargo'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetSteveDore()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Stevedore'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}

        //public DataTable GetAgent()
        //{
        //    string query = @"SELECT * FROM PickList where AttributeName= 'Agent'";
        //    return salesOrderDBAccess.RetrunData(query);
        //}


        //public PickList getPickList(string valueId)
        //{
        //    return salesOrderDBAccess.getPickList(valueId);
        //}

        public SODAL GetSalesOrderDetails(int SOID)
        {
            return salesOrderDBAccess.GetSalesOrderDetails(SOID);
        }

        public bool deleteSalesOrder(int accountID)
        {
            return salesOrderDBAccess.deleteSalesOrder(accountID);
        }

        public bool SaveSalesOrder(SODAL salesOrder)
        {
            return salesOrderDBAccess.SaveSalesOrder(salesOrder);
        }

        public bool SaveSalesOrderBlending(List<SODALCargo>  listSODALCargo)
        {
            return salesOrderDBAccess.SaveSalesOrderBlending(listSODALCargo);
        }

        public bool UpdateSalesOrder(SODAL salesOrder)
        {

            return salesOrderDBAccess.UpdateSalesOrder(salesOrder);
        }

        public List<SODAL> GetSalesOrderList()
        {

            return salesOrderDBAccess.GetSalesOrderList();
        }

        public DataTable GetAnalysis()
        {
            string query = @"SELECT * FROM PickList where AttributeName= 'Analysis'";
            return salesOrderDBAccess.RetrunData(query);
        }

        public DataTable GetCommodity(string sONumber)
        {
            string query = @"SELECT CommodityMaster.CommodityID as Sno , CommodityMaster.AnalysisID as AnalysisID, 
                      CommodityMaster.ParameterID as ParameterID, CommodityMaster.BasicID as BasicID, CommodityMaster.UOMID as UOMID, CommodityMaster.Typical as Typical, 
                      CommodityMaster.Max as Maximum, CommodityMaster.Min as Minimum, CommodityMaster.RejMax as RejAbove, CommodityMaster.RejMin as RejBelow, 
                      CommodityMaster.Remark as Remark, AnalysisMaster.Analysis as Analysis, BasicMaster.Basic as Basic, UOMMaster.UOM as UOM, ParameterMaster.Parameter as Parameter
FROM         CommodityMaster INNER JOIN
                      AnalysisMaster ON CommodityMaster.AnalysisID = AnalysisMaster.AnalysisID2011 INNER JOIN
                      ParameterMaster ON CommodityMaster.ParameterID = ParameterMaster.ParameterID2011 INNER JOIN
                      BasicMaster ON CommodityMaster.BasicID = BasicMaster.BasicID2011 INNER JOIN
                      UOMMaster ON CommodityMaster.UOMID = UOMMaster.UOMID2011
                      where CommodityMaster.Delete in (0,2) and CommodityMaster.SalesOrderID = "+sONumber;
            return salesOrderDBAccess.RetrunData(query);
        }
        public DataTable GetParameter()
        {
            string query = @"SELECT * FROM PickList where AttributeName= 'Parameter'";
            return salesOrderDBAccess.RetrunData(query);
        }
        public DataTable GetBasis()
        {
            string query = @"SELECT * FROM PickList where AttributeName= 'Basis'";
            return salesOrderDBAccess.RetrunData(query);
        }
        public DataTable GetUoM()
        {
            string query = @"SELECT * FROM PickList where AttributeName= 'UoM'";
            return salesOrderDBAccess.RetrunData(query);
        }

        public DataTable GetPlot(int vesselId, int portId)
        {
//            string query = @"select top 1 intPlotID, dbo.fn_CargoAvailable(intVesselID,intPortID) as AvailQty, 
//                dbo.getPortName(intPortID) as Port, dbo.GetVesselName(intVesselId) as Vessel from PlotStock where intDelete = 0
//                        and intVesselID = " + vesselId + " and intPortID = " + portId + "";

            string query = @"select * from PlotStock where VesselID=" + vesselId + " and PortID=" + portId;
            return salesOrderDBAccess.RetrunData(query);
        }

        public DataTable editBlend(string SONumber)
        {
            string query = @"SELECT * from  SOBlendingTrans where  SOBlendingTrans.SalesOrderID = '" + SONumber + "'";
            return salesOrderDBAccess.RetrunData(query);
        }

        public List<SODALCargo> GetSalesOrderDetailsCargo(string sONumber)
        {
            return salesOrderDBAccess.GetSalesOrderDetailsCargo(sONumber);
        }

        public bool UpdateSalesOrderDetailsCargo(SODALCargo sODALCargo,int blendingID )
        {

            return salesOrderDBAccess.UpdateSalesOrderDetailsCargo(sODALCargo,blendingID);
        }
    }
}