using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.Areas.TruckMng.Models.MsWl_Port;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using DSWeb.Areas.CommMng.Models;

using System.Web;
using System.Data.Common;
using DSWeb.TruckMng.Comm.Cookie;
using WebSqlHelper;
using DSWeb.Areas.TruckMng.Controllers;


namespace DSWeb.Areas.TruckMng.DAL.MsWl_Port
{
    public partial class MsWl_PortDAL
    {
        #region 主表

        public static List<MsWl_Portmb> GetDataList(string strCondition, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select p.*,case bodytype when 'ctn' then '集装箱' when 'other' then '杂项' when 'ro' then '滚装业务' ");
            strSql.Append(" when 'movctn' then '外部拖箱' when 'GJ' then '国检' end bodytype_ref  ");
            strSql.Append(" ,d.drvname,d.mobile drvmobile,convert(bigint ,p.TimeMark) as P_TimeMark ");
            strSql.Append(" ,case EXPDATE when '1900-1-1' then '' else CONVERT(varchar, EXPDATE, 23 ) end _EXPDATE ");
            strSql.Append(" ,Amount_1,Amount_2 ");
            strSql.Append(" from tmswl_port p left join tmswldriver d on d.drvcode=p.drvcode");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by GID");

            return SetData(strSql);
        }
        public static MsWl_Portmb GetData(string condition)
        {
            MsWl_Portmb head = null;


            var list = GetDataList(condition, "");
            if (list.Count > 0)
                head = list[0];


            if (head == null)
            {
                head = new MsWl_Portmb();
            }

            return head;
        }
        private static List<MsWl_Portmb> SetData(StringBuilder strSql)
        {
            var headList = new List<MsWl_Portmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new MsWl_Portmb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.EXPDATE = Convert.ToString(reader["_EXPDATE"]);
                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
                    data.Amount_1 = Convert.ToString(reader["AMOUNT_1"]);
                    data.Amount_2 = Convert.ToString(reader["AMOUNT_2"]);
                    data.REMARK = Convert.ToString(reader["REMARK"]);
                    data.BODYTYPE = Convert.ToString(reader["BODYTYPE"]);
                    data.BODYTYPE_REF = Convert.ToString(reader["BODYTYPE_REF"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.DRVMOBILE = Convert.ToString(reader["DRVMOBILE"]);
                    data.TimeMark = Convert.ToDecimal(reader["P_TimeMark"]);
                    data.Locked = Convert.ToDecimal(reader["Locked"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }



        #endregion

        #region 明细表
        
        #region 集装箱

        public static List<PortCtnmb> GetCtn(string GID, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select c.*,d.drvname,d.mobile,(select T.WORKNO+'-'+T.TruckNo from tmswltruck T where t.truckno=c.truckno) WORKNOTruckNo ");

            strSql.Append(" from tmswl_port_ctn c left join tmswldriver d on d.drvcode=c.drvcode");
            if (!string.IsNullOrEmpty(GID))
            {
                strSql.Append(" where linkid='" + GID + "'");
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by TruckNo");

            return SetCtn(strSql);
        }

        public static List<PortCtnmb> SetCtn(StringBuilder strSql)
        {
            var headList = new List<PortCtnmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new PortCtnmb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.LINKID = Convert.ToString(reader["LINKID"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.Mobile = Convert.ToString(reader["Mobile"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.Ctn20 = Convert.ToString(reader["Ctn20"]);
                    data.Ctn40 = Convert.ToString(reader["Ctn40"]);
                    data.Ctn45 = Convert.ToString(reader["Ctn45"]);
                    data.Mov = Convert.ToString(reader["Mov"]);
                    data.TEU = Convert.ToString(reader["TEU"]);
                    data.Price_1 = Convert.ToString(reader["Price_1"]);
                    data.Price_2 = Convert.ToString(reader["Price_2"]);
                    data.AvrOil = Convert.ToString(reader["AvrOil"]);
                    data.Factor = Convert.ToString(reader["Factor"]);
                    data.Plus = Convert.ToString(reader["Plus"]);
                    data.Amount_1 = Convert.ToString(reader["Amount_1"]);
                    data.Amount_2 = Convert.ToString(reader["Amount_2"]);
                    data.WORKNOTruckNo = Convert.ToString(reader["WORKNOTruckNo"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 杂项

        public static List<PortOthermb> GetOther(string GID, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select c.*,d.drvname,d.mobile ");

            strSql.Append(" from tmswl_port_OTHER c left join tmswldriver d on d.drvcode=c.drvcode");
            if (!string.IsNullOrEmpty(GID))
            {
                strSql.Append(" where linkid='" + GID + "'");
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by GID");

            return SetOther(strSql);
        }

        public static List<PortOthermb> SetOther(StringBuilder strSql)
        {
            var headList = new List<PortOthermb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new PortOthermb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.LINKID = Convert.ToString(reader["LINKID"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.Mobile = Convert.ToString(reader["Mobile"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.GP = Convert.ToString(reader["GP"]);
                    data.HZ = Convert.ToString(reader["HZ"]);
                    data.BG_TEU = Convert.ToString(reader["BG_TEU"]);
                    data.Mov = Convert.ToString(reader["Mov"]);
                    data.TEU = Convert.ToString(reader["TEU"]);
                    data.AvrOil = Convert.ToString(reader["AvrOil"]);
                    data.Factor = Convert.ToString(reader["Factor"]);
                    data.Plus = Convert.ToString(reader["Plus"]);
                    data.Amount_1 = Convert.ToString(reader["Amount_1"]);
                    data.Amount_2 = Convert.ToString(reader["Amount_2"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 滚装

        public static List<PortROmb> GetRO(string GID, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select c.*,d.drvname,d.mobile ");

            strSql.Append(" from tmswl_port_RO c left join tmswldriver d on d.drvcode=c.drvcode");
            if (!string.IsNullOrEmpty(GID))
            {
                strSql.Append(" where linkid='" + GID + "'");
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by GID");

            return SetRO(strSql);
        }

        public static List<PortROmb> SetRO(StringBuilder strSql)
        {
            var headList = new List<PortROmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new PortROmb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.LINKID = Convert.ToString(reader["LINKID"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.Mobile = Convert.ToString(reader["Mobile"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.Trans = Convert.ToString(reader["Trans"]);
                    data.Dock = Convert.ToString(reader["Dock"]);

                    data.Mov = Convert.ToString(reader["Mov"]);
                    //data.TEU = Convert.ToString(reader["TEU"]);
                    data.AvrOil = Convert.ToString(reader["AvrOil"]);
                    data.Factor = Convert.ToString(reader["Factor"]);
                    data.Plus = Convert.ToString(reader["Plus"]);
                    data.Amount_1 = Convert.ToString(reader["Amount_1"]);
                    data.Amount_2 = Convert.ToString(reader["Amount_2"]);
                    data.Trans_Price = Convert.ToString(reader["Trans_Price"]);
                    data.Dock_Price = Convert.ToString(reader["Dock_Price"]);
                    data.Mov_Price = Convert.ToString(reader["Mov_Price"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 外部拖箱

        public static List<PortMovCtnmb> GetMovCtn(string GID, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select c.*,d.drvname,d.mobile ");

            strSql.Append(" from tmswl_port_MovCtn c left join tmswldriver d on d.drvcode=c.drvcode");
            if (!string.IsNullOrEmpty(GID))
            {
                strSql.Append(" where linkid='" + GID + "'");
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by GID");

            return SetMovCtn(strSql);
        }

        public static List<PortMovCtnmb> SetMovCtn(StringBuilder strSql)
        {
            var headList = new List<PortMovCtnmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new PortMovCtnmb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.LINKID = Convert.ToString(reader["LINKID"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.Mobile = Convert.ToString(reader["Mobile"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.Emp = Convert.ToString(reader["Emp"]);
                    data.Single = Convert.ToString(reader["Single"]);
                    data.Double = Convert.ToString(reader["Double"]);
                    data.BG = Convert.ToString(reader["BG"]);
                    data.Mov = Convert.ToString(reader["Mov"]);
                    //data.TEU = Convert.ToString(reader["TEU"]);
                    data.AvrOil = Convert.ToString(reader["AvrOil"]);
                    data.Factor = Convert.ToString(reader["Factor"]);
                    data.Plus = Convert.ToString(reader["Plus"]);
                    data.Amount_1 = Convert.ToString(reader["Amount_1"]);
                    data.Amount_2 = Convert.ToString(reader["Amount_2"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 国检

        public static List<PortGJmb> GetGJ(string GID, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select GJ.*,d.drvname,d.mobile ");

            strSql.Append(" from tmswl_port_GJ GJ left join tmswldriver d on d.drvcode=GJ.drvcode");
            if (!string.IsNullOrEmpty(GID))
            {
                strSql.Append(" where linkid='" + GID + "'");
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by GID");

            return SetGJ(strSql);
        }

        public static List<PortGJmb> SetGJ(StringBuilder strSql)
        {
            var headList = new List<PortGJmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new PortGJmb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.LINKID = Convert.ToString(reader["LINKID"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.DX = Convert.ToString(reader["DX"]);
                    data.DX_Price_1 = Convert.ToString(reader["DX_Price_1"]);
                    data.DX_Price_2 = Convert.ToString(reader["DX_Price_2"]);
                    data.XZ = Convert.ToString(reader["XZ"]);
                    data.XZ_Price_1 = Convert.ToString(reader["XZ_Price_1"]);
                    data.XZ_Price_2 = Convert.ToString(reader["XZ_Price_2"]);
                    data.PT = Convert.ToString(reader["PT"]);
                    data.PT_Price_1 = Convert.ToString(reader["PT_Price_1"]);
                    data.PT_Price_2 = Convert.ToString(reader["PT_Price_2"]);
                    data.Amount_1 = Convert.ToString(reader["Amount_1"]);
                    data.Amount_2 = Convert.ToString(reader["Amount_2"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.Mobile = Convert.ToString(reader["Mobile"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion
        #endregion

        #region 车辆加油总数(车号/加油量)

        public static List<FuelTruckListmb> GetFuelTruckList(string strCondition, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append("  select truckno,drvcode,fueltype,sum(price*tt.FuelQty) amount,sum(tt.FuelQty) fuelqty, ");
            strSql.Append("  min(dbo.trimdate(FuelDate)) begindate,max(dbo.trimdate(FuelDate)) enddate ");
            strSql.Append("  from ( ");
            strSql.Append("  select TRUCKNO,(select drvname from tmswldriver where drvcode=fd.drvcode) drvcode,FuelType ");
            strSql.Append("  ,(select isnull(price,0) from tMsWl_Port_FuelPrice fp where fp.FuelDate=fd.FuelDate and fp.FuelType=fd.FuelType) price ");
            strSql.Append("  ,fd.FuelQty,fd.FuelDate ");
            strSql.Append("  from tMsWl_Port_FuelDate fd  ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            strSql.Append(" )tt ");
            strSql.Append(" group by truckno,drvcode,FuelType ");
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by TRUCKNO");

            return SetFuelTruckList(strSql);
        }
        public static FuelTruckListmb GetFuelTruck(string condition)
        {
            FuelTruckListmb head = null;


            var list = GetFuelTruckList(condition, "");
            if (list.Count > 0)
                head = list[0];


            if (head == null)
            {
                head = new FuelTruckListmb();
            }

            return head;
        }
        private static List<FuelTruckListmb> SetFuelTruckList(StringBuilder strSql)
        {
            var headList = new List<FuelTruckListmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new FuelTruckListmb();
                    #region Set DB data to Object
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.FuelType = Convert.ToString(reader["FuelType"]);
                    data.FuelQty = Convert.ToString(reader["FuelQty"]);
                    data.begindate = Convert.ToString(reader["begindate"]);
                    data.enddate = Convert.ToString(reader["enddate"]);
                    data.Amount = Convert.ToString(reader["amount"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 车辆加油日志(车号/日期/加油量)

        public static List<FuelDatemb> GetFuelDate(string strCondition, string sort)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select *,(select drvname from tmswldriver where drvcode=tMsWl_Port_FuelDate.drvcode) drvname  ");
            strSql.Append(" from tMsWl_Port_FuelDate ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else strSql.Append(" order by FuelDate,TRUCKNO");

            return SetFuelDate(strSql);
        }
        public static FuelDatemb GetFuelDate(string condition)
        {
            FuelDatemb head = null;


            var list = GetFuelDate(condition, "");
            if (list.Count > 0)
                head = list[0];


            if (head == null)
            {
                head = new FuelDatemb();
            }

            return head;
        }
        private static List<FuelDatemb> SetFuelDate(StringBuilder strSql)
        {
            var headList = new List<FuelDatemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new FuelDatemb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.DRVCODE = Convert.ToString(reader["DRVCODE"]);
                    data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
                    data.FuelType = Convert.ToString(reader["FuelType"]);
                    data.FuelQty = Convert.ToString(reader["FuelQty"]);
                    data.FuelDate = Convert.ToString(reader["FuelDate"]);
                    data.inserted = Convert.ToString(reader["inserted"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 燃料每日价格(日期/价格)

        public static List<FuelPricemb> GetFuelPrice ( string strCondition, string strCondition2, string sort, string COMPANYID, string USERID )
        {
            var strSql = new StringBuilder();
            strSql.Append(" create table #t ([date] date) ");
            strSql.Append(" create table #t1 ([date] date,fueltype varchar(8),companyid varchar(50),companyname varchar(50))  ");
            strSql.Append(" insert into #t  exec GetDays  ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(strCondition);
            }
            strSql.Append(" insert into #t1 select [date] ,'汽油' fueltype,company.GID,company.NAME from #t,company ");
            strSql.Append(" insert into #t1 select [date] ,'柴油' fueltype,company.GID,company.NAME from #t,company ");
            strSql.Append(" select dbo.trimdate(#t1.[date]) Fueldate, isnull(fp.GID,newid()) gid ,#t1.fueltype,fp.price,fp.inserted ");
            strSql.Append(" ,#t1.companyid,#t1.companyname from #t1   left join tMsWl_Port_FuelPrice fp  ");
            strSql.Append(" on fp.fueldate=#t1.[date] and fp.FuelType=#t1.fueltype  and fp.companyid=#t1.companyid ");
            strSql.Append(" where 1=1 ");
            var visible = GetRangDAStr(USERID, COMPANYID);
            if (visible == "1")
            {
                strSql.Append(" and #t1.companyid='" + COMPANYID + "' ");
            }else
                if (visible == "2" || visible=="3"||visible=="4")
            {
                strSql.Append(" and 1=2 ");
            }

            if (!string.IsNullOrEmpty(strCondition2))
            {
                strSql.Append("and"+strCondition2);
            }


            if (!string.IsNullOrEmpty(sort))
            {
                strSql.Append(" order by " + sort);
            }
            strSql.Append(" order by #t1.companyid,#t1.FuelType,date     ");

            strSql.Append(" drop table #t drop table #t1  ");

            return SetFuelPrice(strSql);
        }
        public static FuelPricemb GetFuelPrice(string condition,string COMPANYID,string USERID)
        {
            FuelPricemb head = null;


            var list = GetFuelPrice(condition,"", "", COMPANYID, USERID);
            if (list.Count > 0)
                head = list[0];

            if (head == null)
            {
                head = new FuelPricemb();
            }

            return head;
        }
        private static List<FuelPricemb> SetFuelPrice(StringBuilder strSql)
        {
            var headList = new List<FuelPricemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new FuelPricemb();
                    #region Set DB data to Object
                    data.GID = Convert.ToString(reader["GID"]);
                    data.FuelType = Convert.ToString(reader["FuelType"]);
                    data.Price = Convert.ToString(reader["Price"]);
                    data.FuelDate = Convert.ToString(reader["FuelDate"]);
                    data.inserted = Convert.ToString(reader["inserted"]);
                    data.COMPANYID = Convert.ToString(reader["COMPANYID"]);
                    data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion


        #region 燃油管理权限范围
        public static string GetRangDAStr (string userid,  string companyid )
        {
            string str = "";
            var strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" VISIBLERANGE,OPERATERANGE ");
            strSql.Append("  from VW_User_Authority ");
            strSql.Append("  where [NAME]='modTruckYl' and USERID='" + userid + "' and ISDELETE=0");

            string visiblerange = "4";
            string operaterange = "4";

            Database db = DatabaseFactory.CreateDatabase();


            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {

                while (reader.Read())
                {
                    visiblerange = Convert.ToString(reader["VISIBLERANGE"]);
                    operaterange = Convert.ToString(reader["OPERATERANGE"]);
                    break;
                }
                reader.Close();
            }

            return visiblerange;
        }
        #endregion

        #region 获取某日，某辆车的燃料价格

        public static List<FuelPricemb> GetFuelPrice_Truck(string DATE, string TruckNo)
        {
            var strSql = new StringBuilder();
            strSql.Append(" select F1.* from tMsWl_Port_FuelPrice F1 ");
            strSql.Append(" where F1.FuelType= ");
            strSql.Append(" (select (select enumvaluename from tSysEnumValue where EnumTypeID='99034' and EnumValueID=fueltype) ");
            strSql.Append(" from tMsWlTruck where TruckNo='" + TruckNo + "') ");
            strSql.Append(" and F1.Fueldate=( ");
            strSql.Append(" select max(F2.fueldate) from tMsWl_Port_FuelPrice F2 ");
            strSql.Append(" where F2.fueltype=(select  ");
            strSql.Append(" (select enumvaluename from tSysEnumValue where EnumTypeID='99034' and EnumValueID=fueltype) ");
            strSql.Append(" from tMsWlTruck where TruckNo='" + TruckNo + "') ");
            strSql.Append(" and F2.fueldate<='" + DATE + "') ");

            return SetFuelPrice(strSql);
        }


        #endregion


        #region 倒入码头集装箱业务信息
        public enum RowState : uint
        {
            Insert,
            Update,
            None
        }

        public static bool ImportExcelData ( HttpRequestBase request, DataTable table, out string msg,
    out Int32 InsertCount, out Int32 UpdateCount, ExcelCtnmb _EC, out string UnKnowenTruckNo, out List<PortCtnmb> headList )
        {
            var isSucess = false;
            msg = "";
            headList = new List<PortCtnmb>();
            if (table == null) throw new ArgumentNullException("table");

            var billNoList = new List<string>();
            Database db = DatabaseFactory.CreateDatabase();
            InsertCount = 0;
            UpdateCount = 0;
            UnKnowenTruckNo = "";
            var UnKnowenDrvName = "";
            using (DbConnection connection = db.CreateConnection())
            //using (SqlTransaction idbTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                connection.Open();

                try
                {
                    var orgCode = CookieConfig.GetCookie_OrgCode(request);
                    var userCode = CookieConfig.GetCookie_UserCode(request);
                    var userName = CookieConfig.GetCookie_UserName(request);
                    var _i = -1;
                    //var ExpDate = "";

                    foreach (DataRow row in table.Rows)
                    {

                        _i++;
                        //if (_i < 1) continue;

                        //var _ACCDATE = ACCDATE;

                        var TruckNo = Convert.ToString(row[0]);
                        TruckNo = TruckNo.Replace("T", "");
                        TruckNo = TruckNo.Replace("-", "");
                        TruckNo = TruckNo.Trim();
                        if (TruckNo.Trim() == "")
                        {
                            continue;
                        }
                        var objTruckNo = db.ExecuteScalar(CommandType.Text, "select TruckNo from tMsWlTruck where WORKNO='" + TruckNo + "'");
                        var _TruckNo = Convert.ToString(objTruckNo);
                        if (string.IsNullOrEmpty(_TruckNo))
                        {
                            if (UnKnowenTruckNo == "") { UnKnowenTruckNo = "有如下工作编号的车辆在系统中不存在，请添加后重新导入：" + TruckNo; }
                            else UnKnowenTruckNo = UnKnowenTruckNo + "/" + TruckNo;
                            continue;
                        }
                        /*
                        var DrvName = Convert.ToString(row["驾驶员"]);
                        var DrvCodeObj = db.ExecuteScalar(CommandType.Text, "select DrvCode from tMsWlDriver where DrvName='" + DrvName + "'");
                        var DrvCode = Convert.ToString(DrvCodeObj);
                        if (string.IsNullOrEmpty(DrvCode))
                        {
                            if (UnKnowenDrvName == "") { UnKnowenDrvName = "有如下驾驶员在系统中不存在，请添加后重新导入：" + DrvName; }
                            else UnKnowenDrvName = UnKnowenDrvName + "/" + DrvName;
                            continue;
                        }*/
                        DbTransaction idbTran = connection.BeginTransaction();
                        /*
                         *        0          1        2      3      4   5   6   7   8   9
                        序号	设备编号	Lifts	Carries	Puts	20	40	45	53	MOV	TEU   
                                 TRUCKNO  Ctn20  Ctn40  Ctn45  Mov  TEU  Price_1  Price_2  AvrOil
                         * Factor  Plus  Amount_1  Amount_2  DRVNAME  Mobile
                         * 
                            if (e.field == 'Ctn20' || e.field == 'Ctn40' || e.field == 'Ctn45') {
                                var TEU = 0;
                                var _20 = parseFloat(e.record.get('Ctn20'));
                                var _40 = parseFloat(e.record.get('Ctn40'));
                                var _45 = parseFloat(e.record.get('Ctn45'));
                                var TEU = Add(_20, Mul(_40, 2));
                                TEU = Add(TEU, Mul(_45, 2.25));
                                var AvrOil = parseFloat(e.record.get('AvrOil'));
                                var Amount_1 = Mul(Add(Mul(Cut(AvrOil, priceCut), priceMul), priceAdd), TEU);
                                var Amount_2 = Add(Mul(_20, _20GPMul), Mul(Add(_40, _45), _40GPMul));
                                var Mov = Add(Add(_20, _40), _45);
                                e.record.set('TEU', TEU);
                                e.record.set('Amount_1', Amount_1);
                                e.record.set('Amount_2', Amount_2);
                                e.record.set('Mov', Mov);
                            }
                         */

                        //var TotalMil = Convert.ToString(row[3]);
                        var _p = -1;
                        //_TruckNo //第一列

                        var Ctn20 = Convert.ToString(row[_p+5]);
                        var Ctn40 = Convert.ToString(row[_p + 6]);
                        var Ctn45 = Convert.ToString(row[_p + 7]);
                        var Mov = Convert.ToString(row[_p + 9]);
                        var TEU = Convert.ToString(Convert.ToDecimal(Ctn20) + Convert.ToDecimal(Ctn40) * 2 + Convert.ToDecimal(Ctn45) * 2.25M);
                        FuelPricemb _F=GetFuelPrice_Truck(DateTime.Now.ToString(), _TruckNo)[0];
                        var AvrOil = _F.Price;
                            ///TruckMng/MsWl_Port/GetFuelPrice',
                        var Price_1 = 2M;
                        var Price_2 = 1.2M;
                        
                        var Factor = 0.7M; //系数7
                        var Plus = 11.15M; //尾数11.15

                        /*      var Amount_1 = Mul(Add(Mul(Cut(AvrOil, priceCut), priceMul), priceAdd), TEU);
                                var Amount_2 = Add(Mul(_20, _20GPMul), Mul(Add(_40, _45), _40GPMul));*/
                        var Amount_1 = ((Convert.ToDecimal(AvrOil) - Convert.ToDecimal(_EC.priceCut)) * Convert.ToDecimal(_EC.priceMul) + Convert.ToDecimal(_EC.priceAdd)) * Convert.ToDecimal(TEU);
                        var Amount_2 = Convert.ToDecimal(Ctn20) * Convert.ToDecimal(_EC._20GPMul)
                                        + (Convert.ToDecimal(Ctn40) + Convert.ToDecimal(Ctn45)) * Convert.ToDecimal(_EC._40GPMul);


                        var _RowState = RowState.Insert;
                        string strCheckRow = "select gid from tMsWl_Port_Ctn where LINKID = '" + _EC.LINKID + "' and truckno='"+_TruckNo+"'";
                        object statusObj = SqlHelper.ExecuteScalar(db.ConnectionString, CommandType.Text, strCheckRow, null);

                        if (statusObj == null)
                        {
                            _RowState = RowState.Insert;
                        }
                        else
                        {
                            _RowState = RowState.Update;
                            var gid = statusObj.ToString().Trim();
                            billNoList.Add(gid);
                        }

                        if (_RowState == RowState.Insert)
                        {
                            #region 托单数据生成
                            //var billNo = PubSysDAL.GetBillNo("0119");
                            //billNoList.Add(ContractNo);
                            var gid = "Portctn_" + Guid.NewGuid().ToString("N").ToUpper();
                            billNoList.Add(gid);
                            const string insertSql = @"insert into tMsWl_Port_Ctn (GID,LINKID,TRUCKNO,Ctn20,Ctn40,Ctn45,MOV,TEU,Price_1,Price_2,AvrOil,Factor,Plus,Amount_1,Amount_2)
                                  values(@GID,@LINKID,@TRUCKNO,@Ctn20,@Ctn40,@Ctn45,@Mov,@TEU,@Price_1,@Price_2,@AvrOil,@Factor,@Plus,@Amount_1,@Amount_2)";

                            DbCommand cmd = db.GetSqlStringCommand(insertSql);

                            db.AddInParameter(cmd, "GID", DbType.String, gid);
                            db.AddInParameter(cmd, "TRUCKNO", DbType.String, _TruckNo);
                            db.AddInParameter(cmd, "LINKID", DbType.String, _EC.LINKID);

                            db.AddInParameter(cmd, "Ctn20", DbType.Double, Ctn20);
                            db.AddInParameter(cmd, "Ctn40", DbType.Double, Ctn40);
                            db.AddInParameter(cmd, "Ctn45", DbType.Double, Ctn45);
                            db.AddInParameter(cmd, "Mov", DbType.Double, Mov);
                            db.AddInParameter(cmd, "TEU", DbType.Double, TEU);
                            db.AddInParameter(cmd, "Price_1", DbType.Double, Price_1);
                            db.AddInParameter(cmd, "Price_2", DbType.Double, Price_2);
                            db.AddInParameter(cmd, "AvrOil", DbType.Double, AvrOil);
                            db.AddInParameter(cmd, "Factor", DbType.Double, Factor);
                            db.AddInParameter(cmd, "Plus", DbType.Double, Plus);
                            db.AddInParameter(cmd, "Amount_1", DbType.Double, Amount_1);
                            db.AddInParameter(cmd, "Amount_2", DbType.Double, Amount_2);
                            db.ExecuteNonQuery(cmd, idbTran);
                            idbTran.Commit();
                            InsertCount++;

                            #endregion

                        }
                        else if (_RowState == RowState.Update)
                        {
                            #region 托单数据生成,update
                            //billNoList.Add(TruckNo+_ACCDATE);
                            /*                            const string insertSql = @"insert into tMsWl_Port_Ctn (GID,LINKID,TRUCKNO,Ctn20,Ctn40,Ctn45,MOV,TEU,Price_1,Price_2,AvrOil,Factor,Plus,Amount_1,Amount_2)
                                  values(@GID,@LINKID,@TRUCKNO,@Ctn20,@Ctn40,@Ctn45,@Mov,@TEU,@Price_1,@Price_2,@AvrOil,@Factor,@Plus,@Amount_1,@Amount_2)";*/
                            const string UpdSql = @" Update tMsWl_Port_Ctn set Ctn20=@Ctn20,Ctn40=@Ctn40, Ctn45=@Ctn45,MOV=@Mov,TEU=@TEU
                                ,Price_1=@Price_1,Price_2=@Price_2,AvrOil=@AvrOil,Factor=@Factor,Plus=@Plus,Amount_1=@Amount_1,Amount_2=@Amount_2
                                where TruckNo=@TruckNo and LINKID=@LINKID ";
                            DbCommand cmd = db.GetSqlStringCommand(UpdSql);

                            db.AddInParameter(cmd, "TRUCKNO", DbType.String, _TruckNo);
                            db.AddInParameter(cmd, "LINKID", DbType.String, _EC.LINKID);

                            db.AddInParameter(cmd, "Ctn20", DbType.Double, Ctn20);
                            db.AddInParameter(cmd, "Ctn40", DbType.Double, Ctn40);
                            db.AddInParameter(cmd, "Ctn45", DbType.Double, Ctn45);
                            db.AddInParameter(cmd, "Mov", DbType.Double, Mov);
                            db.AddInParameter(cmd, "TEU", DbType.Double, TEU);
                            db.AddInParameter(cmd, "Price_1", DbType.Double, Price_1);
                            db.AddInParameter(cmd, "Price_2", DbType.Double, Price_2);
                            db.AddInParameter(cmd, "AvrOil", DbType.Double, AvrOil);
                            db.AddInParameter(cmd, "Factor", DbType.Double, Factor);
                            db.AddInParameter(cmd, "Plus", DbType.Double, Plus);
                            db.AddInParameter(cmd, "Amount_1", DbType.Double, Amount_1);
                            db.AddInParameter(cmd, "Amount_2", DbType.Double, Amount_2);

                            db.ExecuteNonQuery(cmd, idbTran);
                            idbTran.Commit();
                            UpdateCount++;
                            //idbTran.Commit();
                            #endregion
                        }
                    }

                    #region 获取计算总应收应付

                    var strSql = new StringBuilder();
                    strSql.Append(" update  tMsWl_Port set AMOUNT_1=(select sum(amount_1) from tMsWl_Port_Ctn where LINKID=tMsWl_Port.GID)");
                    strSql.Append(" ,AMOUNT_2=(select sum(amount_2) from tMsWl_Port_Ctn where LINKID=tMsWl_Port.GID)");
                    strSql.Append(" where GID= '" + _EC.LINKID+ "'");
                    
                    var _count = 0;
                    Database db2 = DatabaseFactory.CreateDatabase();
                    _count = db2.ExecuteNonQuery(CommandType.Text, strSql.ToString());


                    #endregion
                    isSucess = true;
                }
                catch (Exception exception)
                {
                    //idbTran.Rollback();
                    isSucess = false;
                    msg = exception.Message;
                }
            }
            UnKnowenTruckNo = UnKnowenTruckNo + "  " + UnKnowenDrvName;
            return isSucess;
        }
        #endregion

        #region 参照部分



        #endregion
    }
}