You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
915 lines
40 KiB
C#
915 lines
40 KiB
C#
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
|
|
}
|
|
}
|