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 'dev'
${ noResults }
DS7/DSWeb/Areas/TruckMng/DAL/MsRptPcHeadEdit/MsRptPcHeadEditDAL.cs

3138 lines
173 KiB
C#
Raw Permalink Blame History

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.Areas.CommMng.Models;
using System.Web;
using System.Data.Common;
using DSWeb.TruckMng.Comm.Cookie;
using WebSqlHelper;
using HcUtility.Core;
using DSWeb.EntityDA;
using HcUtility.Comm;
using System.Data.SqlClient;
using DSWeb.Areas.CommMng.DAL;
using System.Linq;
using DSWeb.MvcShipping.DAL.MsChFeeDAL;
using DSWeb.Common.DB;
using System.Data.Entity.Migrations;
using System.Web.UI.WebControls;
using DSWeb.Areas.MvcShipping.Helper;
using DSWeb.MvcShipping.DAL.MsSysBillNoSet;
using DSWeb.MvcShipping.Models.MsOpLetter;
using DSWeb.MvcShipping.DAL.MsCodeCtnSet;
using DSWeb.MvcShipping.Models.MsOpSeae;
using DSWeb.MvcShipping.DAL.ChMonthCloseDAL;
using DSWeb.MvcShipping.DAL.MsSysParamSet;
using DSWeb.MvcShipping.Models.MsOpOther;
using DSWeb.Areas.Account.Models.BSNOLB;
using DSWeb.Areas.MvcShipping.DAL.BookingConfirm;
using DSWeb.MvcShipping.DAL.MsOpSeaeEdiPortDAL;
using DSWeb.Areas.MvcShipping.Models.Message.VGM;
using com.sun.java.swing.plaf.motif.resources;
namespace DSWeb.Areas.TruckMng.DAL.MsRptPcHeadEdit
{
public partial class MsRptPcHeadEditDAL
{
#region 派车计划列表
static public List<MsWlPcHead_ADLEdit> GetDataList ( string strCondition, string userid, string usercode, string username, string companyid, string sort )
{ //合同信息/商品信息
var rangstr = GetRangDAStr("index", userid, usercode, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
var strSql = new StringBuilder();
strSql.Append(" select GId,case when TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,dbo.trimdate(expdate) GroupDate ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate,CustomerName,(select description from info_client where shortname=t.CustomerName) CustomerNameFull ");
strSql.Append(" ,F_CustomerName,DstArea,DetiNation ");
strSql.Append(" ,ContainerType,ContainerTypeSe,(SELECT CtnName FROM VMSTRUCKCTN WHERE CTNCODE=ContainerType) AS ContainerType_Ref ");
strSql.Append(" ,isnull(ContainerQty,0) ContainerQty,TruckNo,MblNo,dbo.trimdate(EndPortDate) EndPortDate,DrvCode,DrvName ");
strSql.Append(" ,Mobile,YardCode,YARDNAME AS YardCode_Ref ");
strSql.Append(" ,RtnYardCode,RtnYardName AS RtnYardCode_Ref ");
strSql.Append(" ,VoyVeg,OrgCode,DDCode,LoadPlace,TruckSpec,FactoryAddr,Remark ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99033 and EnumValueID=(select Property from tmswltruck where truckno=t.truckno)) PropertyRef ");
strSql.Append(" ,RefBillNo,DGTYPE,DGTYPECODE,SECOND_DRV,dbo.trimdate(ArriveDate) ArriveDate,ArriveTime,RefBillNoSe");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",GOODSCOUNT,CBM,ISHW,Ton,GoodsName,CreateTime,PGId,ContainerNo,SealNo,ContainerNoSe,SealNoSe ");
strSql.Append(" from tMsWlPcHead t ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where " + strCondition.Replace("~",""));//str.Replace("b","**");
}
//strSql.Append(" order by Expdate desc ");
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by Expdate desc," + sortstring);
}
else
{
strSql.Append(" order by Expdate desc ");
}
return SetData(strSql);
}
//static public MsWlPcHead_ADLEdit GetData ( string condition )
//{
// var _t = "";
// var list = GetDataList(condition, _t);
// if (list.Count > 0)
// return list[0];
// return new MsWlPcHead_ADLEdit();
//}
static public List<MsWlPcHead_ADLEdit> AddTail(List<MsWlPcHead_ADLEdit> tmpList)
{
double ton = 0.000;
foreach (var item in tmpList)
{
ton += Convert.ToDouble(item.Ton);
}
MsWlPcHead_ADLEdit data = new MsWlPcHead_ADLEdit();
data.GId ="0000000000000";
data.RowNumber = "合计";
data.PCSTATUSREF = "";
data.BillNo = "";
data.ExpDate = "";
data.GroupDate = "";
data.CustomerName = "";
data.CustomerNameFull = "";
data.F_CustomerName = "";
data.DstArea = "";
data.DetiNation = "";
data.FactoryAddr = "";
data.ContainerType = "";
data.ContainerType_Ref = "";
data.ContainerQty = "";
data.TruckNo = "";
data.TruckSpec = "";
data.MblNo = "";
data.EndPortDate = "";
data.DrvCode = "";
data.DrvName = "";
data.Mobile = "";
data.YardCode = "";
data.YardCode_Ref = "";
data.RtnYardCode = "";
data.RtnYardCode_Ref = "";
data.VoyVeg = "";
data.OrgCode = "";
data.DDCode = "";
data.PropertyRef = "";
data.LoadPlace = "";
data.RefBillNo = "";
data.RefBillNoSe = "";
data.DGTYPE = "";
data.DGTYPECODE = "";
data.SECOND_DRV = "";
data.ArriveDate = "";
data.ArriveTime = "";
data.DRFEESTATUS = "";
data.CRFEESTATUS = "";
data.GOODSCOUNT = "";
data.CBM = "";
data.ISHW = 0;
data.Remark = "";
data.GoodsName = "";
data.Ton = Convert.ToString(ton);
data.CreateTime = "";
tmpList.Add(data);
return tmpList;
}
private static List<MsWlPcHead_ADLEdit> SetData ( StringBuilder strSql )
{
var headList = new List<MsWlPcHead_ADLEdit>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsWlPcHead_ADLEdit data = new MsWlPcHead_ADLEdit();
#region Set DB data to Object
data.GId = Convert.ToString(reader["GId"]);
data.RowNumber = Convert.ToString(reader["RowNumber"]);
data.PCSTATUSREF = Convert.ToString(reader["PCSTATUSREF"]);
data.BillNo = Convert.ToString(reader["BillNo"]);
if (reader["ExpDate"] != DBNull.Value)
data.ExpDate = Convert.ToDateTime(reader["ExpDate"]).ToString("yyyy-MM-dd");//到港日期
//data.ExpDate = Convert.ToString(reader["ExpDate"]);
data.GroupDate = Convert.ToString(reader["GroupDate"]);
data.CustomerName = Convert.ToString(reader["CustomerName"]);
data.CustomerNameFull = Convert.ToString(reader["CustomerNameFull"]);
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
data.DstArea = Convert.ToString(reader["DstArea"]);
data.DetiNation = Convert.ToString(reader["DetiNation"]);
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
data.ContainerType = Convert.ToString(reader["ContainerType"]);
data.ContainerType_Ref = Convert.ToString(reader["ContainerType_Ref"]);
data.ContainerQty = Convert.ToString(reader["ContainerQty"]);
data.ContainerTypeSe = Convert.ToString(reader["ContainerTypeSe"]);
data.TruckNo = Convert.ToString(reader["TruckNo"]);
data.TruckSpec = Convert.ToString(reader["TruckSpec"]);
data.MblNo = Convert.ToString(reader["MblNo"]);
data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
data.DrvCode = Convert.ToString(reader["DrvCode"]);
data.DrvName = Convert.ToString(reader["DrvName"]);
data.Mobile = Convert.ToString(reader["Mobile"]);
data.YardCode = Convert.ToString(reader["YardCode"]);
data.YardCode_Ref = Convert.ToString(reader["YardCode_Ref"]);
data.RtnYardCode = Convert.ToString(reader["RtnYardCode"]);
data.RtnYardCode_Ref = Convert.ToString(reader["RtnYardCode_Ref"]);
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
data.OrgCode = Convert.ToString(reader["OrgCode"]);
data.DDCode = Convert.ToString(reader["DDCode"]);
data.PropertyRef = Convert.ToString(reader["PropertyRef"]);
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
data.RefBillNoSe = Convert.ToString(reader["RefBillNoSe"]);
data.DGTYPE = Convert.ToString(reader["DGTYPE"]);
data.DGTYPECODE = Convert.ToString(reader["DGTYPECODE"]);
data.SECOND_DRV = Convert.ToString(reader["SECOND_DRV"]);
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
data.ArriveTime = Convert.ToString(reader["ArriveTime"]);
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.Remark = Convert.ToString(reader["Remark"]);
data.GoodsName = Convert.ToString(reader["GoodsName"]);
data.Ton = Convert.ToString(reader["Ton"]);
data.CreateTime = reader["CreateTime"] == DBNull.Value ? "" : reader["CreateTime"].ToString();
data.PGId = Convert.ToString(reader["PGId"]);
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
data.SealNo = Convert.ToString(reader["SealNo"]);
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
data.SealNoSe = Convert.ToString(reader["SealNoSe"]);
#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 "部分提交";
}
else if (feestatusint.IndexOf("6:") > -1)
{
return "驳回提交";
}
else if (feestatusint.IndexOf("7:") > -1)
{
return "驳回申请";
}
return result;
}
#endregion
/// <summary>
/// 获取目的地列表
/// </summary>
/// <param name="strCondition"></param>
/// <param name="sort"></param>
/// <returns></returns>
static public List<MsWlMudidi> GetMuDiDi ( string strCondition, string sort )
{
string sql = "select DISTINCT dstarea from tMsWlPcHead where dstarea <> ''";
var list = new List<MsWlMudidi>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql))
{
while (reader.Read())
{
MsWlMudidi mdd = new MsWlMudidi();
mdd.mudidi = reader["dstarea"].ToString();
list.Add(mdd);
}
}
return list;
}
/// <summary>
/// 获取始发地列表
/// </summary>
/// <param name="strCondition"></param>
/// <param name="sort"></param>
/// <returns></returns>
static public List<MsWlShiFaDi> GetShiFaDi ( string strCondition, string sort )
{
string sql = "select DISTINCT LoadPlace from tMsWlPcHead where dstarea <> ''";
var list = new List<MsWlShiFaDi>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql))
{
while (reader.Read())
{
MsWlShiFaDi sfd = new MsWlShiFaDi();
sfd.shifadi = reader["LoadPlace"].ToString();
list.Add(sfd);
}
}
return list;
}
#region 编码生成
public static String getCodeRule ( string strRULENAME, string strfield, string strETD, string strACCDATE, string strUserID, string strCompanyID )
{
//调用编码规则
if (strETD.Trim().IndexOf("0001") > -1)
{
strETD = "";
}
string strRULEYEAR = "";
string strRULECONTENT = "";
string strCODENAME = "";
string strDEPTNO = "";
string strRULENOLENGTH = "";
int iRULENOLENGTH = 0;
int inum = 0;
string strCONTENT = "";
T_ALL_DA T_ALL_DA = new T_ALL_DA();
//
DataSet ds = T_ALL_DA.GetAllSQL("select * from code_rule where RULENAME='" + strRULENAME.Trim() + "' and RULETYPE=6");
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ISSTARTUSING"].ToString().Trim().Equals("True"))
{
if (ds.Tables[0].Rows[0]["RULEDATE"].ToString().Trim().Equals("业务日期"))
{
if (strETD.Trim() == "")
{
return "";
}
DateTime dETD = System.Convert.ToDateTime(strETD.Trim());
if (ds.Tables[0].Rows[0]["ISCLEAREDINEARLY"].ToString().Trim().Equals("True"))
{
if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4")
{
strRULEYEAR = dETD.ToString("yyyy") + dETD.ToString("MM");
}
else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2")
{
strRULEYEAR = dETD.ToString("yy") + dETD.ToString("MM");
}
}
else
{
if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4")
{
strRULEYEAR = dETD.ToString("yyyy");
}
else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2")
{
strRULEYEAR = dETD.ToString("yy");
}
}
}
else if (ds.Tables[0].Rows[0]["RULEDATE"].ToString().Trim().Equals("会计期间"))
{
if (strACCDATE.Trim() == "")
{
return "";
}
DateTime dACCDATE = System.Convert.ToDateTime(strACCDATE.Trim() + "-01");
if (ds.Tables[0].Rows[0]["ISCLEAREDINEARLY"].ToString().Trim().Equals("True"))
{
if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4")
{
strRULEYEAR = dACCDATE.ToString("yyyy") + dACCDATE.ToString("MM");
}
else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2")
{
strRULEYEAR = dACCDATE.ToString("yy") + dACCDATE.ToString("MM");
}
}
else
{
if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4")
{
strRULEYEAR = dACCDATE.ToString("yyyy");
}
else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2")
{
strRULEYEAR = dACCDATE.ToString("yy");
}
}
}
else if (ds.Tables[0].Rows[0]["RULEDATE"].ToString().Trim().Equals("自然月份"))
{
DateTime fwqdate = System.Convert.ToDateTime(T_ALL_DA.GetStrSQL("fwqdate", "select fwqdate=getdate()"));
if (ds.Tables[0].Rows[0]["ISCLEAREDINEARLY"].ToString().Trim().Equals("True"))
{
if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4")
{
strRULEYEAR = fwqdate.ToString("yyyy") + fwqdate.ToString("MM");
}
else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2")
{
strRULEYEAR = fwqdate.ToString("yy") + fwqdate.ToString("MM");
}
}
else
{
if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4")
{
strRULEYEAR = fwqdate.ToString("yyyy");
}
else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2")
{
strRULEYEAR = fwqdate.ToString("yy");
}
}
}
//
if (ds.Tables[0].Rows[0]["ISCOMPANY"].ToString().Trim().Equals("True"))
{
strCODENAME = T_ALL_DA.GetStrSQL("CODENAME", "select CODENAME from [company] where GID=(select top 1 COMPANYID from user_company where USERID='" + strUserID.Trim() + "')");
}
if (ds.Tables[0].Rows[0]["ISDEPT"].ToString().Trim().Equals("True"))
{
strDEPTNO = T_ALL_DA.GetStrSQL("DEPTNO", "select DEPTNO from [sys_dept] where LINKID=(select top 1 COMPANYID from user_company where USERID='" + strUserID.Trim() + "')");
}
strRULECONTENT = ds.Tables[0].Rows[0]["RULECONTENT"].ToString().Trim();
strRULECONTENT = strRULECONTENT.Replace("《COMPANY》", strCODENAME);
strRULECONTENT = strRULECONTENT.Replace("《DEPT》", strDEPTNO);
//
iRULENOLENGTH = int.Parse(ds.Tables[0].Rows[0]["RULENOLENGTH"].ToString().Trim());
strCONTENT = strRULECONTENT + strRULEYEAR;
//获取分公司的票号头字符
//string tempBANKSHEAD = T_ALL_DA.GetStrSQL("BANKSHEAD", "SELECT TOP 1 BANKSHEAD FROM company WHERE GID='" + strCompanyID.Trim() + "'");
//strCONTENT = tempBANKSHEAD + strCONTENT;
//
strRULENOLENGTH = T_ALL_DA.GetStrSQL(strfield, "select top 1 " + strfield + " from [tmswlPcHead] where " + strfield + " like '" + strCONTENT.Trim() + "%' order by " + strfield + " DESC");
if (strRULENOLENGTH == "")
{
strRULENOLENGTH = "1";
}
else
{
strRULENOLENGTH = strRULENOLENGTH.Replace(strCONTENT.Trim(), "");
inum = int.Parse(strRULENOLENGTH.Trim());
inum = inum + 1;
strRULENOLENGTH = inum.ToString();
}
int j = iRULENOLENGTH - strRULENOLENGTH.Length;
for (int i = 1; i <= j; i++)
{
strRULENOLENGTH = "0" + strRULENOLENGTH;
}
//
strCONTENT = strCONTENT + strRULENOLENGTH;
return strCONTENT;
}
}
}
return "";
}
#endregion
#region 潍坊松达需求
//GetWFSDList MsWlPcHead_WFSDEdit
static public List<MsWlPcHead_WFSDEdit> GetWFSDList ( string strCondition, string userid,string usercode,string username,string companyid,string sort )
{ //合同信息/商品信息
if (!string.IsNullOrWhiteSpace(userid) || !string.IsNullOrWhiteSpace(usercode) || !string.IsNullOrWhiteSpace(username) || !string.IsNullOrWhiteSpace(companyid))
{
var rangstr = GetRangDAStr("index", userid, usercode, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
}
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DdName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,VoyVegSe,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(select TOP 1 COLOR FROM code_op_status_detail WITH (NOLOCK) WHERE OPSTATUS = (CASE WHEN (SELECT TOP 1 [STATUS] FROM V_OP_STATUS WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) is null THEN '接受委托' else (SELECT TOP 1[STATUS] FROM V_OP_STATUS WHERE BSNO =t.GId ORDER BY COMPTIME DESC, INPUTTIME DESC) end))AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,(CASE ISNULL(BillStatus,0) WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF ");
strSql.Append(",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,YardCodeSe,YardNameSe");
strSql.Append(",ISSHUISHI,ISGAOWEN,ISXIANLIANG,ISCHECHUAN,ISHAIWU,ISFENGXIANG,ISWEISHENBAO,ISBAOGUAN,ISPINGTAI,FrTruckNo,FrDrvName,WmsTruckNo,WmsDrvName");
strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR,I.TTLCR,I.TTLINVCR,I.TTLDUICR,ISCANCEL");
strSql.Append( " from tMsWlPcHead t ");
strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=t.GID) ");
if (!string.IsNullOrEmpty(strCondition))
{
//strSql.Append(" where 1=1 AND " + strCondition);
strSql.Append(" where (t.GID=t.PGID OR t.PGID='' OR t.PGID IS NULL) AND EXISTS ( select 1 from tMsWlPcHead WHERE t.GID=tMsWlPcHead.PGID AND " + strCondition + " )");
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.GID=t.PGID OR t.PGID='' OR t.PGID IS NULL) ");
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
if (sortstring.ToUpper().IndexOf("DRINVSTATUS") >= 0)
strSql.Append(" order by " + sortstring.Replace("DRINVSTATUS", " CASE WHEN ISNULL(I.TTLINVDR,0)=0 THEN '未开票' ELSE CASE WHEN ISNULL(I.TTLINVDR,0)=ISNULL(I.TTLDR,0) THEN '已开票' ELSE '部分开票' END END "));
if (sortstring.ToUpper().IndexOf("DRDUISTATUS") >= 0)
strSql.Append(" order by " + sortstring.Replace("DRDUISTATUS", " CASE WHEN ISNULL(I.TTLDUIDR,0)=0 THEN '未对账' ELSE CASE WHEN ISNULL(I.TTLDUIDR,0)=ISNULL(I.TTLDR,0) THEN '已对账' ELSE '部分对账' END END "));
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by Expdate,billno ");
}
return SetWFSDData(strSql);
}
static public List<MsWlPcHead_WFSDEdit> GetWFSDFenList(string strCondition, string sort)
{ //合同信息/商品信息
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DdName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,VoyVegSe,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(select TOP 1 COLOR FROM code_op_status_detail WITH (NOLOCK) WHERE OPSTATUS = (CASE WHEN (SELECT TOP 1 [STATUS] FROM V_OP_STATUS WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) is null THEN '接受委托' else (SELECT TOP 1[STATUS] FROM V_OP_STATUS WHERE BSNO =t.GId ORDER BY COMPTIME DESC, INPUTTIME DESC) end))AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,(CASE ISNULL(BillStatus,0) WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF ");
strSql.Append(",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,YardCodeSe,YardNameSe");
strSql.Append(",ISSHUISHI,ISGAOWEN,ISXIANLIANG,ISCHECHUAN,ISHAIWU,ISFENGXIANG,ISWEISHENBAO,ISBAOGUAN,ISPINGTAI,FrTruckNo,FrDrvName,WmsTruckNo,WmsDrvName");
strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR,I.TTLCR,I.TTLINVCR,I.TTLDUICR,ISCANCEL");
strSql.Append(" from tMsWlPcHead t ");
strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=t.GID) ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where " + strCondition.Replace("~", ""));//str.Replace("b","**");
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by Expdate,billno ");
}
return SetWFSDData(strSql);
}
static public MsWlPcHead_WFSDEdit GetWFSDData ( string condition )
{
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DdName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,VoyVegSe,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,ContainerTypeSe,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",'' AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(",ISSHUISHI,ISGAOWEN,ISXIANLIANG,ISCHECHUAN,ISHAIWU,ISFENGXIANG,ISWEISHENBAO,ISBAOGUAN,ISPINGTAI,FrTruckNo,FrDrvName,WmsTruckNo,WmsDrvName");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,(CASE ISNULL(BillStatus,0) WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF" +
",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,YardCodeSe,YardNameSe ");
strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR,I.TTLCR,I.TTLINVCR,I.TTLDUICR,ISCANCEL");
strSql.Append(" from tMsWlPcHead t ");
strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=t.GID) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition.Replace("~", ""));//str.Replace("b","**");
}
var list = SetWFSDData(strSql);
if (list.Count > 0)
return list[0];
return new MsWlPcHead_WFSDEdit();
}
private static List<MsWlPcHead_WFSDEdit> SetWFSDData ( StringBuilder strSql )
{
var headList = new List<MsWlPcHead_WFSDEdit>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsWlPcHead_WFSDEdit data = new MsWlPcHead_WFSDEdit();
#region Set DB data to Object
data.PcBillType = Convert.ToString(reader["PcBillType"]);
data.PcBillTypeREF = Convert.ToString(reader["PcBillTypeREF"]);
data.timeMark = Convert.ToDecimal(reader["timeMark"]);
data.gId = Convert.ToString(reader["gId"]);
data.PGid = Convert.ToString(reader["PGid"]);
data.BLTYPE = Convert.ToString(reader["BLTYPE"]);
data.TRANSSTATUS = Convert.ToString(reader["TRANSSTATUS"]);
data.RowNumber = Convert.ToString(reader["RowNumber"]);
data.BillNo = Convert.ToString(reader["BillNo"]);
if (reader["ExpDate"] != DBNull.Value)
data.ExpDate = Convert.ToDateTime(reader["ExpDate"]).ToString("yyyy-MM-dd HH:mm:ss");//到港日期
data.GroupDate = Convert.ToString(reader["GroupDate"]);
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
data.ReturnDate = Convert.ToString(reader["ReturnDate"]);
data.CustomerName = Convert.ToString(reader["CustomerName"]);
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
data.DrvName = Convert.ToString(reader["DrvName"]);
data.DdName = Convert.ToString(reader["DdName"]);
data.DrvCode = Convert.ToString(reader["DrvCode"]);
data.TruckNo = Convert.ToString(reader["TruckNo"]);
data.MblNo = Convert.ToString(reader["MblNo"]);
data.LinkMan = Convert.ToString(reader["LinkMan"]);
data.DstArea = Convert.ToString(reader["DstArea"]);
data.DetiNation = Convert.ToString(reader["DetiNation"]);
data.ContainerQty = Convert.ToString(reader["ContainerQty"]);
data.Ton = Convert.ToString(reader["Ton"]);
data.GOODSCOUNT = Convert.ToString(reader["GOODSCOUNT"]);
data.CBM = Convert.ToString(reader["CBM"]);
data.LoadCount = Convert.ToString(reader["LoadCount"]);
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
data.TruckSpec = Convert.ToString(reader["TruckSpec"]);
data.IsDouble = Convert.ToString(reader["IsDouble"]);
data.Property = Convert.ToString(reader["Property"]);
data.YUNFEI = Convert.ToString(reader["yunfei"]);
data.HCHJ = Convert.ToString(reader["HCHJ"]);
data.ZXF = Convert.ToString(reader["ZXF"]);
data.DDF = Convert.ToString(reader["DDF"]);
data.QTFee = Convert.ToString(reader["QTFee"]);
data.TotalFee = Convert.ToString(reader["TotalFee"]);
data.Remark = Convert.ToString(reader["Remark"]);
data.FEESTATUS = Convert.ToString(reader["FeeStatus"]);
data.UserName = Convert.ToString(reader["UserName"]);
data.LrDate = Convert.ToString(reader["LrDate"]);
data.PCSTATUSREF = Convert.ToString(reader["PCSTATUSREF"]);
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
data.RefBillNose = Convert.ToString(reader["RefBillNose"]);
data.MblNoSe = Convert.ToString(reader["MblNoSe"]);
data.ContainerType = Convert.ToString(reader["ContainerType"]);
if (BasicDataRefDAL.ReadFieldExist(reader,"ContainerTypeSe"))
data.ContainerTypeSe = Convert.ToString(reader["ContainerTypeSe"]);
data.LinkTel = Convert.ToString(reader["LinkTel"]);
data.YardName = Convert.ToString(reader["YardName"]);
data.LinkManSe = Convert.ToString(reader["LinkManSe"]);
data.LinkTelSe = Convert.ToString(reader["LinkTelSe"]);
data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
data.BsType = Convert.ToString(reader["BsType"]);
data.CustName = Convert.ToString(reader["CustName"]);
data.CustLikeManName = Convert.ToString(reader["CustLikeManName"]);
data.CustTel = Convert.ToString(reader["CustTel"]);
data.ChangedReson = Convert.ToString(reader["ChangedReson"]);
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
data.DispatchName = Convert.ToString(reader["DispatchName"]);
data.INVOICENO = Convert.ToString(reader["INVOICENO"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
data.SalesName = Convert.ToString(reader["SalesName"]);
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
data.VoyVegSe = Convert.ToString(reader["VoyVegSe"]);
data.EtDate = Convert.ToString(reader["EtDate"]);
data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
data.GoodsName = Convert.ToString(reader["GoodsName"]);
data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
data.DESTINATION = Convert.ToString(reader["DESTINATION"]);
data.CargoID = Convert.ToString(reader["CARGOID"]);
data.LoadFactory = Convert.ToString(reader["LOADFACTORY"]);
data.RtnDocStatus = Convert.ToString(reader["RtnDocStatus"]);
data.RtnDocTime = Convert.ToString(reader["RtnDocTime"]);
if (reader["RoadFee"] != DBNull.Value)
data.RoadFee = Convert.ToDecimal(reader["RoadFee"]);
data.DRFEESTATUS = getfeestatus(Convert.ToString(reader["DRFEESTATUS"]));
data.CRFEESTATUS = getfeestatus(Convert.ToString(reader["CRFEESTATUS"]));
if (reader["FEESTATUSREF"] != DBNull.Value)
data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);//费用状态
// if (reader["BSSTATUSREF"] != DBNull.Value)
data.BSSTATUSREF = Convert.ToString(reader["BSSTATUSREF"]);//费用状态
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
data.SealNo = Convert.ToString(reader["SealNo"]);
data.SALECORP = Convert.ToString(reader["SALECORP"]);
data.SALECORPID = Convert.ToString(reader["SALECORPID"]);
data.DCLASS = Convert.ToString(reader["DCLASS"]);
data.DUNNO = Convert.ToString(reader["DUNNO"]);
data.OPSTATUS = Convert.ToString(reader["OPSTATUS"]);
data.COLOR = Convert.ToString(reader["COLOR"]);
if (reader["FuelPrice"] != DBNull.Value)
data.FuelPrice = Convert.ToDecimal(reader["FuelPrice"]);
if (reader["FuelQty"] != DBNull.Value)
data.FuelQty = Convert.ToDecimal(reader["FuelQty"]);
if (reader["RealMil"] != DBNull.Value)
data.RealMil = Convert.ToDecimal(reader["RealMil"]);
if (reader["RatedMil"] != DBNull.Value)
data.RatedMil = Convert.ToDecimal(reader["RatedMil"]);
if (reader["RatedFuel"] != DBNull.Value)
data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
data.DetiNationSe = Convert.ToString(reader["DetiNationSe"]);
data.FactoryAddrSe = Convert.ToString(reader["FactoryAddrSe"]);
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
if (reader["ISSHUISHI"] != DBNull.Value)
data.ISSHUISHI = Convert.ToBoolean(reader["ISSHUISHI"]);
if (reader["ISGAOWEN"] != DBNull.Value)
data.ISGAOWEN = Convert.ToBoolean(reader["ISGAOWEN"]);
if (reader["ISXIANLIANG"] != DBNull.Value)
data.ISXIANLIANG = Convert.ToBoolean(reader["ISXIANLIANG"]);
if (reader["ISCHECHUAN"] != DBNull.Value)
data.ISCHECHUAN = Convert.ToBoolean(reader["ISCHECHUAN"]);
if (reader["ISHAIWU"] != DBNull.Value)
data.ISHAIWU = Convert.ToBoolean(reader["ISHAIWU"]);
if (reader["ISFENGXIANG"] != DBNull.Value)
data.ISFENGXIANG = Convert.ToBoolean(reader["ISFENGXIANG"]);
if (reader["ISWEISHENBAO"] != DBNull.Value)
data.ISWEISHENBAO = Convert.ToBoolean(reader["ISWEISHENBAO"]);
if (reader["ISBAOGUAN"] != DBNull.Value)
data.ISBAOGUAN = Convert.ToBoolean(reader["ISBAOGUAN"]);
if (reader["ISPINGTAI"] != DBNull.Value)
data.ISPINGTAI = Convert.ToBoolean(reader["ISPINGTAI"]);
data.FrTruckNo = Convert.ToString(reader["FrTruckNo"]);
data.FrDrvName = Convert.ToString(reader["FrDrvName"]);
data.WmsTruckNo = Convert.ToString(reader["WmsTruckNo"]);
data.WmsDrvName = Convert.ToString(reader["WmsDrvName"]);
data.EtDateSe = Convert.ToString(reader["EtDateSe"]);
data.YardCodeSe = Convert.ToString(reader["YardCodeSe"]);
data.YardNameSe = Convert.ToString(reader["YardNameSe"]);
data.isEnd = Convert.ToString(reader["isEnd"]);
decimal ttldr = 0;
decimal ttlduidr = 0;
if (reader["TTLDR"] != DBNull.Value)
ttldr = Convert.ToDecimal(reader["TTLDR"]);
decimal ttlinvdr = 0;
if (reader["TTLINVDR"] != DBNull.Value)
ttlinvdr = Convert.ToDecimal(reader["TTLINVDR"]);
if (reader["TTLDUIDR"] != DBNull.Value)
ttlduidr = Convert.ToDecimal(reader["TTLDUIDR"]);
if (ttlinvdr == 0) data.DRINVSTATUS = "未开票";
else if (ttlinvdr == ttldr) data.DRINVSTATUS = "已开票";
else if (ttlinvdr != ttldr) data.DRINVSTATUS = "部分开票";
if (ttlduidr == 0) data.DRDUISTATUS = "未对账";
else if (ttlduidr == ttldr) data.DRDUISTATUS = "已对账";
else if (ttlduidr != ttldr) data.DRDUISTATUS = "部分对账";
data.ISCANCEL = Convert.ToString(reader["ISCANCEL"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
static public List<MsWlPcHead_QDLHEdit> GetQDLHList(string strCondition, string userid, string usercode, string username, string companyid, string sort)
{ //合同信息/商品信息
if (!string.IsNullOrWhiteSpace(userid) || !string.IsNullOrWhiteSpace(usercode) || !string.IsNullOrWhiteSpace(username) || !string.IsNullOrWhiteSpace(companyid))
{
var rangstr = GetRangDAStr("index", userid, usercode, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
}
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,ContainerTypeSe,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",'' AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF" +
",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,TotalMil,RealFuel,OrgName from tMsWlPcHead t ");
if (!string.IsNullOrEmpty(strCondition))
{
//strSql.Append(" where 1=1 AND " + strCondition);
strSql.Append(" where (t.GID=t.PGID OR t.PGID='' OR t.PGID IS NULL) AND EXISTS ( select 1 from tMsWlPcHead WHERE t.GID=tMsWlPcHead.PGID AND " + strCondition + " )");
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.GID=t.PGID OR t.PGID='' OR t.PGID IS NULL) ");
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by Expdate,billno ");
}
return SetQDLHData(strSql);
}
static public MsWlPcHead_QDLHEdit GetQDLHData(string condition)
{
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,ContainerTypeSe,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",'' AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF" +
",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,TotalMil,RealFuel,OrgName from tMsWlPcHead t ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition.Replace("~", ""));//str.Replace("b","**");
}
var list = SetQDLHData(strSql);
if (list.Count > 0)
return list[0];
return new MsWlPcHead_QDLHEdit();
}
private static List<MsWlPcHead_QDLHEdit> SetQDLHData(StringBuilder strSql)
{
var headList = new List<MsWlPcHead_QDLHEdit>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsWlPcHead_QDLHEdit data = new MsWlPcHead_QDLHEdit();
#region Set DB data to Object
data.PcBillType = Convert.ToString(reader["PcBillType"]);
data.PcBillTypeREF = Convert.ToString(reader["PcBillTypeREF"]);
data.timeMark = Convert.ToDecimal(reader["timeMark"]);
data.gId = Convert.ToString(reader["gId"]);
data.PGid = Convert.ToString(reader["PGid"]);
data.BLTYPE = Convert.ToString(reader["BLTYPE"]);
data.TRANSSTATUS = Convert.ToString(reader["TRANSSTATUS"]);
data.RowNumber = Convert.ToString(reader["RowNumber"]);
data.BillNo = Convert.ToString(reader["BillNo"]);
data.OrgName = Convert.ToString(reader["OrgName"]);
//data.ExpDate = Convert.ToString(reader["ExpDate"]);
if (reader["ExpDate"] != DBNull.Value)
data.ExpDate = Convert.ToDateTime(reader["ExpDate"]).ToString("yyyy-MM-dd");//到港日期
data.GroupDate = Convert.ToString(reader["GroupDate"]);
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
data.ReturnDate = Convert.ToString(reader["ReturnDate"]);
data.CustomerName = Convert.ToString(reader["CustomerName"]);
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
data.DrvName = Convert.ToString(reader["DrvName"]);
data.DrvCode = Convert.ToString(reader["DrvCode"]);
data.TruckNo = Convert.ToString(reader["TruckNo"]);
data.MblNo = Convert.ToString(reader["MblNo"]);
data.LinkMan = Convert.ToString(reader["LinkMan"]);
data.DstArea = Convert.ToString(reader["DstArea"]);
data.DetiNation = Convert.ToString(reader["DetiNation"]);
data.ContainerQty = Convert.ToString(reader["ContainerQty"]);
data.Ton = Convert.ToString(reader["Ton"]);
data.GOODSCOUNT = Convert.ToString(reader["GOODSCOUNT"]);
data.CBM = Convert.ToString(reader["CBM"]);
data.LoadCount = Convert.ToString(reader["LoadCount"]);
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
data.TruckSpec = Convert.ToString(reader["TruckSpec"]);
data.IsDouble = Convert.ToString(reader["IsDouble"]);
data.Property = Convert.ToString(reader["Property"]);
data.YUNFEI = Convert.ToString(reader["yunfei"]);
data.HCHJ = Convert.ToString(reader["HCHJ"]);
data.ZXF = Convert.ToString(reader["ZXF"]);
data.DDF = Convert.ToString(reader["DDF"]);
data.QTFee = Convert.ToString(reader["QTFee"]);
data.TotalFee = Convert.ToString(reader["TotalFee"]);
data.Remark = Convert.ToString(reader["Remark"]);
data.FEESTATUS = Convert.ToString(reader["FeeStatus"]);
data.UserName = Convert.ToString(reader["UserName"]);
data.LrDate = Convert.ToString(reader["LrDate"]);
data.PCSTATUSREF = Convert.ToString(reader["PCSTATUSREF"]);
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
data.RefBillNose = Convert.ToString(reader["RefBillNose"]);
data.MblNoSe = Convert.ToString(reader["MblNoSe"]);
data.ContainerType = Convert.ToString(reader["ContainerType"]);
if (BasicDataRefDAL.ReadFieldExist(reader, "ContainerTypeSe"))
data.ContainerTypeSe = Convert.ToString(reader["ContainerTypeSe"]);
data.LinkTel = Convert.ToString(reader["LinkTel"]);
data.YardName = Convert.ToString(reader["YardName"]);
data.LinkManSe = Convert.ToString(reader["LinkManSe"]);
data.LinkTelSe = Convert.ToString(reader["LinkTelSe"]);
data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
data.BsType = Convert.ToString(reader["BsType"]);
data.CustName = Convert.ToString(reader["CustName"]);
data.CustLikeManName = Convert.ToString(reader["CustLikeManName"]);
data.CustTel = Convert.ToString(reader["CustTel"]);
data.ChangedReson = Convert.ToString(reader["ChangedReson"]);
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
data.DispatchName = Convert.ToString(reader["DispatchName"]);
data.INVOICENO = Convert.ToString(reader["INVOICENO"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
data.SalesName = Convert.ToString(reader["SalesName"]);
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
data.EtDate = Convert.ToString(reader["EtDate"]);
data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
data.GoodsName = Convert.ToString(reader["GoodsName"]);
data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
data.DESTINATION = Convert.ToString(reader["DESTINATION"]);
data.CargoID = Convert.ToString(reader["CARGOID"]);
data.LoadFactory = Convert.ToString(reader["LOADFACTORY"]);
data.RtnDocStatus = Convert.ToString(reader["RtnDocStatus"]);
data.RtnDocTime = Convert.ToString(reader["RtnDocTime"]);
if (reader["RoadFee"] != DBNull.Value)
data.RoadFee = Convert.ToDecimal(reader["RoadFee"]);
data.DRFEESTATUS = getfeestatus(Convert.ToString(reader["DRFEESTATUS"]));
data.CRFEESTATUS = getfeestatus(Convert.ToString(reader["CRFEESTATUS"]));
if (reader["FEESTATUSREF"] != DBNull.Value)
data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);//费用状态
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
data.SealNo = Convert.ToString(reader["SealNo"]);
data.SALECORP = Convert.ToString(reader["SALECORP"]);
data.SALECORPID = Convert.ToString(reader["SALECORPID"]);
data.DCLASS = Convert.ToString(reader["DCLASS"]);
data.DUNNO = Convert.ToString(reader["DUNNO"]);
data.OPSTATUS = Convert.ToString(reader["OPSTATUS"]);
data.COLOR = Convert.ToString(reader["COLOR"]);
data.FuelPrice = Convert.ToString(reader["FuelPrice"]);
if (reader["FuelQty"] != DBNull.Value)
data.FuelQty = Convert.ToDecimal(reader["FuelQty"]);
if (reader["RealMil"] != DBNull.Value)
data.RealMil = Convert.ToDecimal(reader["RealMil"]);
if (reader["RatedMil"] != DBNull.Value)
data.RatedMil = Convert.ToDecimal(reader["RatedMil"]);
if (reader["RatedFuel"] != DBNull.Value)
data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
if (reader["TotalMil"] != DBNull.Value)
data.TotalMil = Convert.ToDecimal(reader["TotalMil"]);
if (reader["RealFuel"] != DBNull.Value)
data.RealFuel = Convert.ToDecimal(reader["RealFuel"]);
data.DetiNationSe = Convert.ToString(reader["DetiNationSe"]);
data.FactoryAddrSe = Convert.ToString(reader["FactoryAddrSe"]);
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
if (reader["EtDateSe"] != DBNull.Value)
data.EtDateSe = Convert.ToString(reader["EtDateSe"]);//到港日期
data.isEnd = Convert.ToString(reader["isEnd"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
public static DBResult HuiDan(MsWlPcHead_WFSDEdit HeadData,List<MsWlPcHead_WFSDEdit> BodyList)
{
var result = new DBResult();
var RtnDocStatus = HeadData.RtnDocStatus;
var RtnDocTime = HeadData.RtnDocTime;
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
foreach (var enumValue in BodyList)
{
var cmdUpdate = db.GetSqlStringCommand(
@"UPDATE tMsWlPcHead SET RtnDocStatus='"+RtnDocStatus+ "',RtnDocTime='"+RtnDocTime+ "' WHERE BillNo=@BillNo ");
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@BillNo", DbType.String, enumValue.BillNo);
db.ExecuteNonQuery(cmdUpdate, tran);
if (enumValue.BLTYPE == "派车分票") {
var cmdUpdateMaster = db.GetSqlStringCommand(
@"UPDATE PC SET RtnDocStatus=(CASE WHEN F.RCT=F.CT THEN '1' ELSE CASE WHEN F.NRCT=F.CT THEN '' ELSE '0' END END) FROM tMsWlPcHead PC LEFT JOIN (SELECT PGID,SUM(CASE WHEN ISNULL(RtnDocStatus,'')='' THEN 1 ELSE 0 END) NRCT,SUM(CASE WHEN ISNULL(RtnDocStatus,'')='1' THEN 1 ELSE 0 END) RCT,COUNT(GID) CT FROM tMsWlPcHead WHERE PGID='" + enumValue.PGid + "' AND GID<>PGID GROUP BY PGID) F ON (F.PGID=PC.GID) WHERE GID=@GID ");
cmdUpdateMaster.Parameters.Clear();
db.AddInParameter(cmdUpdateMaster, "@GID", DbType.String, enumValue.PGid);
db.ExecuteNonQuery(cmdUpdateMaster, tran);
}
}
tran.Commit();
result.Success = true;
result.Message = "更新成功";
}
catch (Exception e)
{
tran.Rollback();
result.Success = false;
result.Message = "更新过程出现错误"+e.Message;
}
}
return result;
}
#endregion
#region 良通捷运
static public List<MsWlPcHead_LTJYEdit> GetLTJYList(string strCondition, string userid, string usercode, string username, string companyid, string sort)
{ //合同信息/商品信息
var rangstr = GetRangDAStr("index", userid, usercode, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
var strSql = new StringBuilder();
strSql.Append(" select PcBillType,PGid,BLTYPE ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",dbo.Trimdate(t.billdate) BillDate,RealMil,ContainerNoSe");
strSql.Append(",case when ( ((select islock from wms where BLNO=t.billno)=1) OR((select islock from wms_out where BSNO=t.billno)=1) ) then 1 else 0 end islock");//用于判断该路单下是否有已锁定的入库单或出库单 如有则在前台禁止删除
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName from tMsWlPcHead t ");
if (!string.IsNullOrEmpty(strCondition))
{
//strSql.Append(" where 1=1 AND " + strCondition);
strSql.Append(" where (t.GID=t.PGID OR t.PGID='' OR t.PGID IS NULL) AND EXISTS ( select 1 from tMsWlPcHead WHERE t.GID=tMsWlPcHead.PGID AND " + strCondition + " )");
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.GID=t.PGID OR t.PGID='' OR t.PGID IS NULL) ");
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by Expdate,billno ");
}
return SetLTJYData(strSql);
}
static public List<MsWlPcHead_LTJYEdit> GetLTJYFenList(string strCondition, string sort)
{ //合同信息/商品信息
var strSql = new StringBuilder();
strSql.Append(" select PcBillType,PGid,BLTYPE ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,dbo.trimdate(EtDate) EtDate");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",dbo.Trimdate(t.billdate) BillDate,RealMil,ContainerNoSe");
strSql.Append(",case when ( ((select islock from wms where BLNO=t.billno)=1) OR((select islock from wms_out where BSNO=t.billno)=1) ) then 1 else 0 end islock");//用于判断该路单下是否有已锁定的入库单或出库单 如有则在前台禁止删除
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName from tMsWlPcHead t ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where " + strCondition.Replace("~", ""));//str.Replace("b","**");
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by Expdate,billno ");
}
return SetLTJYData(strSql);
}
static public MsWlPcHead_LTJYEdit GetLTJYData(string condition)
{
var strSql = new StringBuilder();
strSql.Append(" select PcBillType,PGid,BLTYPE ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty ");
strSql.Append(" ,Ton,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimdate(EndPortDate) EndPortDate,VoyVeg,dbo.trimdate(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimdate(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",dbo.Trimdate(t.billdate) BillDate,RealMil,ContainerNoSe");
strSql.Append(",case when ( ((select islock from wms where BLNO=t.billno)=1) OR((select islock from wms_out where BSNO=t.billno)=1) ) then 1 else 0 end islock");//用于判断该路单下是否有已锁定的入库单或出库单 如有则在前台禁止删除
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName from tMsWlPcHead t ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition.Replace("~", ""));//str.Replace("b","**");
}
var list = SetLTJYData(strSql);
if (list.Count > 0)
return list[0];
return new MsWlPcHead_LTJYEdit();
}
private static List<MsWlPcHead_LTJYEdit> SetLTJYData(StringBuilder strSql)
{
var headList = new List<MsWlPcHead_LTJYEdit>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsWlPcHead_LTJYEdit data = new MsWlPcHead_LTJYEdit();
#region Set DB data to Object
data.PcBillType = Convert.ToString(reader["PcBillType"]);
data.PcBillTypeREF = Convert.ToString(reader["PcBillTypeREF"]);
data.timeMark = Convert.ToDecimal(reader["timeMark"]);
data.gId = Convert.ToString(reader["gId"]);
data.PGid = Convert.ToString(reader["PGid"]);
data.BLTYPE = Convert.ToString(reader["BLTYPE"]);
data.RowNumber = Convert.ToString(reader["RowNumber"]);
data.BillNo = Convert.ToString(reader["BillNo"]);
if (reader["ExpDate"] != DBNull.Value)
data.ExpDate = Convert.ToDateTime(reader["ExpDate"]).ToString("yyyy-MM-dd");//到港日期
data.GroupDate = Convert.ToString(reader["GroupDate"]);
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
data.ReturnDate = Convert.ToString(reader["ReturnDate"]);
data.CustomerName = Convert.ToString(reader["CustomerName"]);
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
data.DrvName = Convert.ToString(reader["DrvName"]);
data.TruckNo = Convert.ToString(reader["TruckNo"]);
data.MblNo = Convert.ToString(reader["MblNo"]);
data.LinkMan = Convert.ToString(reader["LinkMan"]);
data.DstArea = Convert.ToString(reader["DstArea"]);
data.DetiNation = Convert.ToString(reader["DetiNation"]);
data.ContainerQty = Convert.ToString(reader["ContainerQty"]);
data.Ton = Convert.ToString(reader["Ton"]);
data.LoadCount = Convert.ToString(reader["LoadCount"]);
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
data.TruckSpec = Convert.ToString(reader["TruckSpec"]);
data.IsDouble = Convert.ToString(reader["IsDouble"]);
data.Property = Convert.ToString(reader["Property"]);
data.YUNFEI = Convert.ToString(reader["yunfei"]);
data.HCHJ = Convert.ToString(reader["HCHJ"]);
data.ZXF = Convert.ToString(reader["ZXF"]);
data.DDF = Convert.ToString(reader["DDF"]);
data.QTFee = Convert.ToString(reader["QTFee"]);
data.TotalFee = Convert.ToString(reader["TotalFee"]);
data.Remark = Convert.ToString(reader["Remark"]);
data.FEESTATUS = Convert.ToString(reader["FeeStatus"]);
data.UserName = Convert.ToString(reader["UserName"]);
data.LrDate = Convert.ToString(reader["LrDate"]);
data.PCSTATUSREF = Convert.ToString(reader["PCSTATUSREF"]);
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
data.RefBillNose = Convert.ToString(reader["RefBillNose"]);
data.MblNoSe = Convert.ToString(reader["MblNoSe"]);
data.ContainerType = Convert.ToString(reader["ContainerType"]);
data.LinkTel = Convert.ToString(reader["LinkTel"]);
data.YardName = Convert.ToString(reader["YardName"]);
data.LinkManSe = Convert.ToString(reader["LinkManSe"]);
data.LinkTelSe = Convert.ToString(reader["LinkTelSe"]);
data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
data.BsType = Convert.ToString(reader["BsType"]);
data.CustName = Convert.ToString(reader["CustName"]);
data.CustLikeManName = Convert.ToString(reader["CustLikeManName"]);
data.CustTel = Convert.ToString(reader["CustTel"]);
data.ChangedReson = Convert.ToString(reader["ChangedReson"]);
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
data.DispatchName = Convert.ToString(reader["DispatchName"]);
data.INVOICENO = Convert.ToString(reader["INVOICENO"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
data.SalesName = Convert.ToString(reader["SalesName"]);
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
data.EtDate = Convert.ToString(reader["EtDate"]);
data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
if (reader["RoadFee"] != DBNull.Value)
data.RoadFee = Convert.ToDecimal(reader["RoadFee"]);
data.DRFEESTATUS = getfeestatus(Convert.ToString(reader["DRFEESTATUS"]));
data.CRFEESTATUS = getfeestatus(Convert.ToString(reader["CRFEESTATUS"]));
data.BillDate = Convert.ToString(reader["BillDate"]);
data.RealMil = Convert.ToDecimal(reader["RealMil"]);
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
data.IsLock = Convert.ToString(reader["IsLock"]);
data.GoodsName = Convert.ToString(reader["GoodsName"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
public static DBResult UpdateFileData(string filename, string billno,string userid )
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdInsert =
db.GetSqlStringCommand(
@"update tMsWlPcHead set ContainerNoSe=@filename where BillNo = @billno ");
cmdInsert.Parameters.Clear();
db.AddInParameter(cmdInsert, "@filename", DbType.String, filename);
db.AddInParameter(cmdInsert, "@billno", DbType.String, billno);
db.ExecuteNonQuery(cmdInsert, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功" + result.Message;
return result;
}
public static DBResult UpdateFileData(string filename, string billno, string userid, string TABLENAME)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdInsert = db.GetSqlStringCommand(@"update WMS set FilePath=@filename where WMSNO = @billno ");
if (TABLENAME == "WMS")
{
cmdInsert = db.GetSqlStringCommand(
@"update WMS set FilePath=@filename where WMSNO = @billno ");
}
if (TABLENAME == "WMS_OUT")
{
cmdInsert = db.GetSqlStringCommand(
@"update WMS_OUT set FilePath=@filename where BSNO = @billno ");
}
cmdInsert.Parameters.Clear();
db.AddInParameter(cmdInsert, "@filename", DbType.String, filename);
db.AddInParameter(cmdInsert, "@billno", DbType.String, billno);
db.ExecuteNonQuery(cmdInsert, tran);
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员:" + e.Message;
return result;
}
}
result.Success = true;
result.Message = "保存成功" + result.Message;
return result;
}
static public int DeleteUploadFile(string tempGId)
{
int iResult = 0;
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
var strSql = new StringBuilder();
strSql.Append(" update tMsWlPcHead set ContainerNoSe='', where billno = '" + tempGId + "' ");
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString());
sqlTran.Commit();
iResult = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
iResult = -1;//有异常,插入失败
sqlTran.Rollback();
iResult = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
return iResult;
}
#endregion
#region 参照部分
public static string GetRangDAStr(string tb, string userid,string userCode, string username, string companyid)
{
string str = "";
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID,VSSQL ");
strSql.Append(" from VW_User_Authority ");
strSql.Append(" where [NAME]='modMsPc' and USERID='" + userid + "' and ISDELETE=0");
string visiblerange = "4";
string operaterange = "4";
string AUTHORITYID = "";
string VSSQL = "";
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"]);
AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]);
VSSQL = Convert.ToString(reader["VSSQL"]);
break;
}
reader.Close();
}
if (visiblerange == "4")
{
str = " (t.UserCode='" + userCode + "' OR t.UserName='" + username + "' OR t.LinkManSe='"+ username + "' OR t.DdName='" + username + "' OR t.SalesName='" + username + "')";
}
else if (visiblerange == "3")
{
str = " (t.UserCode='" + userCode + "' OR t.UserName='" + username + "' OR t.LinkManSe='"+ username + "' OR t.DdName='" + username + "' OR t.SalesName='" + username + "')";
}
else if (visiblerange == "2")
{
var rangeDa = new RangeDA();
var deptname = rangeDa.GetDEPTNAME(userid);
str = " (t.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 t.UserName in (select showname 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 t.DdName in (select showname 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 t.SalesName in (select showname 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 t.LinkManSe in (select showname 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 if (visiblerange == "1")
{
str = " (t.UserCode in (select CODENAME from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))"
+ " OR t.UserName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))"
+ " OR t.DdName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))"
+ " OR t.SalesName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))"
+ " OR t.LinkManSe in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "')))";
}
else if (visiblerange == "5")
{
var userstr = new StringBuilder();
userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (t.UserName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "')) " +
"or t.DdName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "')) "+
"or t.SalesName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "')) "+
"or t.LinkManSe in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "'))) ";
}
else
{
str = str + " or (t.UserName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "')) " +
"or t.DdName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "')) " +
"or t.SalesName in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) + "')) " +
"or t.LinkManSe in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + Convert.ToString(reader["COMPANYID"]) +"'))) ";
};
}
str = str + ")";
reader.Close();
}
}
else if (visiblerange == "6")
{
str = " EXISTS (select 1 from user_authority_range_op P left join [user] u on (u.GID=P.OPID) where (t.UserName=U.SHOWNAME or t.LinkManSe=U.SHOWNAME or t.DdName=U.SHOWNAME or t.SalesName=U.SHOWNAME) AND P.userid='" + userid + "' and P.AUTHORITYID='" + AUTHORITYID + "' and P.VISIBLERANGE=1) ";
}
else if (visiblerange == "0")
{
str = " 1=1 ";
}
return str;
}
#endregion
static public List<MsWlPcHead_WFSDEdit> GetHYRDList(string strCondition, string userid, string usercode, string username, string companyid, string sort,string ismaster="")
{ //合同信息/商品信息
if (!string.IsNullOrWhiteSpace(userid) || !string.IsNullOrWhiteSpace(usercode) || !string.IsNullOrWhiteSpace(username) || !string.IsNullOrWhiteSpace(companyid))
{
var rangstr = GetRangDAStr("index", userid, usercode, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
}
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DdName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,AroundTon,OverLoadMil,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,EndPortDate,VoyVeg,VoyVegSe,EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimtime(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,ContainerTypeSe,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(select TOP 1 COLOR FROM code_op_status_detail WITH (NOLOCK) WHERE OPSTATUS = (CASE WHEN (SELECT TOP 1 [STATUS] FROM V_OP_STATUS WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) is null THEN '接受委托' else (SELECT TOP 1[STATUS] FROM V_OP_STATUS WHERE BSNO =t.GId ORDER BY COMPTIME DESC, INPUTTIME DESC) end))AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,(CASE ISNULL(BillStatus,0) WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF ");
strSql.Append(",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,RealFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,YardCodeSe,YardNameSe");
strSql.Append(",ISSHUISHI,ISGAOWEN,ISXIANLIANG,ISCHECHUAN,ISHAIWU,ISFENGXIANG,ISWEISHENBAO,ISBAOGUAN,ISRIZHAO,ISBOCHUAN,ISPINGTAI,FrTruckNo,FrDrvName,WmsTruckNo,WmsDrvName");
strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR,I.TTLCR,I.TTLINVCR,I.TTLDUICR,ISCANCEL,DstAreaSe");
strSql.Append(" from tMsWlPcHead t ");
strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=t.GID) ");
if (ismaster != "1"&& ismaster != "2")
{
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where (t.BLTYPE='派车主票' or t.BLTYPE='派车单票') AND EXISTS ( select 1 from tMsWlPcHead WHERE t.GID=tMsWlPcHead.PGID AND " + strCondition + " )");
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.BLTYPE='派车主票' or t.BLTYPE='派车单票') ");
}
}
else
{
if (ismaster == "2")
{
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where (t.BLTYPE='派车分票' or t.BLTYPE='派车单票') AND " + strCondition);
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.BLTYPE='派车分票' or t.BLTYPE='派车单票') ");
}
}
else
{
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where 1=1 AND " + strCondition);
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where 1=1");
}
}
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
if (sortstring.ToUpper().IndexOf("DRINVSTATUS") >= 0)
strSql.Append(" order by " + sortstring.Replace("DRINVSTATUS", " CASE WHEN ISNULL(I.TTLINVDR,0)=0 THEN '未开票' ELSE CASE WHEN ISNULL(I.TTLINVDR,0)=ISNULL(I.TTLDR,0) THEN '已开票' ELSE '部分开票' END END "));
if (sortstring.ToUpper().IndexOf("DRDUISTATUS") >= 0)
strSql.Append(" order by " + sortstring.Replace("DRDUISTATUS", " CASE WHEN ISNULL(I.TTLDUIDR,0)=0 THEN '未对账' ELSE CASE WHEN ISNULL(I.TTLDUIDR,0)=ISNULL(I.TTLDR,0) THEN '已对账' ELSE '部分对账' END END "));
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by ArriveDate desc ");
}
return SetHYRDData(strSql);
}
static public string GetHYRDListStr(string strCondition, string userid, string usercode, string username, string companyid, string sort, string ismaster = "")
{ //合同信息/商品信息
if (!string.IsNullOrWhiteSpace(userid) || !string.IsNullOrWhiteSpace(usercode) || !string.IsNullOrWhiteSpace(username) || !string.IsNullOrWhiteSpace(companyid))
{
var rangstr = GetRangDAStr("index", userid, usercode, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
}
var strSql = new StringBuilder();
strSql.Append("SELECT * ");
strSql.Append(",CASE WHEN ISNULL(TTLINVCR,0)=0 THEN '未开票' WHEN ISNULL(TTLINVCR,0)=ISNULL(TTLCR,0) THEN '已开票' WHEN ISNULL(TTLINVCR,0)!=ISNULL(TTLCR,0) THEN '部分开票' END CRINVSTATUS ");
strSql.Append(",CASE WHEN ISNULL(TTLINVDR,0)=0 THEN '未开票' WHEN ISNULL(TTLINVDR,0)=ISNULL(TTLDR,0) THEN '已开票' WHEN ISNULL(TTLINVDR,0)!=ISNULL(TTLDR,0) THEN '部分开票' END DRINVSTATUS ");
strSql.Append(",CASE WHEN ISNULL(TTLDUIDR,0)=0 THEN '未对账' WHEN ISNULL(TTLDUIDR,0)=ISNULL(TTLDR,0) THEN '已对账' WHEN ISNULL(TTLDUIDR,0)!=ISNULL(TTLDR,0) THEN '部分对账' END DRDUISTATUS ");
strSql.Append(",CASE WHEN ISNULL(TTLDUICR,0)=0 THEN '未对账' WHEN ISNULL(TTLDUICR,0)=ISNULL(TTLCR,0) THEN '已对账' WHEN ISNULL(TTLDUICR,0)!=ISNULL(TTLCR,0) THEN '部分对账' END CRDUISTATUS ");
strSql.Append(" FROM (");
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DdName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,AroundTon,OverLoadMil,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,EndPortDate,VoyVeg,VoyVegSe,EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimtime(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,ContainerTypeSe,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(select TOP 1 COLOR FROM code_op_status_detail WITH (NOLOCK) WHERE OPSTATUS = (CASE WHEN (SELECT TOP 1 [STATUS] FROM V_OP_STATUS WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) is null THEN '接受委托' else (SELECT TOP 1[STATUS] FROM V_OP_STATUS WHERE BSNO =t.GId ORDER BY COMPTIME DESC, INPUTTIME DESC) end))AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,(CASE ISNULL(BillStatus,0) WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF ");
strSql.Append(",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,RealFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,YardCodeSe,YardNameSe");
strSql.Append(",ISSHUISHI,ISGAOWEN,ISXIANLIANG,ISCHECHUAN,ISHAIWU,ISFENGXIANG,ISWEISHENBAO,ISBAOGUAN,ISRIZHAO,ISBOCHUAN,ISPINGTAI,FrTruckNo,FrDrvName,WmsTruckNo,WmsDrvName");
strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR,I.TTLCR,I.TTLINVCR,I.TTLDUICR,ISCANCEL,DstAreaSe");
strSql.Append(" from tMsWlPcHead t ");
strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=t.GID) ");
if (ismaster != "1" && ismaster != "2")
{
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where (t.BLTYPE='派车主票' or t.BLTYPE='派车单票') AND EXISTS ( select 1 from tMsWlPcHead WHERE t.GID=tMsWlPcHead.PGID AND " + strCondition + " )");
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.BLTYPE='派车主票' or t.BLTYPE='派车单票') ");
}
}
else
{
if (ismaster == "2")
{
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where (t.BLTYPE='派车分票' or t.BLTYPE='派车单票') AND " + strCondition);
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where (t.BLTYPE='派车分票' or t.BLTYPE='派车单票') ");
}
}
else
{
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where 1=1 AND " + strCondition);
}
else
{
//strSql.Append(" where 1=1 " );
strSql.Append(" where 1=1");
}
}
}
strSql.Append(") AS T");
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
if (sortstring.ToUpper().IndexOf("DRINVSTATUS") >= 0)
strSql.Append(" order by " + sortstring.Replace("DRINVSTATUS", " CASE WHEN ISNULL(I.TTLINVDR,0)=0 THEN '未开票' ELSE CASE WHEN ISNULL(I.TTLINVDR,0)=ISNULL(I.TTLDR,0) THEN '已开票' ELSE '部分开票' END END "));
if (sortstring.ToUpper().IndexOf("DRDUISTATUS") >= 0)
strSql.Append(" order by " + sortstring.Replace("DRDUISTATUS", " CASE WHEN ISNULL(I.TTLDUIDR,0)=0 THEN '未对账' ELSE CASE WHEN ISNULL(I.TTLDUIDR,0)=ISNULL(I.TTLDR,0) THEN '已对账' ELSE '部分对账' END END "));
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by ArriveDate desc ");
}
return strSql.ToString();
}
static public MsWlPcHead_WFSDEdit GetHYRDData(string condition)
{
var strSql = new StringBuilder();
strSql.Append(" select CargoID,LoadFactory,PcBillType,PGid,BLTYPE,TRANSSTATUS ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99046 and EnumValueID=t.PcBillType) PcBillTypeREF ");
strSql.Append(" ,convert(bigint ,t.TimeMark) as timeMark,gId,UserName ");
strSql.Append(" ,ROW_NUMBER()over (partition by dbo.trimdate(expdate) order by expdate ) RowNumber ");
strSql.Append(" ,BillNo,dbo.trimtime(ExpDate) ExpDate, dbo.trimdate(expdate) GroupDate ,ArriveDate,ReturnDate ");
strSql.Append(" ,CustomerName,F_CustomerName,t.DrvName,t.DdName,t.DrvCode,t.TruckNo,MblNo,LinkMan,DstArea,DetiNation,ContainerQty,GOODSCOUNT,CBM ");
strSql.Append(" ,Ton,AroundTon,OverLoadMil,t.LoadCount,LoadPlace,TruckSpec,IsDouble,Property,RefBillNo,RefBillNose,MblNoSe,dbo.trimtime(EndPortDate) EndPortDate,VoyVeg,VoyVegSe,dbo.trimtime(EtDate) EtDate ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='运费' and FEETYPE=1)YUNFEI ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='回程合计' and FEETYPE=1)HCHJ ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='装卸费' and FEETYPE=1)ZXF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename='代垫费' and FEETYPE=1)DDF ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and feename not in('运费','回程合计','装卸费','代垫费') and FEETYPE=1)QTFee ");
strSql.Append(" ,(select sum(amount) from ch_Fee where bsno=t.gid and FEETYPE=1)TotalFee,t.Remark,t.FeeStatus,dbo.trimtime(LrDate) LrDate");
strSql.Append(" ,case when t.TruckNo='' then '接单' else case when isnull(isend,0)=0 then '派车' else '完成' end end PCSTATUSREF ");
strSql.Append(" ,ContainerType,ContainerTypeSe,LinkTel,YardName,LinkManSe,LinkTelSe,RtnYardName,BsType,CustName,CustLikeManName,CustTel,ChangedReson,DispatchName,RoadFee,FactoryAddr,ContainerNo,SealNo");
strSql.Append(",dbo.F_GetBillDrFeeStatus(t.GId) DRFEESTATUS");
strSql.Append(",dbo.F_GetBillCrFeeStatus(t.GId) CRFEESTATUS");
strSql.Append(",(SELECT TOP 1 STATUS FROM V_OP_STATUS WITH (NOLOCK) WHERE BSNO=t.GId ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",'' AS COLOR");
strSql.Append(",t.SALECORPID,(select [NAME] from [company] where GID=t.SALECORPID) as SALECORP,DCLASS,DUNNO");
strSql.Append(",ISSHUISHI,ISGAOWEN,ISXIANLIANG,ISCHECHUAN,ISHAIWU,ISFENGXIANG,ISWEISHENBAO,ISBAOGUAN,ISRIZHAO,ISBOCHUAN,ISPINGTAI,FrTruckNo,FrDrvName,WmsTruckNo,WmsDrvName");
strSql.Append(" ,INVOICENO,KINDPKGS,CUSTNO,SalesName,GoodsName,PORTLOAD,DESTINATION,RtnDocStatus,RtnDocTime,ACCDATE,(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,(CASE ISNULL(BillStatus,0) WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF" +
",FuelPrice,FuelQty,RealMil,isEnd,RatedMil,RatedFuel,RealFuel,DetiNationSe,FactoryAddrSe,ContainerNoSe,EtDateSe,YardCodeSe,YardNameSe ");
strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR,I.TTLCR,I.TTLINVCR,I.TTLDUICR,ISCANCEL,DstAreaSe");
strSql.Append(" from tMsWlPcHead t ");
strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=t.GID) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" where " + condition.Replace("~", ""));//str.Replace("b","**");
}
var list = SetHYRDData(strSql);
if (list.Count > 0)
return list[0];
return new MsWlPcHead_WFSDEdit();
}
private static List<MsWlPcHead_WFSDEdit> SetHYRDData(StringBuilder strSql)
{
var headList = new List<MsWlPcHead_WFSDEdit>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
MsWlPcHead_WFSDEdit data = new MsWlPcHead_WFSDEdit();
#region Set DB data to Object
data.PcBillType = Convert.ToString(reader["PcBillType"]);
data.PcBillTypeREF = Convert.ToString(reader["PcBillTypeREF"]);
data.timeMark = Convert.ToDecimal(reader["timeMark"]);
data.gId = Convert.ToString(reader["gId"]);
data.PGid = Convert.ToString(reader["PGid"]);
data.BLTYPE = Convert.ToString(reader["BLTYPE"]);
data.TRANSSTATUS = Convert.ToString(reader["TRANSSTATUS"]);
data.RowNumber = Convert.ToString(reader["RowNumber"]);
data.BillNo = Convert.ToString(reader["BillNo"]);
if (reader["ExpDate"] != DBNull.Value)
data.ExpDate = Convert.ToDateTime(reader["ExpDate"]).ToString("yyyy-MM-dd");//到港日期
data.GroupDate = Convert.ToString(reader["GroupDate"]);
data.ArriveDate = Convert.ToString(reader["ArriveDate"]);
data.ReturnDate = Convert.ToString(reader["ReturnDate"]);
data.CustomerName = Convert.ToString(reader["CustomerName"]);
data.F_CustomerName = Convert.ToString(reader["F_CustomerName"]);
data.DrvName = Convert.ToString(reader["DrvName"]);
data.DdName = Convert.ToString(reader["DdName"]);
data.DrvCode = Convert.ToString(reader["DrvCode"]);
data.TruckNo = Convert.ToString(reader["TruckNo"]);
data.MblNo = Convert.ToString(reader["MblNo"]);
data.LinkMan = Convert.ToString(reader["LinkMan"]);
data.DstArea = Convert.ToString(reader["DstArea"]);
data.DstAreaSe = Convert.ToString(reader["DstAreaSe"]);
data.DetiNation = Convert.ToString(reader["DetiNation"]);
data.ContainerQty = Convert.ToString(reader["ContainerQty"]);
data.OverLoadMil = Convert.ToString(reader["OverLoadMil"]);
data.Ton = Convert.ToString(reader["Ton"]);
data.AroundTon = Convert.ToString(reader["AroundTon"]);
data.GOODSCOUNT = Convert.ToString(reader["GOODSCOUNT"]);
data.CBM = Convert.ToString(reader["CBM"]);
data.LoadCount = Convert.ToString(reader["LoadCount"]);
data.LoadPlace = Convert.ToString(reader["LoadPlace"]);
data.TruckSpec = Convert.ToString(reader["TruckSpec"]);
data.IsDouble = Convert.ToString(reader["IsDouble"]);
data.Property = Convert.ToString(reader["Property"]);
data.YUNFEI = Convert.ToString(reader["yunfei"]);
data.HCHJ = Convert.ToString(reader["HCHJ"]);
data.ZXF = Convert.ToString(reader["ZXF"]);
data.DDF = Convert.ToString(reader["DDF"]);
data.QTFee = Convert.ToString(reader["QTFee"]);
data.TotalFee = Convert.ToString(reader["TotalFee"]);
data.Remark = Convert.ToString(reader["Remark"]);
data.FEESTATUS = Convert.ToString(reader["FeeStatus"]);
data.UserName = Convert.ToString(reader["UserName"]);
data.LrDate = Convert.ToString(reader["LrDate"]);
data.PCSTATUSREF = Convert.ToString(reader["PCSTATUSREF"]);
data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
data.RefBillNose = Convert.ToString(reader["RefBillNose"]);
data.MblNoSe = Convert.ToString(reader["MblNoSe"]);
data.ContainerType = Convert.ToString(reader["ContainerType"]);
if (BasicDataRefDAL.ReadFieldExist(reader, "ContainerTypeSe"))
data.ContainerTypeSe = Convert.ToString(reader["ContainerTypeSe"]);
data.LinkTel = Convert.ToString(reader["LinkTel"]);
data.YardName = Convert.ToString(reader["YardName"]);
data.LinkManSe = Convert.ToString(reader["LinkManSe"]);
data.LinkTelSe = Convert.ToString(reader["LinkTelSe"]);
data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
data.BsType = Convert.ToString(reader["BsType"]);
data.CustName = Convert.ToString(reader["CustName"]);
data.CustLikeManName = Convert.ToString(reader["CustLikeManName"]);
data.CustTel = Convert.ToString(reader["CustTel"]);
data.ChangedReson = Convert.ToString(reader["ChangedReson"]);
data.FactoryAddr = Convert.ToString(reader["FactoryAddr"]);
data.DispatchName = Convert.ToString(reader["DispatchName"]);
data.INVOICENO = Convert.ToString(reader["INVOICENO"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
data.SalesName = Convert.ToString(reader["SalesName"]);
data.VoyVeg = Convert.ToString(reader["VoyVeg"]);
data.VoyVegSe = Convert.ToString(reader["VoyVegSe"]);
if (reader["EtDate"] != DBNull.Value&& Convert.ToString(reader["EtDate"]) != "")
data.EtDate = Convert.ToDateTime(reader["EtDate"]).ToString("yyyy-MM-dd HH:mm:ss");//到港日期
if (reader["EndPortDate"] != DBNull.Value && Convert.ToString(reader["EndPortDate"]) != "")
data.EndPortDate = Convert.ToDateTime(reader["EndPortDate"]).ToString("yyyy-MM-dd HH:mm:ss");//到港日期
//data.EtDate = Convert.ToString(reader["EtDate"]);
//data.EndPortDate = Convert.ToString(reader["EndPortDate"]);
data.GoodsName = Convert.ToString(reader["GoodsName"]);
data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
data.DESTINATION = Convert.ToString(reader["DESTINATION"]);
data.CargoID = Convert.ToString(reader["CARGOID"]);
data.LoadFactory = Convert.ToString(reader["LOADFACTORY"]);
data.RtnDocStatus = Convert.ToString(reader["RtnDocStatus"]);
data.RtnDocTime = Convert.ToString(reader["RtnDocTime"]);
if (reader["RoadFee"] != DBNull.Value)
data.RoadFee = Convert.ToDecimal(reader["RoadFee"]);
data.DRFEESTATUS = getfeestatus(Convert.ToString(reader["DRFEESTATUS"]));
data.CRFEESTATUS = getfeestatus(Convert.ToString(reader["CRFEESTATUS"]));
if (reader["FEESTATUSREF"] != DBNull.Value)
data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);//费用状态
// if (reader["BSSTATUSREF"] != DBNull.Value)
data.BSSTATUSREF = Convert.ToString(reader["BSSTATUSREF"]);//费用状态
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
data.SealNo = Convert.ToString(reader["SealNo"]);
data.SALECORP = Convert.ToString(reader["SALECORP"]);
data.SALECORPID = Convert.ToString(reader["SALECORPID"]);
data.DCLASS = Convert.ToString(reader["DCLASS"]);
data.DUNNO = Convert.ToString(reader["DUNNO"]);
data.OPSTATUS = Convert.ToString(reader["OPSTATUS"]);
data.COLOR = Convert.ToString(reader["COLOR"]);
if (reader["FuelPrice"] != DBNull.Value)
data.FuelPrice = Convert.ToDecimal(reader["FuelPrice"]);
if (reader["FuelQty"] != DBNull.Value)
data.FuelQty = Convert.ToDecimal(reader["FuelQty"]);
if (reader["RealMil"] != DBNull.Value)
data.RealMil = Convert.ToDecimal(reader["RealMil"]);
if (reader["RatedMil"] != DBNull.Value)
data.RatedMil = Convert.ToDecimal(reader["RatedMil"]);
if (reader["RatedFuel"] != DBNull.Value)
data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
if (reader["RealFuel"] != DBNull.Value)
data.RealFuel = Convert.ToDecimal(reader["RealFuel"]);
data.DetiNationSe = Convert.ToString(reader["DetiNationSe"]);
data.FactoryAddrSe = Convert.ToString(reader["FactoryAddrSe"]);
data.ContainerNoSe = Convert.ToString(reader["ContainerNoSe"]);
if (reader["ISSHUISHI"] != DBNull.Value)
data.ISSHUISHI = Convert.ToBoolean(reader["ISSHUISHI"]);
if (reader["ISGAOWEN"] != DBNull.Value)
data.ISGAOWEN = Convert.ToBoolean(reader["ISGAOWEN"]);
if (reader["ISXIANLIANG"] != DBNull.Value)
data.ISXIANLIANG = Convert.ToBoolean(reader["ISXIANLIANG"]);
if (reader["ISCHECHUAN"] != DBNull.Value)
data.ISCHECHUAN = Convert.ToBoolean(reader["ISCHECHUAN"]);
if (reader["ISHAIWU"] != DBNull.Value)
data.ISHAIWU = Convert.ToBoolean(reader["ISHAIWU"]);
if (reader["ISFENGXIANG"] != DBNull.Value)
data.ISFENGXIANG = Convert.ToBoolean(reader["ISFENGXIANG"]);
if (reader["ISWEISHENBAO"] != DBNull.Value)
data.ISWEISHENBAO = Convert.ToBoolean(reader["ISWEISHENBAO"]);
if (reader["ISBAOGUAN"] != DBNull.Value)
data.ISBAOGUAN = Convert.ToBoolean(reader["ISBAOGUAN"]);
if (reader["ISBOCHUAN"] != DBNull.Value)
data.ISBOCHUAN = Convert.ToBoolean(reader["ISBOCHUAN"]);
if (reader["ISRIZHAO"] != DBNull.Value)
data.ISRIZHAO = Convert.ToBoolean(reader["ISRIZHAO"]);
if (reader["ISPINGTAI"] != DBNull.Value)
data.ISPINGTAI = Convert.ToBoolean(reader["ISPINGTAI"]);
data.FrTruckNo = Convert.ToString(reader["FrTruckNo"]);
data.FrDrvName = Convert.ToString(reader["FrDrvName"]);
data.WmsTruckNo = Convert.ToString(reader["WmsTruckNo"]);
data.WmsDrvName = Convert.ToString(reader["WmsDrvName"]);
data.EtDateSe = Convert.ToString(reader["EtDateSe"]);
data.YardCodeSe = Convert.ToString(reader["YardCodeSe"]);
data.YardNameSe = Convert.ToString(reader["YardNameSe"]);
data.isEnd = Convert.ToString(reader["isEnd"]);
decimal ttldr = 0;
decimal ttlduidr = 0;
if (reader["TTLDR"] != DBNull.Value)
ttldr = Convert.ToDecimal(reader["TTLDR"]);
decimal ttlinvdr = 0;
if (reader["TTLINVDR"] != DBNull.Value)
ttlinvdr = Convert.ToDecimal(reader["TTLINVDR"]);
if (reader["TTLDUIDR"] != DBNull.Value)
ttlduidr = Convert.ToDecimal(reader["TTLDUIDR"]);
if (ttlinvdr == 0) data.DRINVSTATUS = "未开票";
else if (ttlinvdr == ttldr) data.DRINVSTATUS = "已开票";
else if (ttlinvdr != ttldr) data.DRINVSTATUS = "部分开票";
if (ttlduidr == 0) data.DRDUISTATUS = "未对账";
else if (ttlduidr == ttldr) data.DRDUISTATUS = "已对账";
else if (ttlduidr != ttldr) data.DRDUISTATUS = "部分对账";
decimal ttlcr = 0;
decimal ttlduicr = 0;
if (reader["TTLCR"] != DBNull.Value)
ttlcr = Convert.ToDecimal(reader["TTLCR"]);
decimal ttlinvcr = 0;
if (reader["TTLINVCR"] != DBNull.Value)
ttlinvcr = Convert.ToDecimal(reader["TTLINVCR"]);
if (reader["TTLDUICR"] != DBNull.Value)
ttlduicr = Convert.ToDecimal(reader["TTLDUICR"]);
if (ttlinvcr == 0) data.CRINVSTATUS = "未开票";
else if (ttlinvcr == ttlcr) data.CRINVSTATUS = "已开票";
else if (ttlinvcr != ttlcr) data.CRINVSTATUS = "部分开票";
if (ttlduicr == 0) data.CRDUISTATUS = "未对账";
else if (ttlduicr == ttldr) data.CRDUISTATUS = "已对账";
else if (ttlduicr != ttldr) data.CRDUISTATUS = "部分对账";
data.ISCANCEL = Convert.ToString(reader["ISCANCEL"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
public static DBResult SaveFixFee(string LE_ID, List<MsWlPcFixed> bodyList)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
//var cmddelete = db.GetSqlStringCommand(@"delete from tMsWlPcFixed where BillNo='" + LE_ID + "'");
//db.ExecuteNonQuery(cmddelete, tran);
var cntr = "";
if (bodyList != null)
{
foreach (var enumValue in bodyList)
{
if (enumValue.BillNo == "*" || enumValue.BillNo == "")
{
var cmdInsert = db.GetSqlStringCommand(@"insert into tMsWlPcFixed (GId,BillNo,SerialNo,CreditDebit,FeeTypeCode,FeeTypeName,DistCount,Price,FsTotal,JsTotal,Remark,ENTEROPERATOR,ENTERDATE,FTruckNo) values (newid(),'" + LE_ID
+ "'," + enumValue.SerialNo + "," + enumValue.CreditDebit + ",'" + enumValue.FeeTypeCode + "','" + enumValue.FeeTypeName + "'," + enumValue.DistCount + "," + enumValue.Price + "," + enumValue.FsTotal + "," + enumValue.JsTotal + ",'" + enumValue.Remark + "','"+ enumValue.ENTEROPERATOR + "',GETDATE(),'"+enumValue.FTruckNo+"')");
db.ExecuteNonQuery(cmdInsert, tran);
}
else
{
var cmdUpdate = db.GetSqlStringCommand(@"update tMsWlPcFixed set SerialNo=" + enumValue.SerialNo + ",CreditDebit=" + enumValue.CreditDebit + ",FeeTypeCode='" + enumValue.FeeTypeCode + "',FeeTypeName='" + enumValue.FeeTypeName
+ "' ,DistCount=" + enumValue.DistCount + " ,Price=" + enumValue.Price + ",FsTotal=" + enumValue.FsTotal+ " ,JsTotal=" + enumValue.JsTotal + ",Remark='" + enumValue.Remark + "',FTruckNo='"+enumValue.FTruckNo+"' where GId='" + enumValue.GId + "'");
db.ExecuteNonQuery(cmdUpdate, tran);
}
}
}
//
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功" + result.Message;
return result;
}
public static DBResult SaveCtnDetail(string bsno, List<MsOpSeaeDetail> bodyList, string userid)
{
var CodeCtnList = MsCodeCtnSetDAL.GetListByPage("", "", 0, 100);
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
var tempctnstr = "";
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
if (bodyList != null)
{
//var StrSerialNo = "('1000'";
foreach (var enumValue in bodyList)
{
//StrSerialNo = StrSerialNo + ",'" + Convert.ToString(enumValue.CTN_ID) + "'";
var SIZE = enumValue.SIZE;
string CTN = enumValue.CTN;
decimal TEU = 0;
var iTEU = "";
if (!string.IsNullOrEmpty(enumValue.CTNALL))
{
var ctn = CodeCtnList.Find(x => x.CTN == enumValue.CTNALL);
if (ctn != null && ctn.TEU != 0)
{
TEU = Convert.ToDecimal(ctn.TEU) * enumValue.CTNNUM;
}
else
{
SIZE = enumValue.CTNALL.ToString().Trim().Substring(0, 2);
CTN = enumValue.CTNALL.ToString().Trim().Substring(2);
if (enumValue.CTNALL.IndexOf("'") > 0) CTN = enumValue.CTNALL.ToString().Trim().Substring(3);
TEU = enumValue.CTNNUM;
iTEU = enumValue.CTNALL.ToString().Trim().Substring(0, 1);
if (iTEU == "4")
{
TEU = TEU * 2;
}
else if (iTEU == "L")
{
TEU = 0;
}
}
}
if (enumValue.TRUCKFEE == "") enumValue.TRUCKFEE = "0";
if (enumValue.FREESTORAGEDAY == "") enumValue.FREESTORAGEDAY = "0";
if (enumValue.STORAGEDAY == "") enumValue.STORAGEDAY = "0";
if (enumValue.STORAGEPRICE == "") enumValue.STORAGEPRICE = "0";
if (enumValue.STORAGEFEE == "") enumValue.STORAGEFEE = "0";
if (enumValue.FREECTNDAY == "") enumValue.FREECTNDAY = "0";
if (enumValue.CTNDAY == "") enumValue.CTNDAY = "0";
if (enumValue.CTNPRICE == "") enumValue.CTNPRICE = "0";
if (enumValue.CTNFEE == "") enumValue.CTNFEE = "0";
//
if (enumValue.BSNO == "*")
{
var cmdInsert = db.GetSqlStringCommand(@"insert into op_ctn (CTN_ID,BSNO,CTNCODE,[SIZE],CTN,CTNNUM,TEU,CTNALL,CNTRNO,SEALNO,PKGS,KGS,CBM,REMARK,KINDPKGS,TAREWEIGHT,GOODSNAME,CTNSTATUS,MASTERNO,WEIGHTYPE,WEIGHKGS,WEIGHATTN,WEIGHTEL,WEIGHSIGN,WEIGHDATE,TRUCKER,TRUCKFEE,FREESTORAGEDAY,STORAGEDAY,STORAGEPRICE,STORAGEFEE,FREECTNDAY,CTNDAY,CTNPRICE,CTNFEE,TRUCKNO,VGMCONNCOM,VGMADDR,VGMEMAIL,ISTEMP,TIXIANGSHIJIAN) values ('" + enumValue.CTN_ID
+ "','" + bsno + "','" + enumValue.CTNCODE + "','" + SIZE + "','" + CTN + "'," + enumValue.CTNNUM + "," + TEU + ",'" + enumValue.CTNALL.Replace("'", "''") + "','" + enumValue.CNTRNO + "','" + enumValue.SEALNO + "','" + enumValue.PKGS + "'," + enumValue.KGS + "," + enumValue.CBM + ",'" + enumValue.REMARK + "','" + enumValue.KINDPKGS + "'," + enumValue.TAREWEIGHT + ",'','"
+ enumValue.CTNSTATUS + "','" + enumValue.MASTERNO + "','" + enumValue.WEIGHTYPE + "'," + enumValue.WEIGHKGS + ",'" + enumValue.WEIGHATTN + "','" + enumValue.WEIGHTEL + "','" + enumValue.WEIGHSIGN + "','" + enumValue.WEIGHDATE + "','" + enumValue.TRUCKER + "'," + enumValue.TRUCKFEE
+ "," + enumValue.FREESTORAGEDAY + "," + enumValue.STORAGEDAY + "," + enumValue.STORAGEPRICE + "," + enumValue.STORAGEFEE + "," + enumValue.FREECTNDAY + "," + enumValue.CTNDAY + "," + enumValue.CTNPRICE + "," + enumValue.CTNFEE + ",'" + enumValue.TRUCKNO + "','" + enumValue.VGMCONNCOM + "','" + enumValue.VGMADDR + "','" + enumValue.VGMEMAIL + "','" + enumValue.ISTEMP + "','" + enumValue.TIXIANGSHIJIAN + "')");
db.ExecuteNonQuery(cmdInsert, tran);
}
else
{
var cmdUpdate = db.GetSqlStringCommand(@"update op_ctn set CTNCODE='" + enumValue.CTNCODE + "',[SIZE]='" + SIZE + "',CTN='" + CTN + "',CTNNUM=" + enumValue.CTNNUM + ",TEU=" + TEU + ",CTNALL='" + enumValue.CTNALL.Replace("'", "''") + "',CNTRNO='" + enumValue.CNTRNO + "',SEALNO='" + enumValue.SEALNO + "',PKGS='" + enumValue.PKGS + "',KGS=" + enumValue.KGS
+ ",CBM=" + enumValue.CBM + ",REMARK='" + enumValue.REMARK + "',KINDPKGS='" + enumValue.KINDPKGS + "',TAREWEIGHT=" + enumValue.TAREWEIGHT + ",CTNSTATUS='" + enumValue.CTNSTATUS + "',MASTERNO='" + enumValue.MASTERNO + "',WEIGHTYPE='" + enumValue.WEIGHTYPE + "',WEIGHKGS=" + enumValue.WEIGHKGS + ",WEIGHATTN='"
+ enumValue.WEIGHATTN + "',WEIGHTEL='" + enumValue.WEIGHTEL + "',WEIGHSIGN='" + enumValue.WEIGHSIGN + "',WEIGHDATE='" + enumValue.WEIGHDATE + "',TRUCKER='" + enumValue.TRUCKER + "',TRUCKFEE=" + enumValue.TRUCKFEE + ",FREESTORAGEDAY=" + enumValue.FREESTORAGEDAY + ",STORAGEDAY=" + enumValue.STORAGEDAY
+ ",STORAGEPRICE=" + enumValue.STORAGEPRICE + ",STORAGEFEE=" + enumValue.STORAGEFEE + ",FREECTNDAY=" + enumValue.FREECTNDAY + ",CTNDAY=" + enumValue.CTNDAY + ",CTNPRICE=" + enumValue.CTNPRICE + ",CTNFEE=" + enumValue.CTNFEE + ",TRUCKNO='" + enumValue.TRUCKNO + "',VGMCONNCOM='" + enumValue.VGMCONNCOM + "',VGMADDR='" + enumValue.VGMADDR + "',VGMEMAIL='" + enumValue.VGMEMAIL + "',ISTEMP='" + enumValue.ISTEMP + "',TIXIANGSHIJIAN='" + enumValue.TIXIANGSHIJIAN + "'"
+ " where BSNO='" + bsno + "' AND CTN_ID='" + enumValue.CTN_ID + "'");
db.ExecuteNonQuery(cmdUpdate, tran);
}
if (enumValue.CNTRNO != "")
{
var FeeCount = db.ExecuteScalar(tran, CommandType.Text, "SELECT COUNT(1) as FeeCount from OP_CTN where ISTEMP='1' AND CNTRNO='" + enumValue.CNTRNO + "' AND EXISTS (SELECT 1 FROM OP_SEAI B WHERE B.BSNO=OP_CTN.BSNO) ");
var IntFeeCount = Convert.ToInt16(FeeCount);
if (IntFeeCount != 0)
{
if (tempctnstr == "")
tempctnstr = enumValue.CNTRNO;
else
tempctnstr = tempctnstr + "," + enumValue.CNTRNO;
}
}
}
}
//
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功" + result.Message + tempctnstr;
return result;
}
public static DBResult Modify(List<MsWlPcHead_WFSDEdit> bodyList, MsWlPcHead_WFSDEdit modifydata, string companyid)
{
var result = new DBResult();
var head = ChMonthCloseDAL.GetData("", companyid);
string errstr = "";
int i = 0;
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var strSql = new StringBuilder();
strSql.Append("update tMsWlPcHead set BillNo=BillNo ");
if (modifydata.RtnDocStatus != "" && modifydata.RtnDocStatus != null)
strSql.Append(",RtnDocStatus=@RtnDocStatus");
if (modifydata.VoyVeg != "" && modifydata.VoyVeg != null)
strSql.Append(",VoyVeg=@VoyVeg");
if (modifydata.VoyVegSe != "" && modifydata.VoyVegSe != null)
strSql.Append(",VoyVegSe=@VoyVegSe");
if (modifydata.EtDateSe != "" && modifydata.EtDateSe != null)
strSql.Append(",EtDateSe=@EtDateSe");
if (modifydata.ISCANCEL != "" && modifydata.ISCANCEL != null)
strSql.Append(",ISFENGXIANG=@ISFENGXIANG");
if (modifydata.LinkTelSe != "" && modifydata.LinkTelSe != null)
strSql.Append(",LinkTelSe=@LinkTelSe");
strSql.Append(" where GID=@GID ");
var cmdupdate =
db.GetSqlStringCommand(strSql.ToString());
if (bodyList != null)
{
foreach (var enumValue in bodyList)
{
var opseae = GetHYRDData("GID='" + enumValue.gId + "'");
if (!string.IsNullOrEmpty(enumValue.ACCDATE))
{
if (Convert.ToDateTime(head.PERIOD + "-01") > Convert.ToDateTime(enumValue.ACCDATE + "-01"))
{
if (errstr == "")
errstr = enumValue.MblNo + "此票所在业务已月末结账";
else
errstr = errstr + "," + enumValue.MblNo + "此票所在业务已月末结账";
}
}
if (opseae.BSSTATUSREF == "锁定")
{
if (errstr == "")
errstr = enumValue.MblNo + "此票所在业务已业务锁定";
else
errstr = errstr + "," + enumValue.MblNo + "此票所在业务已业务锁定";
}
else
{
cmdupdate.Parameters.Clear();
if (modifydata.RtnDocStatus != "" && modifydata.RtnDocStatus != null)
db.AddInParameter(cmdupdate, "@RtnDocStatus", DbType.String, modifydata.RtnDocStatus);
if (modifydata.VoyVeg != "" && modifydata.VoyVeg != null)
db.AddInParameter(cmdupdate, "@VoyVeg", DbType.String, modifydata.VoyVeg);
if (modifydata.VoyVegSe != "" && modifydata.VoyVegSe != null)
db.AddInParameter(cmdupdate, "@VoyVegSe", DbType.String, modifydata.VoyVegSe);
if (modifydata.EtDateSe != "" && modifydata.EtDateSe != null)
db.AddInParameter(cmdupdate, "@EtDateSe", DbType.String, modifydata.EtDateSe);
if (modifydata.ISCANCEL != "" && modifydata.ISCANCEL != null)
{
if (modifydata.ISCANCEL == "1") {
db.AddInParameter(cmdupdate, "@ISFENGXIANG", DbType.Boolean,true);
}
if (modifydata.ISCANCEL == "0")
{
db.AddInParameter(cmdupdate, "@ISFENGXIANG", DbType.Boolean,false);
}
}
if (modifydata.LinkTelSe != "" && modifydata.LinkTelSe != null)
db.AddInParameter(cmdupdate, "@LinkTelSe", DbType.String, modifydata.LinkTelSe);
db.AddInParameter(cmdupdate, "@GID", DbType.String, enumValue.gId);
db.ExecuteNonQuery(cmdupdate, tran);
i = i + 1;
}
}
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
result.Success = false;
result.Message = "修改出现错误，请重试或联系系统管理员"+e.Message;
return result;
}
}
result.Success = true;
result.Message = "修改成功" + i.ToString() + "票";
if (errstr != "")
{
result.Message = result.Message + "(其中" + errstr + " 无法修改)";
}
return result;
}
#region 退舱
public static DBResult CancelShippingOrder(string bsno, string type, string op)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var BSNOLIST = new List<BSNOLB>();
if (type == "1")
{
var cmdUpdate = db.GetSqlStringCommand("update tMsWlPcHead set iscancel='" + type + "' where GID='" + bsno + "'");
db.ExecuteNonQuery(cmdUpdate, tran);
var cmdDelete = db.GetSqlStringCommand("insert into op_status(ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,INPUTTIME,STTYPE,COMPOP,INPUTBY) values(NEWID(),'" + bsno + "','退舱',1,'" + DateTime.Now.ToString() + "','" + DateTime.Now.ToString() + "','1','" + op + "','" + op + "')");
db.ExecuteNonQuery(cmdDelete, tran);
var NEWBSNO = new BSNOLB();
NEWBSNO.BSNO = bsno;
BSNOLIST.Add(NEWBSNO);
}
else
{
var cmdUpdate = db.GetSqlStringCommand("update tMsWlPcHead set iscancel='" + type + "' where GID='" + bsno + "'");
db.ExecuteNonQuery(cmdUpdate, tran);
var cmdDelete = db.GetSqlStringCommand("insert into op_status(ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,INPUTTIME,STTYPE,COMPOP,INPUTBY) values(NEWID(),'" + bsno + "','接受委托',1,'" + DateTime.Now.ToString() + "','" + DateTime.Now.ToString() + "','1','" + op + "','" + op + "')");
db.ExecuteNonQuery(cmdDelete, tran);
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "退舱出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "操作成功";
return result;
}
public static DBResult UpdateStatus(string bsno, string status, string op)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
// var cmdDelete = db.GetSqlStringCommand("insert into op_status(ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,INPUTTIME,STTYPE,COMPOP,INPUTBY) values(NEWID(),'" + bsno + "','"+ status + "',1,'" + DateTime.Now.ToString() + "','" + DateTime.Now.ToString() + "','1','" + op + "','" + op + "')");
// db.ExecuteNonQuery(cmdDelete, tran);
if (status == "完结")
{
var cmdDelete = db.GetSqlStringCommand("insert into op_status(ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,INPUTTIME,STTYPE,COMPOP,INPUTBY) SELECT " +
"NEWID(),GID,'" + status + "',1,'" + DateTime.Now.ToString() + "','" + DateTime.Now.ToString() + "','1','" + op + "','" + op + "' FROM tMsWlPcHead WHERE PGID='"+bsno+"' ");
db.ExecuteNonQuery(cmdDelete, tran);
}
else
{
var cmdDelete = db.GetSqlStringCommand("insert into op_status(ST_ID,BSNO,STATUS,ISCOMP,COMPTIME,INPUTTIME,STTYPE,COMPOP,INPUTBY) values(NEWID(),'" + bsno + "','" + status + "',1,'" + DateTime.Now.ToString() + "','" + DateTime.Now.ToString() + "','1','" + op + "','" + op + "')");
db.ExecuteNonQuery(cmdDelete, tran);
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "更新出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "操作成功";
return result;
}
public static DBResult deleteStatus(string bsno, string status)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdDelete = db.GetSqlStringCommand("delete from op_status where BSNO='" + bsno + "' AND STATUS='" + status + "'");
db.ExecuteNonQuery(cmdDelete, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "更新出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "操作成功";
return result;
}
#endregion
#region 明细表
static public List<MsWlPcFixed> GetBodyList(string strCondition, string userid, string username, string companyid)
{
var rangstr = GetRangDAStr(userid, username, companyid);
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,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,ENTEROPERATOR,ENTERDATE,FTruckNo");
strSql.Append(" from tMsWlPcFixed ");
if (strCondition.Trim() != String.Empty)
{
strSql.Append(" where " + strCondition);
}
return SetBodyData(strSql);
}
static public int GetRdCount(string strCondition)
{
var strSql = new StringBuilder();
strSql.Append("SELECT Count(Gid) AS CT from tMsWlPcHead (NOLOCK) ");
if (strCondition.Trim() != String.Empty)
{
strSql.Append(" where " + strCondition);
}
var ct = 0;
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
ct = Convert.ToInt16(reader["CT"]);
}
reader.Close();
}
return ct;
}
public static string GetRangDAStr(string userid, string username, string companyid)
{
string str = "";
string modustr = "";
modustr = "modOp_TruckFix";
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID,VSSQL ");
strSql.Append(" from VW_User_Authority ");
strSql.Append(" where [NAME]='" + modustr + "' and USERID='" + userid + "' and ISDELETE=0");
string visiblerange = "4";
string operaterange = "4";
string AUTHORITYID = "";
string VSSQL = "";
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"]);
AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]);
VSSQL = Convert.ToString(reader["VSSQL"]);
break;
}
reader.Close();
}
if (visiblerange == "4")
{
str = " ENTEROPERATOR='" + userid + "'";
}
else if (visiblerange == "3")
{
str = " ENTEROPERATOR='" + userid + "'";
}
else if (visiblerange == "2")
{
var rangeDa = new RangeDA();
var deptname = rangeDa.GetDEPTNAME(userid);
str = " ENTEROPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "') and ENTEROPERATOR in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')";
}
else if (visiblerange == "1")
{
str = " ENTEROPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "') ";
}
else if (visiblerange == "5")
{
var userstr = new StringBuilder();
userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (ENTEROPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "') ";
}
else
{
str = str + " or ENTEROPERATOR in (select USERID from user_company where COMPANYID='" + companyid + "')";
};
}
if (str != "")
str = str + ")";
reader.Close();
}
}
else if (visiblerange == "6")
{
var userstr = new StringBuilder();
userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (ENTEROPERATOR='" + Convert.ToString(reader["OPID"]) + "' ";
}
else
{
str = str + " or ENTEROPERATOR='" + Convert.ToString(reader["OPID"]) + "'";
};
}
if (str != "")
str = str + ")";
reader.Close();
}
}
else if (visiblerange == "0")
{
str = " 1=1 ";
}
VSSQL = VSSQL.Trim();
if (!string.IsNullOrEmpty(VSSQL))
{
if (!string.IsNullOrEmpty(str))
{
str = str + " and (" + VSSQL + ") ";
}
else
{
str = " (" + VSSQL + ") ";
}
}
return str;
}
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.FTruckNo = Convert.ToString(reader["FTruckNo"]);
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"]);
data.ENTEROPERATOR = Convert.ToString(reader["ENTEROPERATOR"]);
if (reader["ENTERDATE"] != DBNull.Value)
data.ENTERDATE = Convert.ToDateTime(reader["ENTERDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
#endregion
bodyList.Add(data);
}
reader.Close();
}
return bodyList;
}
public static DBResult SaveDetail(string billno, List<MsWlPcFixed> bodyList, string userid)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdInsert =
db.GetSqlStringCommand(
@"insert into tMsWlPcFixed (GID,BillNo,SerialNo,CreditDebit,FeeTypeCode,FeeTypeName,DistCount,Price,FsTotal,JsTotal,Remark,FeeStatus,FTruckNo)
values (@GID,@BillNo,@SerialNo,@CreditDebit,@FeeTypeCode,@FeeTypeName,@DistCount,@Price,@FsTotal,@JsTotal,@Remark,@FeeStatus,@FTruckNo) ");
var cmdUpdate =
db.GetSqlStringCommand(
@"update tMsWlPcFixed set SerialNo=@SerialNo,CreditDebit=@CreditDebit,FeeTypeCode=@FeeTypeCode,FeeTypeName=@FeeTypeName,DistCount=@DistCount,Price=@Price,FsTotal=@FsTotal
,JsTotal=@JsTotal,Remark=@Remark,FeeStatus=@FeeStatus,FTruckNo=@FTruckNo where GID=@GID");
if (bodyList != null)
{
foreach (var enumValue in bodyList)
{
if (enumValue.BillNo == "*")
{
cmdInsert.Parameters.Clear();
db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue.GId);
db.AddInParameter(cmdInsert, "@BillNo", DbType.String, billno);
db.AddInParameter(cmdInsert, "@SerialNo", DbType.String, enumValue.SerialNo);
db.AddInParameter(cmdInsert, "@CreditDebit", DbType.String, enumValue.CreditDebit);
db.AddInParameter(cmdInsert, "@FeeTypeCode", DbType.String, enumValue.FeeTypeCode);
db.AddInParameter(cmdInsert, "@FeeTypeName", DbType.String, enumValue.FeeTypeName);
db.AddInParameter(cmdInsert, "@DistCount", DbType.Decimal, enumValue.DistCount);
db.AddInParameter(cmdInsert, "@Price", DbType.Decimal, enumValue.Price);
db.AddInParameter(cmdInsert, "@FsTotal", DbType.Decimal, enumValue.FsTotal);
db.AddInParameter(cmdInsert, "@JsTotal", DbType.Decimal, enumValue.JsTotal);
db.AddInParameter(cmdInsert, "@Remark", DbType.String, enumValue.Remark);
db.AddInParameter(cmdInsert, "@FTruckNo", DbType.String, enumValue.FTruckNo);
db.AddInParameter(cmdInsert, "@FeeStatus", DbType.Decimal, enumValue.FeeStatus);
db.ExecuteNonQuery(cmdInsert, tran);
}
else
{
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GId);
db.AddInParameter(cmdUpdate, "@BillNo", DbType.String, billno);
db.AddInParameter(cmdUpdate, "@SerialNo", DbType.String, enumValue.SerialNo);
db.AddInParameter(cmdUpdate, "@CreditDebit", DbType.String, enumValue.CreditDebit);
db.AddInParameter(cmdUpdate, "@FeeTypeCode", DbType.String, enumValue.FeeTypeCode);
db.AddInParameter(cmdUpdate, "@FeeTypeName", DbType.String, enumValue.FeeTypeName);
db.AddInParameter(cmdUpdate, "@DistCount", DbType.Decimal, enumValue.DistCount);
db.AddInParameter(cmdUpdate, "@Price", DbType.Decimal, enumValue.Price);
db.AddInParameter(cmdUpdate, "@FsTotal", DbType.Decimal, enumValue.FsTotal);
db.AddInParameter(cmdUpdate, "@JsTotal", DbType.Decimal, enumValue.JsTotal);
db.AddInParameter(cmdUpdate, "@Remark", DbType.String, enumValue.Remark);
db.AddInParameter(cmdUpdate, "@FeeStatus", DbType.Decimal, enumValue.FeeStatus);
db.AddInParameter(cmdUpdate, "@FTruckNo", DbType.String, enumValue.FTruckNo);
db.ExecuteNonQuery(cmdUpdate, tran);
}
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static DBResult DeleteDetail(string GID)
{
var result = new DBResult();
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmd = db.GetSqlStringCommand("delete from tMsWlPcFixed where GID='" + GID + "'");
db.ExecuteNonQuery(cmd, tran);
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
#endregion
public static DBResult MakeFee(MsWlPcHead_WFSDEdit headData,string USERID)
{
var result = new DBResult();
//var currHead = GetWFSDData($" t.GID='{headData.gId}'");
var cdc = new Common.DB.CommonDataContext();
var taskList = cdc.op_task.Where(x => x.BSNO == headData.gId).ToList();
var task = new op_task_md();
if (taskList == null || taskList.Count == 0)
{
result.SetErrorInfo("没有找到对应的业务");
return result;
}
else {
task = taskList[0];
if (task.ISCOMPLETE == 1) {
result.SetErrorInfo("费用已经生成，无法重新生成");
return result;
}
}
var orderfeeList = cdc.ch_fee_order.Where(x => x.BSNO == headData.gId).ToList();
var 司机费用与结算对象关系 = cdc.tSysEnumValue.Where(x => x.EnumTypeID == 7002).ToList();
if (orderfeeList != null && orderfeeList.Count > 0) {
try
{
foreach (var orderfee in orderfeeList)
{
//MsChFeeDAL.MakeNewFee(USERID, headData.gId, "车辆费用", "其它", 2, orderfee.FEENAME, orderfee.AMOUNT, "单票", 1, "","","RMB","车辆成本");
var cust = "车辆费用";
var feename = orderfee.FEENAME;
var feegroup = "";
if (司机费用与结算对象关系.Exists(x => x.EnumValueID == orderfee.FEENAME)) {
var _rec = 司机费用与结算对象关系.First(x => x.EnumValueID == orderfee.FEENAME);
cust = _rec.EnumValueName_2;
feename = _rec.VerNo;
feegroup = _rec.EnumValueName;
}
var newfee = ChFeeHelper.getChFee(USERID, headData.gId, cust, 2, feename, orderfee.AMOUNT, "单次", 1, "", "", feegroup);
newfee.BXGID = "任务生成";
cdc.ch_fee.Add(newfee);
}
task.ISCOMPLETE = 1;
cdc.op_task.AddOrUpdate(task);
cdc.SaveChanges();
result.OK("费用生成成功");
}
catch (Exception e)
{
result.SetErrorInfo(e.Message);
return result;
}
}
var FuelList = cdc.t_Truck_FHApp_Fuel.Where(x => x.BSNO == headData.gId).ToList();
if (FuelList != null && FuelList.Count > 0)
{
try
{
var fueltypeEnum = cdc.tSysEnumValue.Where(x => x.EnumTypeID == 7001).ToList();
foreach (var Fuel in FuelList)
{
var custenum = fueltypeEnum.FirstOrDefault(x => x.EnumValueID == Fuel.JYTYPE);
var cust = custenum.EnumValueName_2;
var feename = custenum.VerNo;
//MsChFeeDAL.MakeNewFee(USERID, headData.gId, "车辆费用", "其它", 2, orderfee.FEENAME, orderfee.AMOUNT, "单票", 1, "");
var newfee = ChFeeHelper.getChFee(USERID, headData.gId, cust, 2, feename, (decimal)Fuel.JYAMOUNT, "单次", (decimal)Fuel.JYCOUNT, "", Fuel.JYTYPE+" "+Fuel.REMARK);
newfee.BXGID = "任务生成";
cdc.ch_fee.Add(newfee);
}
task.ISCOMPLETE = 1;
cdc.op_task.AddOrUpdate(task);
cdc.SaveChanges();
result.OK("费用生成成功");
}
catch (Exception e)
{
result.SetErrorInfo(e.Message);
return result;
}
}
return result;
}
public static DBResult UnMakeFee(MsWlPcHead_WFSDEdit headData)
{
var result = new DBResult();
var cdc = new Common.DB.CommonDataContext();
var taskList = cdc.op_task.Where(x => x.BSNO == headData.gId).ToList();
var task = new op_task_md();
if (taskList == null || taskList.Count == 0)
{
result.SetErrorInfo("没有找到对应的业务");
return result;
}
else
{
task = taskList[0];
if (task.ISCOMPLETE != 1)
{
result.SetErrorInfo("未生成费用，无法进行取消");
return result;
}
var feelist = cdc.ch_fee.Where(x => x.BSNO == headData.gId && x.BXGID=="任务生成" ).ToList();
if (feelist != null && feelist.Count > 0) {
var nodelFeeList = feelist.Where(x=>x.FEESTATUS != 1 && x.FEESTATUS != 6 && x.FEESTATUS != 8 && x.FEESTATUS != 9).ToList();
if (nodelFeeList != null && nodelFeeList.Count > 0)
{
result.SetErrorInfo("费用已进入审核流程，无法取消生成");
return result;
}
}
cdc.ch_fee.RemoveRange(feelist);
task.ISCOMPLETE = 0;
cdc.op_task.AddOrUpdate(task);
cdc.SaveChanges();
result.OK("费用取消成功");
}
return result;
}
public static DBResult SelectionCopy(string GIDListStr,string COMPANYID)
{
var result = new DBResult();
var GIDList = GIDListStr.Split(',').ToList();
var cdc = new CommonDataContext();
var pcheadList = cdc.tMsWlPcHead.Where(x => GIDList.Contains(x.GId)).ToList();
var newList = AutoMapperHelper.MapToList<tMsWlPcHead_md, tMsWlPcHead_md>(pcheadList);
foreach (var item in newList) {
item.GId = "PC"+ System.Guid.NewGuid().ToString();
item.PGId = item.GId;
var billnoset = MsSysBillNoSetDAL.GetData("OPLBNAME='陆运'", COMPANYID);
billnoset.BILLTYPE = "0111";
billnoset.RULEDATETYPE = "业务日期";
var bsdate = DateTime.Now.ToString("yyyy-MM-dd");
item.ACCDATE = DateTime.Now.ToString("yyyy-MM");
item.LrDate = Convert.ToDateTime( bsdate);
item.BillNo = MsSysBillNoSetDAL.GetBillNo(billnoset, bsdate, DateTime.Now.ToString("yyyy-MM")); //获取合同号
}
if (newList.Count > 0)
{
cdc.tMsWlPcHead.AddRange(newList);
cdc.SaveChanges();
result.OK("复制完成");
}
else {
result.SetErrorInfo("没有复制业务");
}
return result;
}
}
}
Reference in New Issue View Git Blame Copy Permalink