|
|
using System;
|
|
|
using System.Data;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Text;
|
|
|
using DSWeb.Areas.TruckMng.Models.MsWlBs;
|
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
|
using DSWeb.EntityDA;
|
|
|
using HcUtility.Comm;
|
|
|
using DSWeb.Areas.TruckMng.Controllers;
|
|
|
|
|
|
namespace DSWeb.Areas.TruckMng.DAL.MsWlBs
|
|
|
{
|
|
|
public partial class MsWlPcDAL
|
|
|
{
|
|
|
#region 主表
|
|
|
|
|
|
public static List<MsWlPcHead> GetDataList(string strCondition, string userid, string usercode, string orgcode)
|
|
|
{
|
|
|
|
|
|
var rangstr = GetRangDAStr("index", userid, usercode, orgcode);
|
|
|
|
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
|
{
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
|
{
|
|
|
strCondition = strCondition + " and " + rangstr;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strCondition = rangstr;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT ");
|
|
|
strSql.Append("BillNo,LrDate,JzDate,UserCode,UserName,OrgCode,OrgName");
|
|
|
strSql.Append(",TruckNo,");
|
|
|
strSql.Append("(select TruckNo from tMsWlTruck where TruckNo=tMsWlPcHead.TruckNo) as TruckNo_Ref");
|
|
|
strSql.Append(",DrvCode,");
|
|
|
strSql.Append("(select DrvName from tMsWlDriver where DrvCode=tMsWlPcHead.DrvCode) as DrvCode_Ref");
|
|
|
strSql.Append(",DrvName,Mobile,dbo.trimdate(ExpDate) ExpDate,ContainerType,");
|
|
|
strSql.Append(
|
|
|
"(select CodeAndName from vMsTruckCtn where CtnCode=tMsWlPcHead.ContainerType) as ContainerType_Ref");
|
|
|
strSql.Append(",ContainerQty,VoyVeg,");
|
|
|
strSql.Append("(select CodeAndName from vMsTruckVoyInfo where VoyCode=tMsWlPcHead.VoyVeg) as VoyVeg_Ref");
|
|
|
strSql.Append(",EtDate,EndPortDate,MblNo,YardCode,");
|
|
|
strSql.Append("YardName as YardCode_Ref");
|
|
|
strSql.Append(",YardName,RtnYardCode,");
|
|
|
strSql.Append("RtnYardName as RtnYardCode_Ref");
|
|
|
strSql.Append(",RtnYardName,DstArea,DetiNation,FactoryAddr,LinkTel,LinkMan,RatedMil");
|
|
|
strSql.Append(",RatedFuel,FuelQty,RealMil,RealFuel,NoLoadMil,OverLoadMil,Ton,ActualWeight");
|
|
|
strSql.Append(",AroundTon,LoadCount,ArriveDate,DdCode,");
|
|
|
strSql.Append("(select CodeAndName from vMsTruckUser where UserCode=tMsWlPcHead.DdCode) as DdCode_Ref");
|
|
|
strSql.Append(",DdName,ReturnDate,RefBillNo,Remark,IsDouble");
|
|
|
|
|
|
strSql.Append(",RefBillNoSe,ContainerTypeSe,");
|
|
|
strSql.Append(
|
|
|
"(select CodeAndName from vMsTruckCtn where CtnCode=tMsWlPcHead.ContainerTypeSe) as ContainerTypeSe_Ref");
|
|
|
strSql.Append(",ContainerQtySe,MblNoSe,VoyVegSe,");
|
|
|
strSql.Append(
|
|
|
"(select CodeAndName from vMsTruckVoyInfo where VoyCode=tMsWlPcHead.VoyVegSe) as VoyVegSe_Ref");
|
|
|
strSql.Append(",EtDateSe,EndPortDateSe,YardCodeSe,");
|
|
|
strSql.Append("YardNameSe as YardCodeSe_Ref");
|
|
|
strSql.Append(",YardNameSe,DstAreaSe,FactoryAddrSe,DetiNationSe,LinkManSe,LinkTelSe,RtnYardCodeSe,");
|
|
|
strSql.Append("RtnYardNameSe as RtnYardCodeSe_Ref");
|
|
|
strSql.Append(",RtnYardNameSe,BillStatus,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99026 and EnumValueID=tMsWlPcHead.BillStatus) as BillStatus_Ref");
|
|
|
strSql.Append(",FeeStatus,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99027 and EnumValueID=tMsWlPcHead.FeeStatus) as FeeStatus_Ref");
|
|
|
strSql.Append(",GId,TotalMil, convert(bigint ,TimeMark) as TimeMark,LoadPlace,LoadFuel,NoLoadFuel,BsType,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99025 and EnumValueID=tMsWlPcHead.BsType) as BsType_Ref");
|
|
|
strSql.Append(",ArriveTime,ReturnTime,CustLikeManCode,CustLikeManName,CustTel");
|
|
|
//strSql.Append(",NeadLoadDate,NeedArriveDate");
|
|
|
strSql.Append(",BillRises1,ChangedMil,ChangedFuel,ChangedReson");
|
|
|
|
|
|
strSql.Append(",FeeOpStatus,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99035 and EnumValueID=tMsWlPcHead.FeeOpStatus) as FeeOpStatus_Ref");
|
|
|
strSql.Append(",RunTimes,ContainerNo,SealNo,ContainerNoSe,SealNoSe");
|
|
|
|
|
|
//以下是adl项目新增的字段和计算字段
|
|
|
strSql.Append(" ,CustomerName,F_CustomerName,dbo.trimdate(CutoffTime) CutoffTime,isEnd");
|
|
|
|
|
|
strSql.Append(" ,FuelPrice,isnull(RatedFuel,0)*isnull(FuelPrice,0) FuelAmount ");
|
|
|
strSql.Append(" ,RoadFee,ACCDATE,GoodsName");
|
|
|
|
|
|
strSql.Append(" ,(select CustName from tMsWlBsHead where BillNo=RefBillNo) RefCustName");
|
|
|
strSql.Append(" from tMsWlPcHead ");
|
|
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
|
{
|
|
|
strSql.Append(" where " + strCondition);
|
|
|
}
|
|
|
return SetData(strSql);
|
|
|
}
|
|
|
|
|
|
public static List<MsWlPcHead_ADL> GetDataList_ADL ( string strCondition, string userid, string usercode, string orgcode )
|
|
|
{
|
|
|
|
|
|
var rangstr = GetRangDAStr("index", userid, usercode, orgcode);
|
|
|
|
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
|
{
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
|
{
|
|
|
strCondition = strCondition + " and " + rangstr;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strCondition = rangstr;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT ");
|
|
|
strSql.Append("PINMING ,BillNo,dbo.trimdate(LrDate) LrDate,JzDate,UserCode,UserName,OrgCode,OrgName");
|
|
|
strSql.Append(",TruckNo,");
|
|
|
strSql.Append("(select top 1 TruckNo from tMsWlTruck where TruckNo=tMsWlPcHead.TruckNo) as TruckNo_Ref");
|
|
|
strSql.Append(",DrvCode,");
|
|
|
strSql.Append("(select top 1 DrvName from tMsWlDriver where DrvCode=tMsWlPcHead.DrvCode) as DrvCode_Ref");
|
|
|
strSql.Append(",DrvName,Mobile,dbo.trimdate(ExpDate) ExpDate,ContainerType,");
|
|
|
strSql.Append(
|
|
|
"(select top 1 CtnName from vMsTruckCtn where CtnCode=tMsWlPcHead.ContainerType) as ContainerType_Ref");
|
|
|
strSql.Append(",ContainerQty,VoyVeg,");
|
|
|
strSql.Append("(select top 1 CodeAndName from vMsTruckVoyInfo where VoyCode=tMsWlPcHead.VoyVeg) as VoyVeg_Ref");
|
|
|
strSql.Append(",EtDate,dbo.trimdate(EndPortDate) EndPortDate,MblNo,YardCode,");
|
|
|
strSql.Append(" (select top 1 shortname from info_client where CODENAME=YardCode) YardCode_Ref");
|
|
|
strSql.Append(",YardName,RtnYardCode,");
|
|
|
strSql.Append("RtnYardName as RtnYardCode_Ref");
|
|
|
strSql.Append(",RtnYardName,DstArea,DetiNation,FactoryAddr,LinkTel,LinkMan,RatedMil");
|
|
|
strSql.Append(",RatedFuel,FuelQty,RealMil,RealFuel,NoLoadMil,OverLoadMil,Ton,ActualWeight");
|
|
|
strSql.Append(",AroundTon,LoadCount,ArriveDate,DdCode,");
|
|
|
strSql.Append("(select top 1 CodeAndName from vMsTruckUser where UserCode=tMsWlPcHead.DdCode) as DdCode_Ref");
|
|
|
strSql.Append(",DdName,ReturnDate,RefBillNo,Remark,IsDouble,RefBillNoSe,ContainerTypeSe,");
|
|
|
strSql.Append(
|
|
|
"(select CodeAndName from vMsTruckCtn where CtnCode=tMsWlPcHead.ContainerTypeSe) as ContainerTypeSe_Ref");
|
|
|
strSql.Append(",ContainerQtySe,MblNoSe,VoyVegSe,");
|
|
|
strSql.Append(
|
|
|
"(select CodeAndName from vMsTruckVoyInfo where VoyCode=tMsWlPcHead.VoyVegSe) as VoyVegSe_Ref");
|
|
|
strSql.Append(",EtDateSe,EndPortDateSe,YardCodeSe,");
|
|
|
strSql.Append("YardNameSe as YardCodeSe_Ref");
|
|
|
strSql.Append(",YardNameSe,DstAreaSe,FactoryAddrSe,DetiNationSe,LinkManSe,LinkTelSe,RtnYardCodeSe,");
|
|
|
strSql.Append("RtnYardNameSe as RtnYardCodeSe_Ref");
|
|
|
strSql.Append(",RtnYardNameSe,BillStatus,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99026 and EnumValueID=tMsWlPcHead.BillStatus) as BillStatus_Ref");
|
|
|
strSql.Append(",FeeStatus,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99027 and EnumValueID=tMsWlPcHead.FeeStatus) as FeeStatus_Ref");
|
|
|
strSql.Append(",GId,TotalMil, convert(bigint ,TimeMark) as TimeMark,LoadPlace,LoadFuel,NoLoadFuel,BsType,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99025 and EnumValueID=tMsWlPcHead.BsType) as BsType_Ref");
|
|
|
strSql.Append(",ArriveTime,ReturnTime,CustLikeManCode,CustLikeManName,CustTel");
|
|
|
strSql.Append(",BillRises1,ChangedMil,ChangedFuel,ChangedReson");
|
|
|
|
|
|
strSql.Append(",FeeOpStatus,");
|
|
|
strSql.Append(
|
|
|
"(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99035 and EnumValueID=tMsWlPcHead.FeeOpStatus) as FeeOpStatus_Ref");
|
|
|
strSql.Append(",RunTimes,ContainerNo,SealNo,ContainerNoSe,SealNoSe");
|
|
|
|
|
|
//以下是adl项目新增的字段和计算字段
|
|
|
strSql.Append(" ,CustomerName,(select top 1 description from info_client where shortname=tMsWlPcHead.CustomerName) CustomerNameFull ");
|
|
|
strSql.Append(" ,F_CustomerName,dbo.trimdate(CutoffTime) CutoffTime,isEnd" );
|
|
|
|
|
|
strSql.Append(" ,FuelPrice,isnull(RatedFuel,0)*isnull(FuelPrice,0) FuelAmount ");
|
|
|
strSql.Append(" ,RoadFee,case when TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF,ACCDATE ");
|
|
|
strSql.Append(" ,DGTYPE,DGTYPECODE,SECOND_DRV,dbo.trimdate(ArriveDate) ArriveDate,ArriveTime ");
|
|
|
|
|
|
strSql.Append(",dbo.F_GetBillDrFeeStatus(tMsWlPcHead.GId) DRFEESTATUS");
|
|
|
strSql.Append(",dbo.F_GetBillCrFeeStatus(tMsWlPcHead.GId) CRFEESTATUS");
|
|
|
strSql.Append(",GOODSCOUNT,CBM,ISHW,TruckSpec,GoodsName");
|
|
|
strSql.Append(" ,(select CustName from tMsWlBsHead where BillNo=RefBillNo) RefCustName");
|
|
|
strSql.Append(" from tMsWlPcHead ");
|
|
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
|
{
|
|
|
strSql.Append(" where " + strCondition);
|
|
|
}
|
|
|
strSql.Append(" order by isnull(ExpDate,getdate()) desc ");
|
|
|
|
|
|
return SetData_ADL(strSql);
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
private static List<MsWlPcHead> SetData(StringBuilder strSql)
|
|
|
{
|
|
|
var headList = new List<MsWlPcHead>();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
MsWlPcHead data = new MsWlPcHead();
|
|
|
#region Set DB data to Object
|
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
|
if (reader["LrDate"] != DBNull.Value)
|
|
|
data.LrDate = Convert.ToDateTime(reader["LrDate"]);
|
|
|
if (reader["JzDate"] != DBNull.Value)
|
|
|
data.JzDate = Convert.ToDateTime(reader["JzDate"]);
|
|
|
data.UserCode = Convert.ToString(reader["UserCode"]);
|
|
|
data.UserName = Convert.ToString(reader["UserName"]);
|
|
|
data.OrgCode = Convert.ToString(reader["OrgCode"]);
|
|
|
data.OrgName = Convert.ToString(reader["OrgName"]);
|
|
|
data.TruckNo = Convert.ToString(reader["TruckNo"]);
|
|
|
data.TruckNo_Ref = Convert.ToString(reader["TruckNo_Ref"]);
|
|
|
data.DrvCode = Convert.ToString(reader["DrvCode"]);
|
|
|
data.DrvName = Convert.ToString(reader["DrvName"]);
|
|
|
data.DrvCode_Ref = Convert.ToString(reader["DrvCode_Ref"]);
|
|
|
data.Mobile = Convert.ToString(reader["Mobile"]);
|
|
|
data.ExpDate = Convert.ToString(reader["ExpDate"]);
|
|
|
data.ContainerType = Convert.ToString(reader["ContainerType"]);
|
|
|
data.ContainerType_Ref = Convert.ToString(reader["ContainerType_Ref"]);
|
|
|
data.ContainerQty = Convert.ToDecimal(reader["ContainerQty"]);
|
|
|
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
|
|
|
data.VoyVeg_Ref = Convert.ToString(reader["VoyVeg_Ref"]);
|
|
|
data.EtDate = Convert.ToString(reader["EtDate"]);
|
|
|
data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
|
|
|
data.MblNo = Convert.ToString(reader["MblNo"]);
|
|
|
data.YardCode = Convert.ToString(reader["YardCode"]);
|
|
|
data.YardCode_Ref = Convert.ToString(reader["YardCode_Ref"]);
|
|
|
data.YardName = Convert.ToString(reader["YardName"]);
|
|
|
data.RtnYardCode = Convert.ToString(reader["RtnYardCode"]);
|
|
|
data.RtnYardCode_Ref = Convert.ToString(reader["RtnYardCode_Ref"]);
|
|
|
data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
|
|
|
data.DstArea = Convert.ToString(reader["DstArea"]);
|
|
|
data.DetiNation = Convert.ToString(reader["DetiNation"]);
|
|
|
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
|
|
|
data.LinkTel = Convert.ToString(reader["LinkTel"]);
|
|
|
data.LinkMan = Convert.ToString(reader["LinkMan"]);
|
|
|
data.RatedMil = Convert.ToDecimal(reader["RatedMil"]);
|
|
|
data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
|
|
|
data.FuelQty = Convert.ToDecimal(reader["FuelQty"]);
|
|
|
data.RealMil = Convert.ToDecimal(reader["RealMil"]);
|
|
|
data.RealFuel = Convert.ToDecimal(reader["RealFuel"]);
|
|
|
data.NoLoadMil = Convert.ToDecimal(reader["NoLoadMil"]);
|
|
|
data.OverLoadMil = Convert.ToDecimal(reader["OverLoadMil"]);
|
|
|
data.Ton = Convert.ToDecimal(reader["Ton"]);
|
|
|
data.ActualWeight = Convert.ToDecimal(reader["ActualWeight"]);
|
|
|
data.AroundTon = Convert.ToDecimal(reader["AroundTon"]);
|
|
|
data.LoadCount = Convert.ToDecimal(reader["LoadCount"]);
|
|
|
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
|
|
|
data.DdCode = Convert.ToString(reader["DdCode"]);
|
|
|
data.DdCode_Ref = Convert.ToString(reader["DdCode_Ref"]);
|
|
|
data.DdName = Convert.ToString(reader["DdName"]);
|
|
|
data.ReturnDate = Convert.ToString(reader["ReturnDate"]);
|
|
|
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
|
|
|
data.Remark = Convert.ToString(reader["Remark"]);
|
|
|
data.IsDouble = Convert.ToString(reader["IsDouble"]);
|
|
|
data.RefBillNoSe = Convert.ToString(reader["RefBillNoSe"]);
|
|
|
data.ContainerTypeSe = Convert.ToString(reader["ContainerTypeSe"]);
|
|
|
data.ContainerTypeSe_Ref = Convert.ToString(reader["ContainerTypeSe_Ref"]);
|
|
|
data.ContainerQtySe = Convert.ToDecimal(reader["ContainerQtySe"]);
|
|
|
data.MblNoSe = Convert.ToString(reader["MblNoSe"]);
|
|
|
data.VoyVegSe = Convert.ToString(reader["VoyVegSe"]);
|
|
|
data.VoyVegSe_Ref = Convert.ToString(reader["VoyVegSe_Ref"]);
|
|
|
data.EtDateSe = Convert.ToString(reader["EtDateSe"]);
|
|
|
data.EndPortDateSe = Convert.ToString(reader["EndPortDateSe"]);
|
|
|
data.YardCodeSe = Convert.ToString(reader["YardCodeSe"]);
|
|
|
data.YardCodeSe_Ref = Convert.ToString(reader["YardCodeSe_Ref"]);
|
|
|
data.YardNameSe = Convert.ToString(reader["YardNameSe"]);
|
|
|
data.DstAreaSe = Convert.ToString(reader["DstAreaSe"]);
|
|
|
data.FactoryAddrSe = Convert.ToString(reader["FactoryAddrSe"]);
|
|
|
data.DetiNationSe = Convert.ToString(reader["DetiNationSe"]);
|
|
|
data.LinkManSe = Convert.ToString(reader["LinkManSe"]);
|
|
|
data.LinkTelSe = Convert.ToString(reader["LinkTelSe"]);
|
|
|
data.RtnYardCodeSe = Convert.ToString(reader["RtnYardCodeSe"]);
|
|
|
data.RtnYardCodeSe_Ref = Convert.ToString(reader["RtnYardCodeSe_Ref"]);
|
|
|
data.RtnYardNameSe = Convert.ToString(reader["RtnYardNameSe"]);
|
|
|
data.BillStatus = Convert.ToString(reader["BillStatus"]);
|
|
|
data.BillStatus_Ref = Convert.ToString(reader["BillStatus_Ref"]);
|
|
|
data.FeeStatus = Convert.ToString(reader["FeeStatus"]);
|
|
|
data.FEESTATUS = Convert.ToString(reader["FeeStatus"]);
|
|
|
data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]);
|
|
|
data.GId = Convert.ToString(reader["GId"]);
|
|
|
data.TotalMil = Convert.ToDecimal(reader["TotalMil"]);
|
|
|
data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
|
|
|
|
|
|
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
|
|
|
data.LoadFuel = Convert.ToDecimal(reader["LoadFuel"]);
|
|
|
data.NoLoadFuel = Convert.ToDecimal(reader["NoLoadFuel"]);
|
|
|
|
|
|
data.BsType = Convert.ToString(reader["BsType"]);
|
|
|
data.BsType_Ref = Convert.ToString(reader["BsType_Ref"]);
|
|
|
|
|
|
//data.NeadLoadDate = Convert.ToString(reader["NeadLoadDate"]);
|
|
|
//data.NeedArriveDate = Convert.ToString(reader["NeedArriveDate"]);
|
|
|
data.ArriveTime = Convert.ToString(reader["ArriveTime"]);
|
|
|
data.ReturnTime = Convert.ToString(reader["ReturnTime"]);
|
|
|
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
|
|
|
data.CustLikeManCode = Convert.ToString(reader["CustLikeManCode"]);
|
|
|
data.CustLikeManName = Convert.ToString(reader["CustLikeManName"]);
|
|
|
data.CustTel = Convert.ToString(reader["CustTel"]);
|
|
|
|
|
|
data.BillRises1 = Convert.ToString(reader["BillRises1"]);
|
|
|
data.ChangedMil = Convert.ToDecimal(reader["ChangedMil"]);
|
|
|
data.ChangedFuel = Convert.ToDecimal(reader["ChangedFuel"]);
|
|
|
data.ChangedReson = Convert.ToString(reader["ChangedReson"]);
|
|
|
|
|
|
data.FeeOpStatus = Convert.ToString(reader["FeeOpStatus"]);
|
|
|
data.FeeOpStatus_Ref = Convert.ToString(reader["FeeOpStatus_Ref"]);
|
|
|
data.RunTimes = Convert.ToDecimal(reader["RunTimes"]);
|
|
|
|
|
|
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
|
|
|
data.SealNo = Convert.ToString(reader["SealNo"]);
|
|
|
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
|
|
|
data.SealNoSe = Convert.ToString(reader["SealNoSe"]);
|
|
|
|
|
|
data.CustomerName = Convert.ToString(reader["CustomerName"]);
|
|
|
//data.CustomerNameFull = Convert.ToString(reader["CustomerNameFull"]);
|
|
|
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
|
|
|
data.CutoffTime = Convert.ToString(reader["CutoffTime"]);
|
|
|
data.isEnd = Convert.ToString(reader["isEnd"]);
|
|
|
|
|
|
data.FuelPrice = Convert.ToString(reader["FuelPrice"]);
|
|
|
data.FuelAmount = Convert.ToString(reader["FuelAmount"]);
|
|
|
data.RoadFee = Convert.ToString(reader["RoadFee"]);
|
|
|
|
|
|
data.RefCustName = Convert.ToString(reader["RefCustName"]);
|
|
|
#endregion
|
|
|
|
|
|
headList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return headList;
|
|
|
}
|
|
|
|
|
|
private static List<MsWlPcHead_ADL> SetData_ADL ( StringBuilder strSql )
|
|
|
{
|
|
|
var headList = new List<MsWlPcHead_ADL>();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
MsWlPcHead_ADL data = new MsWlPcHead_ADL();
|
|
|
#region Set DB data to Object
|
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
|
if (reader["LrDate"] != DBNull.Value)
|
|
|
data.LrDate = Convert.ToDateTime(reader["LrDate"]);
|
|
|
if (reader["JzDate"] != DBNull.Value)
|
|
|
data.JzDate = Convert.ToDateTime(reader["JzDate"]);
|
|
|
data.UserCode = Convert.ToString(reader["UserCode"]);
|
|
|
data.UserName = Convert.ToString(reader["UserName"]);
|
|
|
data.OrgCode = Convert.ToString(reader["OrgCode"]);
|
|
|
data.OrgName = Convert.ToString(reader["OrgName"]);
|
|
|
data.TruckNo = Convert.ToString(reader["TruckNo"]);
|
|
|
data.TruckSpec = Convert.ToString(reader["TruckSpec"]);
|
|
|
data.TruckNo_Ref = Convert.ToString(reader["TruckNo_Ref"]);
|
|
|
data.DrvCode = Convert.ToString(reader["DrvCode"]);
|
|
|
data.DrvName = Convert.ToString(reader["DrvName"]);
|
|
|
data.DrvCode_Ref = Convert.ToString(reader["DrvCode_Ref"]);
|
|
|
data.Mobile = Convert.ToString(reader["Mobile"]);
|
|
|
data.ExpDate = Convert.ToString(reader["ExpDate"]);
|
|
|
data.ContainerType = Convert.ToString(reader["ContainerType"]);
|
|
|
data.ContainerType_Ref = Convert.ToString(reader["ContainerType_Ref"]);
|
|
|
data.ContainerQty = Convert.ToDecimal(reader["ContainerQty"]);
|
|
|
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
|
|
|
data.VoyVeg_Ref = Convert.ToString(reader["VoyVeg_Ref"]);
|
|
|
data.EtDate = Convert.ToString(reader["EtDate"]);
|
|
|
data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
|
|
|
data.MblNo = Convert.ToString(reader["MblNo"]);
|
|
|
data.YardCode = Convert.ToString(reader["YardCode"]);
|
|
|
data.YardCode_Ref = Convert.ToString(reader["YardCode_Ref"]);
|
|
|
data.YardName = Convert.ToString(reader["YardName"]);
|
|
|
data.RtnYardCode = Convert.ToString(reader["RtnYardCode"]);
|
|
|
data.RtnYardCode_Ref = Convert.ToString(reader["RtnYardCode_Ref"]);
|
|
|
data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
|
|
|
data.DstArea = Convert.ToString(reader["DstArea"]);
|
|
|
data.DetiNation = Convert.ToString(reader["DetiNation"]);
|
|
|
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
|
|
|
data.LinkTel = Convert.ToString(reader["LinkTel"]);
|
|
|
data.LinkMan = Convert.ToString(reader["LinkMan"]);
|
|
|
data.RatedMil = Convert.ToDecimal(reader["RatedMil"]);
|
|
|
data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
|
|
|
data.FuelQty = Convert.ToDecimal(reader["FuelQty"]);
|
|
|
data.RealMil = Convert.ToDecimal(reader["RealMil"]);
|
|
|
data.RealFuel = Convert.ToDecimal(reader["RealFuel"]);
|
|
|
data.NoLoadMil = Convert.ToDecimal(reader["NoLoadMil"]);
|
|
|
data.OverLoadMil = Convert.ToDecimal(reader["OverLoadMil"]);
|
|
|
data.Ton = Convert.ToDecimal(reader["Ton"]);
|
|
|
data.ActualWeight = Convert.ToDecimal(reader["ActualWeight"]);
|
|
|
data.AroundTon = Convert.ToDecimal(reader["AroundTon"]);
|
|
|
data.LoadCount = Convert.ToDecimal(reader["LoadCount"]);
|
|
|
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
|
|
|
data.DdCode = Convert.ToString(reader["DdCode"]);
|
|
|
data.DdCode_Ref = Convert.ToString(reader["DdCode_Ref"]);
|
|
|
data.DdName = Convert.ToString(reader["DdName"]);
|
|
|
data.ReturnDate = Convert.ToString(reader["ReturnDate"]);
|
|
|
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
|
|
|
data.Remark = Convert.ToString(reader["Remark"]);
|
|
|
data.IsDouble = Convert.ToString(reader["IsDouble"]);
|
|
|
data.RefBillNoSe = Convert.ToString(reader["RefBillNoSe"]);
|
|
|
data.ContainerTypeSe = Convert.ToString(reader["ContainerTypeSe"]);
|
|
|
data.ContainerTypeSe_Ref = Convert.ToString(reader["ContainerTypeSe_Ref"]);
|
|
|
data.ContainerQtySe = Convert.ToDecimal(reader["ContainerQtySe"]);
|
|
|
data.MblNoSe = Convert.ToString(reader["MblNoSe"]);
|
|
|
data.VoyVegSe = Convert.ToString(reader["VoyVegSe"]);
|
|
|
data.VoyVegSe_Ref = Convert.ToString(reader["VoyVegSe_Ref"]);
|
|
|
data.EtDateSe = Convert.ToString(reader["EtDateSe"]);
|
|
|
data.EndPortDateSe = Convert.ToString(reader["EndPortDateSe"]);
|
|
|
data.YardCodeSe = Convert.ToString(reader["YardCodeSe"]);
|
|
|
data.YardCodeSe_Ref = Convert.ToString(reader["YardCodeSe_Ref"]);
|
|
|
data.YardNameSe = Convert.ToString(reader["YardNameSe"]);
|
|
|
data.DstAreaSe = Convert.ToString(reader["DstAreaSe"]);
|
|
|
data.FactoryAddrSe = Convert.ToString(reader["FactoryAddrSe"]);
|
|
|
data.DetiNationSe = Convert.ToString(reader["DetiNationSe"]);
|
|
|
data.LinkManSe = Convert.ToString(reader["LinkManSe"]);
|
|
|
data.LinkTelSe = Convert.ToString(reader["LinkTelSe"]);
|
|
|
data.RtnYardCodeSe = Convert.ToString(reader["RtnYardCodeSe"]);
|
|
|
data.RtnYardCodeSe_Ref = Convert.ToString(reader["RtnYardCodeSe_Ref"]);
|
|
|
data.RtnYardNameSe = Convert.ToString(reader["RtnYardNameSe"]);
|
|
|
data.BillStatus = Convert.ToString(reader["BillStatus"]);
|
|
|
data.BillStatus_Ref = Convert.ToString(reader["BillStatus_Ref"]);
|
|
|
data.FeeStatus = Convert.ToString(reader["FeeStatus"]);
|
|
|
data.FEESTATUS = Convert.ToString(reader["FeeStatus"]);
|
|
|
data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]);
|
|
|
data.GId = Convert.ToString(reader["GId"]);
|
|
|
data.TotalMil = Convert.ToDecimal(reader["TotalMil"]);
|
|
|
data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
|
|
|
|
|
|
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
|
|
|
data.LoadFuel = Convert.ToDecimal(reader["LoadFuel"]);
|
|
|
data.NoLoadFuel = Convert.ToDecimal(reader["NoLoadFuel"]);
|
|
|
|
|
|
data.BsType = Convert.ToString(reader["BsType"]);
|
|
|
data.BsType_Ref = Convert.ToString(reader["BsType_Ref"]);
|
|
|
|
|
|
//data.NeadLoadDate = Convert.ToString(reader["NeadLoadDate"]);
|
|
|
//data.NeedArriveDate = Convert.ToString(reader["NeedArriveDate"]);
|
|
|
data.ArriveTime = Convert.ToString(reader["ArriveTime"]);
|
|
|
data.ReturnTime = Convert.ToString(reader["ReturnTime"]);
|
|
|
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
|
|
|
|
|
|
data.CustLikeManCode = Convert.ToString(reader["CustLikeManCode"]);
|
|
|
data.CustLikeManName = Convert.ToString(reader["CustLikeManName"]);
|
|
|
data.CustTel = Convert.ToString(reader["CustTel"]);
|
|
|
|
|
|
data.BillRises1 = Convert.ToString(reader["BillRises1"]);
|
|
|
data.ChangedMil = Convert.ToDecimal(reader["ChangedMil"]);
|
|
|
data.ChangedFuel = Convert.ToDecimal(reader["ChangedFuel"]);
|
|
|
data.ChangedReson = Convert.ToString(reader["ChangedReson"]);
|
|
|
|
|
|
data.FeeOpStatus = Convert.ToString(reader["FeeOpStatus"]);
|
|
|
data.FeeOpStatus_Ref = Convert.ToString(reader["FeeOpStatus_Ref"]);
|
|
|
data.RunTimes = Convert.ToDecimal(reader["RunTimes"]);
|
|
|
|
|
|
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
|
|
|
data.SealNo = Convert.ToString(reader["SealNo"]);
|
|
|
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
|
|
|
data.SealNoSe = Convert.ToString(reader["SealNoSe"]);
|
|
|
data.GoodsName = Convert.ToString(reader["GoodsName"]);
|
|
|
|
|
|
data.CustomerName = Convert.ToString(reader["CustomerName"]);
|
|
|
data.CustomerNameFull = Convert.ToString(reader["CustomerNameFull"]);
|
|
|
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
|
|
|
data.CutoffTime = Convert.ToString(reader["CutoffTime"]);
|
|
|
data.isEnd = Convert.ToString(reader["isEnd"]);
|
|
|
|
|
|
data.FuelPrice = Convert.ToString(reader["FuelPrice"]);
|
|
|
data.FuelAmount = Convert.ToString(reader["FuelAmount"]);
|
|
|
data.RoadFee = Convert.ToString(reader["RoadFee"]);
|
|
|
data.PCSTATUSREF = Convert.ToString(reader["PCSTATUSREF"]);
|
|
|
|
|
|
data.DGTYPE = Convert.ToString(reader["DGTYPE"]);
|
|
|
data.DGTYPECODE = Convert.ToString(reader["DGTYPECODE"]);
|
|
|
data.SECOND_DRV = Convert.ToString(reader["SECOND_DRV"]);
|
|
|
|
|
|
data.DRFEESTATUS = getfeestatus(Convert.ToString(reader["DRFEESTATUS"]));
|
|
|
data.CRFEESTATUS = getfeestatus(Convert.ToString(reader["CRFEESTATUS"]));
|
|
|
|
|
|
data.GOODSCOUNT = Convert.ToString(reader["GOODSCOUNT"]);
|
|
|
data.CBM = Convert.ToString(reader["CBM"]);
|
|
|
data.ISHW = Convert.ToDecimal(reader["ISHW"]);
|
|
|
|
|
|
data.PINMING = Convert.ToString(reader["PINMING"]);
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
headList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return headList;
|
|
|
}
|
|
|
|
|
|
public static string getfeestatus ( string feestatusint )
|
|
|
{
|
|
|
var result = "";
|
|
|
if (feestatusint == "0:")
|
|
|
{
|
|
|
return "审核通过";
|
|
|
}
|
|
|
else if (feestatusint == "1:")
|
|
|
{
|
|
|
return "录入状态";
|
|
|
}
|
|
|
else if (feestatusint == "2:")
|
|
|
{
|
|
|
return "提交审核";
|
|
|
}
|
|
|
else if (feestatusint == "8:")
|
|
|
{
|
|
|
return "部分结算";
|
|
|
}
|
|
|
else if (feestatusint == "9:")
|
|
|
{
|
|
|
return "结算完毕";
|
|
|
}
|
|
|
else if (feestatusint == "")
|
|
|
{
|
|
|
return "未录入";
|
|
|
}
|
|
|
else if (feestatusint == "3:")
|
|
|
{
|
|
|
return "申请修改";
|
|
|
}
|
|
|
else if (feestatusint == "4:")
|
|
|
{
|
|
|
return "申请删除";
|
|
|
}
|
|
|
else if (feestatusint == "6:")
|
|
|
{
|
|
|
return "驳回提交";
|
|
|
}
|
|
|
else if (feestatusint == "7:")
|
|
|
{
|
|
|
return "驳回申请";
|
|
|
}
|
|
|
else if (feestatusint.IndexOf("9:") > -1)
|
|
|
{
|
|
|
return "部分结算";
|
|
|
}
|
|
|
else if (feestatusint.IndexOf("0:") > -1)
|
|
|
{
|
|
|
return "部分审核";
|
|
|
}
|
|
|
else if (feestatusint.IndexOf("2:") > -1)
|
|
|
{
|
|
|
return "部分提交";
|
|
|
}
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
|
|
|
|
|
|
static public MsWlPcHead GetHeadDataByBillNo(string billno, string userid, string usercode, string orgcode)
|
|
|
{
|
|
|
MsWlPcHead data = null;
|
|
|
var list = GetDataList(" BillNo='" + billno + "'",userid,usercode,orgcode);
|
|
|
if (list.Count > 0)
|
|
|
data = list[0];
|
|
|
|
|
|
if (data == null)
|
|
|
{
|
|
|
data = new MsWlPcHead();
|
|
|
}
|
|
|
|
|
|
return data;
|
|
|
}
|
|
|
|
|
|
static public MsWlPcHead_ADL GetHeadDataByBillNo_ADL ( string billno, string userid, string usercode, string orgcode )
|
|
|
{
|
|
|
MsWlPcHead_ADL data = null;
|
|
|
var list = GetDataList_ADL(" BillNo='" + billno + "'", userid, usercode, orgcode);
|
|
|
if (list.Count > 0)
|
|
|
data = list[0];
|
|
|
|
|
|
if (data == null)
|
|
|
{
|
|
|
data = new MsWlPcHead_ADL();
|
|
|
}
|
|
|
|
|
|
return data;
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 明细表
|
|
|
|
|
|
static public List<MsWlPcFixed> GetBodyList(string strCondition)
|
|
|
{
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT ");
|
|
|
strSql.Append("BillNo,SerialNo,CreditDebit,");
|
|
|
strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=tMsWlPcFixed.CreditDebit) as CreditDebit_Ref");
|
|
|
strSql.Append(",FeeTypeCode,");
|
|
|
strSql.Append("(select Name from vMsTruckFeeType where FeeCode=tMsWlPcFixed.FeeTypeCode) as FeeTypeCode_Ref");
|
|
|
strSql.Append(",FeeTypeName,DistCount,Price,FsTotal,JsTotal,Remark,GId");
|
|
|
strSql.Append(",FeeStatus,");
|
|
|
strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=tMsWlPcFixed.FeeStatus) as FeeStatus_Ref");
|
|
|
strSql.Append(",SubmitDate,Auditoperator,AuditDate,AuditStatus");
|
|
|
strSql.Append(" from tMsWlPcFixed ");
|
|
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
|
{
|
|
|
strSql.Append(" where " + strCondition);
|
|
|
}
|
|
|
|
|
|
return SetBodyData(strSql);
|
|
|
}
|
|
|
|
|
|
private static List<MsWlPcFixed> SetBodyData(StringBuilder strSql)
|
|
|
{
|
|
|
var bodyList = new List<MsWlPcFixed>();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
MsWlPcFixed data = new MsWlPcFixed();
|
|
|
#region Set DB data to Object
|
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
|
data.SerialNo = Convert.ToDecimal(reader["SerialNo"]);
|
|
|
data.CreditDebit = Convert.ToString(reader["CreditDebit"]);
|
|
|
data.CreditDebit_Ref = Convert.ToString(reader["CreditDebit_Ref"]);
|
|
|
data.FeeTypeCode = Convert.ToString(reader["FeeTypeCode"]);
|
|
|
data.FeeTypeCode_Ref = Convert.ToString(reader["FeeTypeCode_Ref"]);
|
|
|
data.FeeTypeName = Convert.ToString(reader["FeeTypeName"]);
|
|
|
data.DistCount = Convert.ToDecimal(reader["DistCount"]);
|
|
|
data.Price = Convert.ToDecimal(reader["Price"]);
|
|
|
data.FsTotal = Convert.ToDecimal(reader["FsTotal"]);
|
|
|
data.JsTotal = Convert.ToDecimal(reader["JsTotal"]);
|
|
|
data.Remark = Convert.ToString(reader["Remark"]);
|
|
|
data.GId = Convert.ToString(reader["GId"]);
|
|
|
data.FeeStatus = Convert.ToDecimal(reader["FeeStatus"]);
|
|
|
data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]);
|
|
|
if (reader["SubmitDate"] != DBNull.Value)
|
|
|
data.SubmitDate = Convert.ToDateTime(reader["SubmitDate"]);
|
|
|
data.Auditoperator = Convert.ToString(reader["Auditoperator"]);
|
|
|
if (reader["AuditDate"] != DBNull.Value)
|
|
|
data.AuditDate = Convert.ToDateTime(reader["AuditDate"]);
|
|
|
data.AuditStatus = Convert.ToDecimal(reader["AuditStatus"]);
|
|
|
#endregion
|
|
|
|
|
|
bodyList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return bodyList;
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 烟台爱德林项目 保存路单后将路单状态写回进口业务的函电表
|
|
|
static public DBResult SaveTRUCKSTATUS ( string RefBillno, string TRUCKSTATUS )
|
|
|
{
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append(" update op_letter_truck set TRUCKSTATUS ='" + TRUCKSTATUS + "' ");
|
|
|
strSql.Append(" where LE_ID='" + RefBillno + "' ");
|
|
|
|
|
|
var _count = 0;
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
_count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
|
|
|
var _r = new DBResult();
|
|
|
if (_count > 0)
|
|
|
{
|
|
|
_r.Success = true;
|
|
|
}
|
|
|
else { _r.Success = false; }
|
|
|
|
|
|
return _r;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 烟台爱德林项目 批量设置路单完成
|
|
|
static public int SetEnd(string GIDList, string isEnd)
|
|
|
{
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append(" update tmswlpchead set isend=" + isEnd + " where gid in( " + GIDList + ")");
|
|
|
|
|
|
var _count = 0;
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
_count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
|
|
|
|
|
|
return _count;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 设置主票
|
|
|
static public int SetPGID(string GIDList, string PGID)
|
|
|
{
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append(" update tmswlpchead set PGID='" + PGID + "' where gid in( " + GIDList + ")");
|
|
|
|
|
|
var _count = 0;
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
_count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
|
|
|
|
|
|
return _count;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 参照部分
|
|
|
|
|
|
internal static List<MsWlPcHeadRefMsWlTruck> GetTruckNoList(string userid, string usercode, string orgcode)
|
|
|
{
|
|
|
var rangstr = GetRangDAStr("", userid, usercode, orgcode);
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT ");
|
|
|
strSql.Append(" A.TruckNo,A.DrvCode,isnull((select Mobile from tMsWlDriver B where B.DrvCode=A.DrvCode),'') as Mobile");
|
|
|
strSql.Append(" from tMsWlTruck A");
|
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
|
{
|
|
|
strSql.Append(" where " + rangstr);
|
|
|
}
|
|
|
|
|
|
|
|
|
var dataList = new List<MsWlPcHeadRefMsWlTruck>();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var data = new MsWlPcHeadRefMsWlTruck();
|
|
|
|
|
|
#region Set DB data to Object
|
|
|
|
|
|
data.TruckNo = Convert.ToString(reader["TruckNo"]);
|
|
|
data.DrvCode = Convert.ToString(reader["DrvCode"]);
|
|
|
data.Mobile = Convert.ToString(reader["Mobile"]);
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
dataList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return dataList;
|
|
|
}
|
|
|
|
|
|
|
|
|
internal static List<MsWlPcHeadRefMsWlTruck> GetTruckNoList(string userid, string usercode, string orgcode, string condition)
|
|
|
{
|
|
|
var rangstr = GetRangDAStr("", userid, usercode, orgcode);
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append(" SELECT '' TruckNo,'' DrvCode,'' Mobile,'' WORKNOTruckNo,'' DrvName ,'' COMPANYNAME ,'' TEAMNAME ");
|
|
|
strSql.Append(" union all ");
|
|
|
strSql.Append(" SELECT A.TruckNo,A.DrvCode,isnull((select Mobile from tMsWlDriver B where B.DrvCode=A.DrvCode),'') as Mobile ");
|
|
|
strSql.Append(" ,A.WORKNO+'-'+A.TruckNo as WORKNOTruckNo ");
|
|
|
//strSql.Append(" ,(select drvname from tMsWlDriver where drvcode= A.drvcode) DrvName ");
|
|
|
strSql.Append(",(SELECT top 1 (CODENAME+'-'+SHORTNAME) from info_Client where GID = A.OWNER) DrvName");
|
|
|
strSql.Append(" ,(select name from company where codename= A.orgcode) COMPANYNAME ");
|
|
|
strSql.Append(" ,(select teamname from tMswlTruckTeam where GID=A.TEAMGID and TruckNo= A.TruckNo) TEAMNAME ");
|
|
|
strSql.Append(" from tMsWlTruck A ");
|
|
|
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
|
{
|
|
|
strSql.Append(" where " + condition);
|
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
|
{
|
|
|
strSql.Append(" and " + rangstr);
|
|
|
}
|
|
|
}else
|
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
|
{
|
|
|
strSql.Append(" where " + rangstr);
|
|
|
}
|
|
|
|
|
|
var dataList = new List<MsWlPcHeadRefMsWlTruck>();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var data = new MsWlPcHeadRefMsWlTruck();
|
|
|
|
|
|
#region Set DB data to Object
|
|
|
|
|
|
data.TruckNo = Convert.ToString(reader["TruckNo"]);
|
|
|
data.DrvCode = Convert.ToString(reader["DrvCode"]);
|
|
|
data.DrvName = Convert.ToString(reader["DrvName"]);
|
|
|
data.Mobile = Convert.ToString(reader["Mobile"]);
|
|
|
data.WORKNOTruckNo = Convert.ToString(reader["WORKNOTruckNo"]);
|
|
|
data.COMPANYNAME = Convert.ToString(reader["COMPANYNAME"]);
|
|
|
data.TEAMNAME = Convert.ToString(reader["TEAMNAME"]);
|
|
|
#endregion
|
|
|
|
|
|
dataList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return dataList;
|
|
|
}
|
|
|
|
|
|
internal static List<MsWlPcHeadRefMsWlDriver> GetDrvNameList(string userid, string usercode, string orgcode)
|
|
|
{
|
|
|
var rangstr = GetRangDAStr("", userid, usercode, orgcode);
|
|
|
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT ");
|
|
|
strSql.Append(" DrvCode,DrvName,Mobile");
|
|
|
strSql.Append(" from tMsWlDriver");
|
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
|
{
|
|
|
strSql.Append(" where " + rangstr);
|
|
|
}
|
|
|
|
|
|
|
|
|
var dataList = new List<MsWlPcHeadRefMsWlDriver>();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var data = new MsWlPcHeadRefMsWlDriver();
|
|
|
|
|
|
#region Set DB data to Object
|
|
|
|
|
|
data.DrvCode = Convert.ToString(reader["DrvCode"]);
|
|
|
data.DrvName = Convert.ToString(reader["DrvName"]);
|
|
|
data.CodeAndName = data.DrvCode + "-" + Convert.ToString(reader["DrvName"]);
|
|
|
data.Mobile = Convert.ToString(reader["Mobile"]);
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
dataList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return dataList;
|
|
|
}
|
|
|
|
|
|
|
|
|
public static string GetRangDAStr(string tb, string userid, string usercode, string orgcode)
|
|
|
{
|
|
|
string str = "";
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT ");
|
|
|
strSql.Append(" VISIBLERANGE,OPERATERANGE ");
|
|
|
strSql.Append(" from VW_User_Authority ");
|
|
|
strSql.Append(" where [NAME]='modTruckBs' 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();
|
|
|
}
|
|
|
if (visiblerange == "4")
|
|
|
{
|
|
|
str = "1=2";
|
|
|
}
|
|
|
else if (visiblerange == "3")
|
|
|
{
|
|
|
if (tb == "index")
|
|
|
{
|
|
|
str = " UserCode='" + usercode + "'";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
str = " UPPER(OrgCode)='" + orgcode + "'";
|
|
|
}
|
|
|
}
|
|
|
else if (visiblerange == "2")
|
|
|
{
|
|
|
if (tb == "index")
|
|
|
{
|
|
|
var rangeDa = new RangeDA();
|
|
|
var companyid = rangeDa.GetCORPID(userid);
|
|
|
var deptname = rangeDa.GetDEPTNAME(userid);
|
|
|
str = " UserCode in (select codename from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "'))";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
str = " UPPER(OrgCode)='" + orgcode + "'";
|
|
|
}
|
|
|
|
|
|
}
|
|
|
else if (visiblerange == "1")
|
|
|
{
|
|
|
str = " UPPER(OrgCode)='" + orgcode + "'";
|
|
|
}
|
|
|
|
|
|
return str;
|
|
|
}
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
internal static decimal GetTruckFuel(string truckNo,string loadType, decimal ton, decimal mil)
|
|
|
{
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("select FuelTotal ");
|
|
|
strSql.Append(" from tMsWlTruckFuel ");
|
|
|
strSql.Append(" where TruckNo='{0}' and LoadType='{1}'");
|
|
|
strSql.Append(" and ({2}>=Ton1 and {2}<Ton2)");
|
|
|
|
|
|
var sql = strSql.ToString();
|
|
|
sql = string.Format(sql, truckNo, loadType, ton);
|
|
|
|
|
|
decimal fuel = 0;
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
fuel = Convert.ToDecimal(reader["FuelTotal"]);
|
|
|
break;
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
var total = decimal.Round(mil*fuel/100, 2);
|
|
|
return total;
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
#region 判断是否有费用
|
|
|
public static bool GetFeeCount(string BSNO)
|
|
|
{
|
|
|
var isfee = false;
|
|
|
var strSql = new StringBuilder();
|
|
|
strSql.Append("Select count(*) as count ");
|
|
|
strSql.Append(" from ch_fee ");
|
|
|
strSql.Append(" where BSNO='" + BSNO + "'");
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var evData = Convert.ToInt32(reader["count"]);
|
|
|
if (evData > 0) { isfee = true; };
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
return isfee;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
internal static List<MsWlBsHeadRefMsContainers> GetContainerNoList(string pcBillNo, string bsBillNo)
|
|
|
{
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
|
|
|
//已存在的派车单的箱号
|
|
|
var sqlPc = @"select A.ContainerNo,A.ContainerNoSe,A.IsDouble
|
|
|
from tMsWlPcHead A
|
|
|
where A.RefBillNo='{0}'
|
|
|
and A.BillNo<>'{1}'";
|
|
|
sqlPc = string.Format(sqlPc, bsBillNo, pcBillNo);
|
|
|
var dataListPc = new List<string>();
|
|
|
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sqlPc))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var isDouble = Convert.ToString(reader["IsDouble"]);
|
|
|
var containerNo = Convert.ToString(reader["ContainerNo"]);
|
|
|
var containerNoSe = Convert.ToString(reader["ContainerNoSe"]);
|
|
|
|
|
|
if ((!string.IsNullOrEmpty(containerNo)) && (!dataListPc.Contains(containerNo)))
|
|
|
{
|
|
|
dataListPc.Add(containerNo);
|
|
|
}
|
|
|
|
|
|
if ((isDouble == "1") && (!string.IsNullOrEmpty(containerNoSe)) && (!dataListPc.Contains(containerNoSe)))
|
|
|
{
|
|
|
dataListPc.Add(containerNoSe);
|
|
|
}
|
|
|
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
|
|
|
var sql = @"select distinct(C.ContainerNo) as ContainerNo
|
|
|
from tMsWlBsHead A, tMsWlBsCtn C
|
|
|
where A.GId=C.BillNo
|
|
|
and A.BillNo='{0}'";
|
|
|
sql = string.Format(sql, bsBillNo);
|
|
|
|
|
|
var dataList = new List<MsWlBsHeadRefMsContainers>();
|
|
|
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var containerNo = Convert.ToString(reader["ContainerNo"]);
|
|
|
if(string.IsNullOrEmpty(containerNo)) continue;
|
|
|
if(dataListPc.Contains(containerNo)) continue;
|
|
|
|
|
|
var data = new MsWlBsHeadRefMsContainers();
|
|
|
|
|
|
#region Set DB data to Object
|
|
|
|
|
|
data.ContainerNo = containerNo;
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
dataList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return dataList;
|
|
|
}
|
|
|
|
|
|
public static List<MsWlBsHeadRefMsSeals> GetSealNoList(string bsBillNo)
|
|
|
{
|
|
|
var sql = @"select C.ContainerNo,C.SealNo
|
|
|
from tMsWlBsHead A, tMsWlBsCtn C
|
|
|
where A.GId=C.BillNo
|
|
|
and A.BillNo='{0}'
|
|
|
group by C.ContainerNo,C.SealNo";
|
|
|
sql = string.Format(sql, bsBillNo);
|
|
|
|
|
|
var dataList = new List<MsWlBsHeadRefMsSeals>();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql))
|
|
|
{
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
var containerNo = Convert.ToString(reader["ContainerNo"]);
|
|
|
var sealNo = Convert.ToString(reader["SealNo"]);
|
|
|
if (string.IsNullOrEmpty(sealNo)) continue;
|
|
|
|
|
|
var data = new MsWlBsHeadRefMsSeals();
|
|
|
|
|
|
#region Set DB data to Object
|
|
|
|
|
|
data.ContainerNo = containerNo;
|
|
|
data.SealNo = sealNo;
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
dataList.Add(data);
|
|
|
}
|
|
|
reader.Close();
|
|
|
}
|
|
|
|
|
|
return dataList;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
///
|
|
|
/// </summary>
|
|
|
/// <param name="userid"></param>
|
|
|
/// <returns>0：没有任何权限;1:收费 ;2：付费 ;3：收费+付费</returns>
|
|
|
public static int GetFFSFPower (string userid) {
|
|
|
//付费sql
|
|
|
string sqlff = @"select count(*) from user_action where ACTIONID = '14B61145-C3A8-46D4-9FE4-746A805244B5'
|
|
|
and userid = '"+userid+"'";
|
|
|
//收费sql
|
|
|
string sqlsf = @"select count(*) from user_action where ACTIONID = '14B61245-C3A8-46D4-9FE4-746A805244B5'
|
|
|
and userid = '"+userid+"'";
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
|
|
|
int sf = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sqlsf));
|
|
|
int ff = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sqlff));
|
|
|
int power = 0;
|
|
|
if (ff>0&&sf>0)
|
|
|
{
|
|
|
power = 3;
|
|
|
}
|
|
|
else if (ff>0&&sf==0)
|
|
|
{
|
|
|
power = 2;
|
|
|
}
|
|
|
else if (ff==0&&sf>0)
|
|
|
{
|
|
|
power = 1;
|
|
|
}
|
|
|
return power;
|
|
|
}
|
|
|
}
|
|
|
}
|