dengyu
/
DS7
1
0
Fork 0
Code Issues Pull Requests Projects 1 Releases Wiki Activity
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.
DS7_HYRD
ShenZhenCHJY
DS7_JinGang
dev
QDHHYT
master
临时发布20240103
DS7_JingHan
DS7Zhanhao
HuanHaiYunTong
shenzhenchjy
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
DS7/DSWeb/Areas/MvcShipping/DAL/MsOpBill/MsOpBillDAL.cs

486 lines
23 KiB
C#
Raw Permalink Normal View History Unescape Escape

'first'
2 years ago

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.MvcShipping.Models.MsOpBill;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using DSWeb.Areas.Account.Models.Chfee_do;
using DSWeb.MvcShipping.Models.MsOpHistry;
using DSWeb.MvcShipping.DAL.MsSysParamSet;
namespace DSWeb.Areas.MvcShipping.DAL
{
public class MsOpBillDAL
{
#region Inquery DataList
static public List<MsOpBill> 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("BSNO,PARENTID,OPTYPE,OPLB,OPLBNAME,BSSTATUS,FEESTATUS,ACCDATE,MBLNO,HBLNO,CUSTNO,ORDERNO");
strSql.Append(",CUSTOMERNAME,BSSOURCE,BSSOURCEDETAIL,CUSTOMNO,GOODSNAME");
strSql.Append(",VESSEL,VOYNO,ETD,PORTLOAD,DESTINATION");
strSql.Append(",PORTDISCHARGE,INPUTBY,OP,CUSTSERVICE,APPLYNO");
strSql.Append(",DOC,SALE,(CASE BSSTATUS WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF ");
strSql.Append(",(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF ");
strSql.Append(",(SELECT CARGOID FROM OP_SEAE WHERE BSNO=v_op_bill.PARENTID) as CARGOID ");
strSql.Append(",YARD,CNTRTOTAL,(select DESTINATIONID FROM OP_SEAE WHERE BSNO=v_op_bill.BSNO) DESTINATIONID,(select PORTLOADID FROM OP_SEAE WHERE BSNO=v_op_bill.BSNO) PORTLOADID ");
strSql.Append(" from v_op_bill ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where " + strCondition);
}
return SetData(strSql);
}
static public MsOpBill GetData(string condition)
{
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("BSNO,PARENTID,OPTYPE,OPLB,OPLBNAME,BSSTATUS,FEESTATUS,ACCDATE,MBLNO,HBLNO,CUSTNO,ORDERNO");
strSql.Append(",CUSTOMERNAME,BSSOURCE,BSSOURCEDETAIL,CUSTOMNO,GOODSNAME");
strSql.Append(",VESSEL,VOYNO,ETD,PORTLOAD,DESTINATION");
strSql.Append(",PORTDISCHARGE,INPUTBY,OP,CUSTSERVICE,APPLYNO");
strSql.Append(",DOC,SALE,(CASE BSSTATUS WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF ");
strSql.Append(",(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF ");
strSql.Append(",(SELECT CARGOID FROM OP_SEAE WHERE BSNO=v_op_bill.PARENTID) as CARGOID ");
strSql.Append(",YARD,CNTRTOTAL,(select DESTINATIONID FROM OP_SEAE WHERE BSNO=v_op_bill.BSNO) DESTINATIONID,(select PORTLOADID FROM OP_SEAE WHERE BSNO=v_op_bill.BSNO) PORTLOADID ");
strSql.Append(" from v_op_bill ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition);
}
var list = SetData(strSql);
if (list.Count > 0)
return list[0];
return new MsOpBill();
}
private static List<MsOpBill> SetData(StringBuilder strSql)
{
var headList = new List<MsOpBill>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsOpBill data = new MsOpBill();
#region Set DB data to Object
data.BSNO = Convert.ToString(reader["BSNO"]);
data.PARENTID = Convert.ToString(reader["PARENTID"]);
if (reader["ETD"] != DBNull.Value)
data.ETD = Convert.ToDateTime(reader["ETD"]).ToString("yyyy-MM-dd");
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
data.OPLB = Convert.ToString(reader["OPLB"]);
data.OPTYPE = Convert.ToString(reader["OPTYPE"]);
data.OPLBNAME = Convert.ToString(reader["OPLBNAME"]);
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.HBLNO = Convert.ToString(reader["HBLNO"]);
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.VESSEL = Convert.ToString(reader["VESSEL"]);
data.VOYNO = Convert.ToString(reader["VOYNO"]);
data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]);
data.DESTINATION = Convert.ToString(reader["DESTINATION"]);
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
data.OP = Convert.ToString(reader["OP"]);
data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]);
data.DOC = Convert.ToString(reader["DOC"]);
data.SALE = Convert.ToString(reader["SALE"]);
data.BSSOURCE = Convert.ToString(reader["BSSOURCE"]);
data.BSSOURCEDETAIL = Convert.ToString(reader["BSSOURCEDETAIL"]);
data.BSSTATUSREF = Convert.ToString(reader["BSSTATUSREF"]);
data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);
data.APPLYNO = Convert.ToString(reader["APPLYNO"]);
data.ORDERNO = Convert.ToString(reader["ORDERNO"]);
if (reader["BSSTATUS"] != DBNull.Value)
data.BSSTATUS = Convert.ToBoolean(reader["BSSTATUS"]);
if (reader["FEESTATUS"] != DBNull.Value)
data.FEESTATUS = Convert.ToBoolean(reader["FEESTATUS"]);
data.CARGOID = Convert.ToString(reader["CARGOID"]);
data.YARD = Convert.ToString(reader["YARD"]);
data.DESTINATIONID = Convert.ToString(reader["DESTINATIONID"]);
data.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]);
data.PORTLOADID = Convert.ToString(reader["PORTLOADID"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 操作历史记录
static public List<MsOpHistry> GetHistryDataList(string strCondition, string userid, string usercode, string orgcode)
{
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("GID,BSNO,HSTYPE,INPUTTIME,INPUTBY,'' BSTEXT");
strSql.Append(",(select ShowName from [user] where GID=INPUTBY) as INPUTBYREF");
strSql.Append(" from op_histry ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where " + strCondition);
}
return SetHistryData(strSql);
}
static public MsOpHistry GetHistryData(string condition)
{
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("GID,BSNO,HSTYPE,INPUTTIME,INPUTBY,BSTEXT");
strSql.Append(",(select ShowName from [user] where GID=INPUTBY) as INPUTBYREF");
strSql.Append(" from op_histry ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition);
}
var list = SetHistryData(strSql);
if (list.Count > 0)
return list[0];
return new MsOpHistry();
}
static public MsOpHistry GetHistryDetailData(string condition)
{
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("GID,BSNO,HSTYPE,INPUTTIME,INPUTBY,BSTEXT");
strSql.Append(",(select ShowName from [user] where GID=INPUTBY) as INPUTBYREF");
strSql.Append(" from op_histry_detail ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition);
}
var list = SetHistryData(strSql);
if (list.Count > 0)
return list[0];
return new MsOpHistry();
}
private static List<MsOpHistry> SetHistryData(StringBuilder strSql)
{
var headList = new List<MsOpHistry>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsOpHistry data = new MsOpHistry();
#region Set DB data to Object
data.GID = Convert.ToString(reader["GID"]);
data.BSNO = Convert.ToString(reader["BSNO"]);
data.HSTYPE = Convert.ToString(reader["HSTYPE"]);
if (reader["INPUTTIME"] != DBNull.Value)
data.INPUTTIME = Convert.ToString(reader["INPUTTIME"]);
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
data.INPUTBYREF = Convert.ToString(reader["INPUTBYREF"]);
data.BSTEXT = Convert.ToString(reader["BSTEXT"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 结算费用明细
static public List<Chfee_do> GetFeeDoList(string strCondition,string dotype)
{
var strSql = new StringBuilder();
strSql.Append(" SELECT c.GID,c.BSNO,c.BILLNO,c.CUSTOMERNAME,c.FEEID,c.FEENAME,c.CURRENCY,c.AMOUNT,c.DOAMOUNT,c.ORIGCURRENCY,c.ORIGAMOUNT,c.EXCHANGERATE ");
strSql.Append(",c.FEETYPE,(CASE C.FEETYPE WHEN 1 THEN '收' ELSE '付' END) AS FEETYPEREF");
if (dotype == "1") {
strSql.Append(",'' INVNO,b.SETTLETIME BILLDATE,(select ShowName from [user] where GID=b.SETTLEUSER) as BILLUSER,(select STLNAME from [code_stlmode] where STLCODE=b.SETTLETYPE) as SETTLETYPEREF,'' as CREATEUSER");
}
if (dotype == "4" || dotype == "5" || dotype == "6" )
{
strSql.Append(",'' INVNO,b.APPLYTIME BILLDATE,(select ShowName from [user] where GID=b.APPLICANT) as BILLUSER,'' SETTLETYPEREF,'' as CREATEUSER");
}
if (dotype == "7")
{
strSql.Append(",b.INVOICENO INVNO,b.INVOICEMAKETIME BILLDATE,(select ShowName from [user] where GID=b.APPLICANT) as BILLUSER,(select ShowName from [user] where GID=b.OPERATOR) as CREATEUSER,'' SETTLETYPEREF");
}
strSql.Append(" FROM ch_fee_do c ");
if (dotype == "1")
{
strSql.Append(" left join ch_fee_settlement b on (b.BILLNO=c.BILLNO) ");
}
if (dotype == "5")
{
strSql.Append(" left join ch_fee_recvapplication b on (b.BILLNO=c.BILLNO) ");
}
if (dotype == "4")
{
strSql.Append(" left join ch_fee_payapplication b on (b.BILLNO=c.BILLNO) ");
}
if (dotype == "6")
{
strSql.Append(" left join ch_fee_invoiceapplication b on (b.BILLNO=c.BILLNO) ");
}
if (dotype == "7")
{
strSql.Append(" left join ch_fee_invoice b on (b.BILLNO=c.BILLNO) ");
}
strSql.Append(" where (c.ISDELETED=0 or c.ISDELETED IS NULL) ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" and " + strCondition);
}
if (dotype == "1")
{
strSql.Append(" and CATEGORY in (1,2,3,8,9) ");
}
if (dotype == "4")
{
strSql.Append(" and CATEGORY=4 ");
}
if (dotype == "5")
{
strSql.Append(" and CATEGORY=5 ");
}
if (dotype == "6")
{
strSql.Append(" and CATEGORY=6 ");
}
if (dotype == "7")
{
strSql.Append(" and CATEGORY=7 ");
}
strSql.Append(" order by c.CREATETIME ");
return SetFeeDoData(strSql);
}
static public List<Chfee_do> GetInvHXFeeDoList(string strCondition)
{
var strSql = new StringBuilder();
strSql.Append(" SELECT c.GID,c.BILLNO,c.BSNO,c.CUSTOMERNAME,c.FEEID,c.FEENAME,c.CURRENCY,c.AMOUNT,c.DOAMOUNT,c.ORIGCURRENCY,c.ORIGAMOUNT,c.EXCHANGERATE ");
strSql.Append(",c.FEETYPE,(CASE C.FEETYPE WHEN 1 THEN '收' ELSE '付' END) AS FEETYPEREF");
strSql.Append(",b.INVNO,b.INVDATE BILLDATE,b.OP as BILLUSER,'' SETTLETYPEREF,'' as CREATEUSER");
strSql.Append(" FROM ch_fee_do_invoice c ");
strSql.Append(" left join ch_fee_invoicehexiao b on (b.BILLNO=c.BILLNO) ");
strSql.Append(" where (c.ISDELETED=0 or c.ISDELETED IS NULL) ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" and " + strCondition);
}
strSql.Append(" order by c.CREATETIME ");
return SetFeeDoData(strSql);
}
private static List<Chfee_do> SetFeeDoData(StringBuilder strSql)
{
var headList = new List<Chfee_do>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
Chfee_do data = new Chfee_do();
#region Set DB data to Object
data.BSNO = Convert.ToString(reader["BSNO"]);
data.FEEID = Convert.ToString(reader["FEEID"]);
data.FEENAME = Convert.ToString(reader["FEENAME"]);
data.FEETYPE = Convert.ToInt16(reader["FEETYPE"]);
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
data.CURRENCY = Convert.ToString(reader["CURRENCY"]);
data.AMOUNT = Convert.ToDecimal(reader["AMOUNT"]);
data.DOAMOUNT = Convert.ToDecimal(reader["DOAMOUNT"]);
data.ORIGCURRENCY = Convert.ToString(reader["ORIGCURRENCY"]);
if (reader["ORIGAMOUNT"] != DBNull.Value)
data.ORIGAMOUNT = Convert.ToDecimal(reader["ORIGAMOUNT"]);
data.EXCHANGERATE = Convert.ToDecimal(reader["EXCHANGERATE"]);
data.BILLNO = Convert.ToString(reader["BILLNO"]);
if (reader["BILLDATE"] != DBNull.Value)
data.BILLDATE = Convert.ToString(reader["BILLDATE"]);
data.BILLUSER = Convert.ToString(reader["BILLUSER"]);
data.INVNO = Convert.ToString(reader["INVNO"]);
data.SETTLETYPEREF = Convert.ToString(reader["SETTLETYPEREF"]);
data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
static public MsOpSeaeBillTrack GetOpStatusData(string bsno)
{
var USEYDW = MsSysParamSetDAL.GetData("PARAMNAME='USEYDW'");
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append("BSNO,MBLNO,CARRIER CARRIERID,YARD,VESSEL,VOYNO");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='海关放行' ) AS BaoGuanFangXing ");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='预配舱单' ) AS CangDanShiJian ");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='运抵报告' ) AS YunDiShiJian ");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='装船出运' ) AS KaiChuanShiJian ");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='到港' ) AS ATA ");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='目的港提箱' ) AS MuDiGangTiXiang ");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='目的港返空' ) AS MuDiGangFanKong ");
if (USEYDW.PARAMVALUE == "1")
{
strSql.Append(",'' AS ZhuangZaiFangXing ");
}
else
strSql.Append(",(SELECT TOP 1 RDATE FROM QdPortDanPiaoChuKouZZFX WHERE TDH=op_seae.MBLNO ) AS ZhuangZaiFangXing ");
strSql.Append(",(SELECT TOP 1 CASE WHEN COMPTIME IS NULL THEN 'Y' ELSE CONVERT(varchar(100),COMPTIME, 120) END FROM op_status WHERE BSNO='" + bsno + "' AND [STATUS]='码头放行' ) AS MaTouFangXing ");
strSql.Append(" from op_seae ");
strSql.Append(" where BSNO='"+bsno+"'" );
var list = SetOpStatusData(strSql);
if (list.Count > 0)
return list[0];
return new MsOpSeaeBillTrack();
}
private static List<MsOpSeaeBillTrack> SetOpStatusData(StringBuilder strSql)
{
var headList = new List<MsOpSeaeBillTrack>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsOpSeaeBillTrack data = new MsOpSeaeBillTrack();
#region Set DB data to Object
data.MBLNO = Convert.ToString(reader["MBLNO"]);
data.CARRIERID = Convert.ToString(reader["CARRIERID"]);
data.YARD = Convert.ToString(reader["YARD"]);
//data.TiXiangShiJian = Convert.ToString(reader["TiXiangShiJian"]);
//data.FanChangShiJian = Convert.ToString(reader["FanChangShiJian"]);
data.YunDiShiJian = Convert.ToString(reader["YunDiShiJian"]);
data.CangDanShiJian = Convert.ToString(reader["CangDanShiJian"]);
data.BaoGuanFangXing = Convert.ToString(reader["BaoGuanFangXing"]);
data.ZhuangZaiFangXing = Convert.ToString(reader["ZhuangZaiFangXing"]);
data.MaTouFangXing = Convert.ToString(reader["MaTouFangXing"]);
data.KaiChuanShiJian = Convert.ToString(reader["KaiChuanShiJian"]);
data.ATA = Convert.ToString(reader["ATA"]);
data.MuDiGangTiXiang = Convert.ToString(reader["MuDiGangTiXiang"]);
data.MuDiGangFanKong = Convert.ToString(reader["MuDiGangFanKong"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#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]='modTruckBulk' 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
}
}