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.
286 lines
13 KiB
C#
286 lines
13 KiB
C#
|
|
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.Areas.TruckMng.Models.MsWlDj;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.EntityDA;
|
|
|
|
namespace DSWeb.Areas.TruckMng.DAL
|
|
{
|
|
public partial class MsWlDjDAL
|
|
{
|
|
#region Inquery DataList
|
|
|
|
static public List<MsWlDjHead> 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");
|
|
strSql.Append(",OrgName,YwType,convert(bigint ,TimeMark) as TimeMark,GId,SalesCode,");
|
|
strSql.Append("(select UserName from vMsTruckUser where UserCode=tMsWlDjHead.SalesCode) as SalesCode_Ref");
|
|
strSql.Append(",SalesName,CustCode,");
|
|
strSql.Append("(select CustName from vMsTruckClient where CustCode=tMsWlDjHead.CustCode) as CustCode_Ref");
|
|
strSql.Append(",CustName,CustDate,NeedLoadDate,NeedArriveDate");
|
|
strSql.Append(",ClsName,PNum,Pkgs,Weight,Length,Wide,Height,insuretotal");
|
|
strSql.Append(",SedArea,SedNation,DstArea,DetNation");
|
|
strSql.Append(",BillStatus,OneMil,OverPNum,ReMark,");
|
|
strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99029 and EnumValueID=tMsWlDjHead.BillStatus) as BillStatus_Ref");
|
|
strSql.Append(",FeeStatus,");
|
|
strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99027 and EnumValueID=tMsWlDjHead.FeeStatus) as FeeStatus_Ref");
|
|
strSql.Append(",FeeOpStatus,");
|
|
strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99035 and EnumValueID=tMsWlDjHead.FeeOpStatus) as FeeOpStatus_Ref");
|
|
|
|
strSql.Append(" from tMsWlDjHead ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
strSql.Append(" order by LrDate desc ");
|
|
|
|
return SetData(strSql);
|
|
}
|
|
|
|
static public MsWlDjHead GetData(string condition,string userid,string usercode,string orgcode)
|
|
{
|
|
var list = GetDataList(condition,userid,usercode,orgcode);
|
|
if (list.Count > 0)
|
|
return list[0];
|
|
|
|
return new MsWlDjHead();
|
|
}
|
|
|
|
|
|
private static List<MsWlDjHead> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsWlDjHead>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsWlDjHead data = new MsWlDjHead();
|
|
#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.YwType = Convert.ToString(reader["YwType"]);
|
|
data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
|
|
data.GId = Convert.ToString(reader["GId"]);
|
|
data.SalesCode = Convert.ToString(reader["SalesCode"]);
|
|
data.SalesCode_Ref = Convert.ToString(reader["SalesCode_Ref"]);
|
|
data.SalesName = Convert.ToString(reader["SalesName"]);
|
|
data.CustCode = Convert.ToString(reader["CustCode"]);
|
|
data.CustCode_Ref = Convert.ToString(reader["CustCode_Ref"]);
|
|
data.CustName = Convert.ToString(reader["CustName"]);
|
|
data.CustDate = Convert.ToString(reader["CustDate"]);
|
|
data.NeedLoadDate = Convert.ToString(reader["NeedLoadDate"]);
|
|
data.NeedArriveDate = Convert.ToString(reader["NeedArriveDate"]);
|
|
data.ClsName = Convert.ToString(reader["ClsName"]);
|
|
data.PNum = Convert.ToDecimal(reader["PNum"]);
|
|
data.PKgs = Convert.ToDecimal(reader["PKgs"]);
|
|
data.Length = Convert.ToDecimal(reader["Length"]);
|
|
data.Wide = Convert.ToDecimal(reader["Wide"]);
|
|
data.Height = Convert.ToDecimal(reader["Height"]);
|
|
data.Weight = Convert.ToDecimal(reader["Weight"]);
|
|
data.InsureTotal = Convert.ToDecimal(reader["InsureTotal"]);
|
|
|
|
data.SedArea = Convert.ToString(reader["SedArea"]);
|
|
data.SedNation = Convert.ToString(reader["SedNation"]);
|
|
data.DstArea = Convert.ToString(reader["DstArea"]);
|
|
data.DetNation = Convert.ToString(reader["DetNation"]);
|
|
data.BillStatus = Convert.ToString(reader["BillStatus"]);
|
|
data.BillStatus_Ref = Convert.ToString(reader["BillStatus_Ref"]);
|
|
data.FeeStatus = Convert.ToString(reader["FeeStatus"]);
|
|
data.FeeStatus_Ref = Convert.ToString(reader["FeeStatus_Ref"]);
|
|
data.FeeOpStatus = Convert.ToString(reader["FeeOpStatus"]);
|
|
data.FeeOpStatus_Ref = Convert.ToString(reader["FeeOpStatus_Ref"]);
|
|
data.Remark = Convert.ToString(reader["Remark"]);
|
|
data.OneMil = Convert.ToDecimal(reader["OneMil"]);
|
|
data.OverPNum = Convert.ToDecimal(reader["OverPNum"]);
|
|
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
#region Inquery DataDetailList
|
|
|
|
static public List<MsWlDjPoundView> GetDetailDataList(string strCondition)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("p.BillNo,l.SerialNo,l.DjBillNo,l.GId,l.CustCode,");
|
|
strSql.Append("(select CodeAndName from vMsTruckClient where CustCode=l.CustCode) as CustCode_Ref");
|
|
strSql.Append(",l.CustName,");
|
|
strSql.Append("l.ClsName,l.DepartureDate,l.LoadPlace,l.UnLoadPlace");
|
|
strSql.Append(",l.PlanOverLoadMil,l.PlanNoLoadMil,l.PlanLoadCount,l.OverLoadMil,l.NoLoadMil,l.LoadCount,l.PNum,l.PKgs,l.Weight");
|
|
strSql.Append(",l.RatedFuel,l.RealFuel,l.TonMil,l.WorkTime");
|
|
strSql.Append(",p.TruckNo,");
|
|
strSql.Append("(select TruckNo from tMsWlTruck where TruckNo=p.TruckNo) as TruckNo_Ref");
|
|
strSql.Append(",p.DrvCode,");
|
|
strSql.Append("(select DrvName from tMsWlDriver where DrvCode=p.DrvCode) as DrvCode_Ref");
|
|
strSql.Append(",p.DrvName,p.Mobile,dbo.trimdate(p.ExpDate) ExpDate");
|
|
strSql.Append(" from tMsWlDjPound l ");
|
|
strSql.Append(" left join tMsWlPcHead p on (p.Gid=l.billno) ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
|
|
strSql.Append(" order by l.DepartureDate ");
|
|
return SetDetailData(strSql);
|
|
}
|
|
|
|
|
|
|
|
private static List<MsWlDjPoundView> SetDetailData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsWlDjPoundView>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsWlDjPoundView data = new MsWlDjPoundView();
|
|
#region Set DB data to Object
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
data.SerialNo = Convert.ToDecimal(reader["SerialNo"]);
|
|
data.DjBillNo = Convert.ToString(reader["DjBillNo"]);
|
|
data.GId = Convert.ToString(reader["GId"]);
|
|
data.CustCode = Convert.ToString(reader["CustCode"]);
|
|
data.CustCode_Ref = Convert.ToString(reader["CustCode_Ref"]);
|
|
data.CustName = Convert.ToString(reader["CustName"]);
|
|
data.ClsName = Convert.ToString(reader["ClsName"]);
|
|
data.DepartureDate = Convert.ToString(reader["DepartureDate"]);
|
|
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
|
|
data.UnLoadPlace = Convert.ToString(reader["UnLoadPlace"]);
|
|
data.PlanOverLoadMil = Convert.ToDecimal(reader["PlanOverLoadMil"]);
|
|
data.PlanNoLoadMil = Convert.ToDecimal(reader["PlanNoLoadMil"]);
|
|
data.PlanLoadCount = Convert.ToDecimal(reader["PlanLoadCount"]);
|
|
data.OverLoadMil = Convert.ToDecimal(reader["OverLoadMil"]);
|
|
data.NoLoadMil = Convert.ToDecimal(reader["NoLoadMil"]);
|
|
data.LoadCount = Convert.ToDecimal(reader["LoadCount"]);
|
|
data.PNum = Convert.ToDecimal(reader["PNum"]);
|
|
data.PKgs = Convert.ToDecimal(reader["PKgs"]);
|
|
data.Weight = Convert.ToDecimal(reader["Weight"]);
|
|
data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
|
|
data.RealFuel = Convert.ToDecimal(reader["RealFuel"]);
|
|
data.TonMil = Convert.ToDecimal(reader["TonMil"]);
|
|
data.WorkTime = Convert.ToString(reader["WorkTime"]);
|
|
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.DrvName_Ref = Convert.ToString(reader["DrvCode_Ref"]);
|
|
data.Mobile = Convert.ToString(reader["Mobile"]);
|
|
data.ExpDate = Convert.ToString(reader["ExpDate"]);
|
|
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 参照部分
|
|
|
|
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]='modTruckDjCard' 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 + "' or SalesCode='"+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 + "'))"
|
|
+ " or SalesCode 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
|
|
}
|
|
}
|