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 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 SetData(StringBuilder strSql) { var headList = new List(); 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 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 SetDetailData(StringBuilder strSql) { var headList = new List(); 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 } }