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.
327 lines
13 KiB
C#
327 lines
13 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.Areas.TruckMng.Models.MsWlInsure;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.EntityDA;
|
|
using DSWeb.Areas.CommMng.Models;
|
|
|
|
namespace DSWeb.Areas.TruckMng.DAL.MsWlInsure
|
|
{
|
|
public partial class MsWlInsureDAL
|
|
{
|
|
#region 主表
|
|
|
|
static public List<MsWlInsureHead> GetDataList(string strCondition, string userid, string usercode, string orgcode, string sort = null)
|
|
{
|
|
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,UserCode,UserName,JzDate,JzrCode,JzrName");
|
|
strSql.Append(",RptDate,convert(bigint ,TimeMark) as TimeMark,YwType,OrgCode,OrgName,TruckNo,");
|
|
strSql.Append("(select TruckNo from tMsWlTruck where TruckNo=tMsWlInsureHead.TruckNo) as TruckNo_Ref");
|
|
strSql.Append(",OpUserCode,");
|
|
strSql.Append("(select UserName from vMsTruckUser where UserCode=tMsWlInsureHead.OpUserCode) as OpUserCode_Ref");
|
|
strSql.Append(",OpUserName,InsureComp,InsureBillNo,InsureDate,NextDate,NoticeDays,Remark");
|
|
strSql.Append(",Beneficiary,InsureBgnDate,InsureEndDate,InsureCompCode");
|
|
strSql.Append(",isnull((select sum(InsureTotal) from tMsWlInsureBody b where b.Billno=tMsWlInsureHead.Billno),0) InsureTotal");
|
|
strSql.Append(" from tMsWlInsureHead ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
return SetData(strSql);
|
|
}
|
|
|
|
static public List<MsWlInsureHead> GetAllDataList ( string strCondition )
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BillNo,LrDate,UserCode,UserName,JzDate,JzrCode,JzrName");
|
|
strSql.Append(",RptDate,convert(bigint ,TimeMark) as TimeMark,YwType,OrgCode,OrgName,TruckNo,");
|
|
strSql.Append("(select TruckNo from tMsWlTruck where TruckNo=tMsWlInsureHead.TruckNo) as TruckNo_Ref");
|
|
strSql.Append(",OpUserCode,");
|
|
strSql.Append("(select UserName from vMsTruckUser where UserCode=tMsWlInsureHead.OpUserCode) as OpUserCode_Ref");
|
|
strSql.Append(",OpUserName,InsureComp,InsureBillNo,InsureDate,NextDate,NoticeDays,Remark");
|
|
strSql.Append(",Beneficiary,InsureBgnDate,InsureEndDate,InsureCompCode");
|
|
strSql.Append(",isnull((select sum(InsureTotal) from tMsWlInsureBody b where b.Billno=tMsWlInsureHead.Billno),0) InsureTotal");
|
|
strSql.Append(" from tMsWlInsureHead ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
|
|
return SetData(strSql);
|
|
}
|
|
|
|
|
|
private static List<MsWlInsureHead> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsWlInsureHead>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsWlInsureHead data = new MsWlInsureHead();
|
|
#region Set DB data to Object
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
if (reader["LrDate"] != DBNull.Value)
|
|
data.LrDate = Convert.ToDateTime(reader["LrDate"]);
|
|
data.UserCode = Convert.ToString(reader["UserCode"]);
|
|
data.UserName = Convert.ToString(reader["UserName"]);
|
|
if (reader["JzDate"] != DBNull.Value)
|
|
data.JzDate = Convert.ToDateTime(reader["JzDate"]);
|
|
data.JzrCode = Convert.ToString(reader["JzrCode"]);
|
|
data.JzrName = Convert.ToString(reader["JzrName"]);
|
|
data.RptDate = Convert.ToString(reader["RptDate"]);
|
|
data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
|
|
data.YwType = Convert.ToString(reader["YwType"]);
|
|
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.OpUserCode = Convert.ToString(reader["OpUserCode"]);
|
|
data.OpUserCode_Ref = Convert.ToString(reader["OpUserCode_Ref"]);
|
|
data.OpUserName = Convert.ToString(reader["OpUserName"]);
|
|
data.InsureCompCode = Convert.ToString(reader["InsureCompCode"]);
|
|
data.InsureComp = Convert.ToString(reader["InsureComp"]);
|
|
data.InsureBillNo = Convert.ToString(reader["InsureBillNo"]);
|
|
data.InsureDate = Convert.ToString(reader["InsureDate"]);
|
|
data.NextDate = Convert.ToString(reader["NextDate"]);
|
|
data.NoticeDays = Convert.ToDecimal(reader["NoticeDays"]);
|
|
data.Remark = Convert.ToString(reader["Remark"]);
|
|
data.Beneficiary = Convert.ToString(reader["Beneficiary"]);
|
|
data.InsureBgnDate = Convert.ToString(reader["InsureBgnDate"]);
|
|
data.InsureEndDate = Convert.ToString(reader["InsureEndDate"]);
|
|
data.InsureTotal = Convert.ToString(reader["InsureTotal"]);
|
|
#endregion
|
|
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return headList;
|
|
}
|
|
|
|
static public MsWlInsureHead GetHeadDataByBillNo(string billno, string userid, string usercode, string orgcode)
|
|
{
|
|
MsWlInsureHead data = null;
|
|
var list = GetDataList(" BillNo='" + billno + "'",userid,usercode,orgcode);
|
|
if (list.Count > 0)
|
|
data = list[0];
|
|
|
|
if (data == null)
|
|
{
|
|
data = new MsWlInsureHead();
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 明细表
|
|
|
|
static public List<MsWlInsureBody> GetBodyList(string strCondition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BillNo,SerialNo,InsureType,");
|
|
strSql.Append("(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99021 and EnumValueID=tMsWlInsureBody.InsureType) as InsureType_Ref");
|
|
strSql.Append(",InsureTotal,InsureAmount ");
|
|
strSql.Append(" from tMsWlInsureBody ");
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
return SetBodyData(strSql);
|
|
}
|
|
|
|
private static List<MsWlInsureBody> SetBodyData(StringBuilder strSql)
|
|
{
|
|
var bodyList = new List<MsWlInsureBody>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsWlInsureBody data = new MsWlInsureBody();
|
|
#region Set DB data to Object
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
data.SerialNo = Convert.ToDecimal(reader["SerialNo"]);
|
|
data.InsureType = Convert.ToString(reader["InsureType"]);
|
|
data.InsureType_Ref = Convert.ToString(reader["InsureType_Ref"]);
|
|
data.InsureTotal = Convert.ToDecimal(reader["InsureTotal"]);
|
|
data.InsureAmount = Convert.ToDecimal(reader["InsureAmount"]);
|
|
#endregion
|
|
|
|
bodyList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return bodyList;
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
#region 参照部分
|
|
|
|
internal static List<MsWlInsureHeadRefMsWlTruck> GetTruckNoList(string userid, string usercode, string orgcode)
|
|
{
|
|
var rangstr = GetRangDAStr("", userid, usercode, orgcode);
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append(" TruckNo");
|
|
strSql.Append(" from tMsWlTruck");
|
|
if (!string.IsNullOrEmpty(rangstr))
|
|
{
|
|
strSql.Append(" where " + rangstr);
|
|
}
|
|
|
|
|
|
var dataList = new List<MsWlInsureHeadRefMsWlTruck>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var data = new MsWlInsureHeadRefMsWlTruck();
|
|
|
|
#region Set DB data to Object
|
|
|
|
data.TruckNo = Convert.ToString(reader["TruckNo"]);
|
|
|
|
#endregion
|
|
|
|
dataList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return dataList;
|
|
}
|
|
|
|
internal static List<MsWlInsureHeadRefMsWlInsureLtd> GetMsWlInsureLtdList()
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append(" Code,Name");
|
|
strSql.Append(" from tMsWlInsureLtd");
|
|
|
|
var dataList = new List<MsWlInsureHeadRefMsWlInsureLtd>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var data = new MsWlInsureHeadRefMsWlInsureLtd();
|
|
|
|
#region Set DB data to Object
|
|
|
|
data.Code = Convert.ToString(reader["Code"]);
|
|
data.Name = Convert.ToString(reader["Name"]);
|
|
data.CodeAndName = data.Code + "-" + data.Name;
|
|
|
|
#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]='modTruckCar' 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
|
|
}
|
|
}
|