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.
1702 lines
72 KiB
C#
1702 lines
72 KiB
C#
|
|
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.MvcShipping.Models.MsOpBulk;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.EntityDA;
|
|
using DSWeb.Areas.CommMng.Models;
|
|
using HcUtility.Comm;
|
|
using System.Data.SqlClient;
|
|
using System.Web;
|
|
using System.Data.Common;
|
|
using DSWeb.TruckMng.Comm.Cookie;
|
|
using DSWeb.DataAccess;
|
|
|
|
namespace DSWeb.MvcShipping.DAL.MsOpBulkDAL
|
|
{
|
|
public class MsOpBulkDAL
|
|
{
|
|
#region Inquery DataList
|
|
|
|
static public List<MsOpBulk> GetDataList(string strCondition, string userid, string usercode, string companyid, string sort = null)
|
|
{
|
|
|
|
var rangstr = GetRangDAStr("index", userid, usercode, 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("BsNo,BsStatus,FeeStatus,OpStatus,BsDate,AccDate,OpDate,CustNo,ORDERNO");
|
|
strSql.Append(",CustomerName,TransType,ETD,LASTETD,ETA,PORTLOAD,PORTDISCHARGE");
|
|
strSql.Append(",INPUTBY,OP,SALE,BSSOURCE,BSSOURCEDETAIL,GOODCODE,GOODSNAME");
|
|
strSql.Append(",CORPID,SALEDEPT,PKGS,KGS,OVERKGS,OVERPKGS,CBM,REMARK,STLNAME,STLDATE");
|
|
strSql.Append(",(CASE BsStatus WHEN 1 THEN '已锁定' else '未锁定' end) as bsstatusref ");
|
|
strSql.Append(",(CASE FeeStatus WHEN 1 THEN '已锁定' else '未锁定' end) as feestatusref ");
|
|
strSql.Append(" from OP_BULK ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by BsNo");
|
|
|
|
}
|
|
|
|
return SetData(strSql);
|
|
}
|
|
|
|
static public string GetDataListStr(string strCondition, string userid, string usercode, string companyid, string sort = null)
|
|
{
|
|
|
|
var rangstr = GetRangDAStr("index", userid, usercode, 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("BsNo,BsStatus,FeeStatus,OpStatus,BsDate,AccDate,OpDate,CustNo,ORDERNO");
|
|
strSql.Append(",CustomerName,TransType,ETD,LASTETD,ETA,PORTLOAD,PORTDISCHARGE");
|
|
strSql.Append(",INPUTBY,OP,SALE,BSSOURCE,BSSOURCEDETAIL,GOODCODE,GOODSNAME");
|
|
strSql.Append(",CORPID,SALEDEPT,PKGS,KGS,OVERKGS,OVERPKGS,CBM,REMARK,STLNAME,STLDATE");
|
|
strSql.Append(",(CASE BsStatus WHEN 1 THEN '已锁定' else '未锁定' end) as bsstatusref ");
|
|
strSql.Append(",(CASE FeeStatus WHEN 1 THEN '已锁定' else '未锁定' end) as feestatusref ");
|
|
strSql.Append(" from OP_BULK ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by BsNo");
|
|
|
|
}
|
|
|
|
return strSql.ToString();
|
|
}
|
|
|
|
static public MsOpBulk GetData(string condition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BsNo,BsStatus,FeeStatus,OpStatus,BsDate,AccDate,OpDate,CustNo,ORDERNO");
|
|
strSql.Append(",CustomerName,TransType,ETD,LASTETD,ETA,PORTLOAD,PORTDISCHARGE");
|
|
strSql.Append(",INPUTBY,OP,SALE,BSSOURCE,BSSOURCEDETAIL,GOODCODE,GOODSNAME");
|
|
strSql.Append(",CORPID,SALEDEPT,PKGS,KGS,OVERKGS,OVERPKGS,CBM,REMARK,STLNAME,STLDATE");
|
|
strSql.Append(",(CASE BsStatus WHEN 1 THEN '已锁定' else '未锁定' end) as bsstatusref ");
|
|
strSql.Append(",(CASE FeeStatus WHEN 1 THEN '已锁定' else '未锁定' end) as feestatusref ");
|
|
strSql.Append(" from OP_BULK ");
|
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
{
|
|
strSql.Append(" where " + condition);
|
|
}
|
|
|
|
|
|
var list=SetData(strSql);
|
|
if (list.Count > 0)
|
|
return list[0];
|
|
|
|
return new MsOpBulk();
|
|
}
|
|
|
|
|
|
private static List<MsOpBulk> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsOpBulk>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsOpBulk data = new MsOpBulk();
|
|
#region Set DB data to Object
|
|
data.BSNO = Convert.ToString(reader["BSNO"]);
|
|
data.BSSTATUS = Convert.ToBoolean(reader["BSSTATUS"]);
|
|
data.FEESTATUS = Convert.ToBoolean(reader["FEESTATUS"]);
|
|
data.BSSTATUSREF = Convert.ToString(reader["BSSTATUSREF"]);
|
|
data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);
|
|
|
|
if (reader["BSDATE"] != DBNull.Value)
|
|
data.BSDATE = Convert.ToDateTime(reader["BSDATE"]);
|
|
data.ETD = Convert.ToString(reader["ETD"]);
|
|
data.LASTETD = Convert.ToString(reader["LASTETD"]);
|
|
data.ETA = Convert.ToString(reader["ETA"]);
|
|
data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
|
|
data.OPSTATUS = Convert.ToString(reader["OPSTATUS"]);
|
|
data.GOODCODE = Convert.ToString(reader["GOODCODE"]);
|
|
data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
|
|
if (reader["OpDate"] != DBNull.Value)
|
|
data.OPDATE = Convert.ToString(reader["OpDate"]);
|
|
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
|
|
data.ORDERNO = Convert.ToString(reader["ORDERNO"]);
|
|
data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
|
|
data.TRANSTYPE = Convert.ToString(reader["TRANSTYPE"]);
|
|
|
|
data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
|
|
data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]);
|
|
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
|
|
data.OP = Convert.ToString(reader["OP"]);
|
|
data.SALE = Convert.ToString(reader["SALE"]);
|
|
|
|
data.BSSOURCE = Convert.ToString(reader["BSSOURCE"]);
|
|
data.BSSOURCEDETAIL = Convert.ToString(reader["BSSOURCEDETAIL"]);
|
|
data.CORPID = Convert.ToString(reader["CORPID"]);
|
|
data.SALEDEPT = Convert.ToString(reader["SALEDEPT"]);
|
|
data.PKGS = Convert.ToInt32(reader["PKGS"]);
|
|
data.KGS = Convert.ToDecimal(reader["KGS"]);
|
|
if (reader["OVERKGS"] != DBNull.Value)
|
|
data.OVERKGS = Convert.ToDecimal(reader["OVERKGS"]);
|
|
if (reader["OVERPKGS"] != DBNull.Value)
|
|
data.OVERPKGS = Convert.ToDecimal(reader["OVERPKGS"]);
|
|
data.CBM = Convert.ToDecimal(reader["CBM"]);
|
|
data.REMARK = Convert.ToString(reader["REMARK"]);
|
|
data.STLNAME = Convert.ToString(reader["STLNAME"]);
|
|
data.STLDATE = Convert.ToString(reader["STLDATE"]);
|
|
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
#endregion
|
|
|
|
static public List<MsOpBulkDetail> GetBodyList(string strCondition,string sort)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BSNO,SerialNo,ACCSTATUS,ISTRAIN,TruckNo,DrvName,DrvCust,STATIONNO");
|
|
strSql.Append(",ExpDate,PKGS,KGS,NETKGS,TAREKGS,PKGKGS,POUNDNO,TRANSTOTAL,TRANSOT,Remark");
|
|
strSql.Append(" from op_Bulk_detail ");
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by SerialNo");
|
|
|
|
}
|
|
|
|
return SetBodyData(strSql);
|
|
}
|
|
|
|
private static List<MsOpBulkDetail> SetBodyData(StringBuilder strSql)
|
|
{
|
|
var bodyList = new List<MsOpBulkDetail>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsOpBulkDetail data = new MsOpBulkDetail();
|
|
#region Set DB data to Object
|
|
data.BSNO = Convert.ToString(reader["BSNO"]);
|
|
if (reader["SERIALNO"] != DBNull.Value)
|
|
data.SerialNo = Convert.ToDecimal(reader["SERIALNO"]);
|
|
if (reader["ACCSTATUS"] != DBNull.Value)
|
|
data.ACCSTATUS = Convert.ToBoolean(reader["ACCSTATUS"]);
|
|
data.ISTRAIN = Convert.ToString(reader["ISTRAIN"]);
|
|
data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
|
|
data.DRVNAME = Convert.ToString(reader["DRVNAME"]);
|
|
data.DRVCUST = Convert.ToString(reader["DRVCUST"]);
|
|
data.EXPDATE = Convert.ToString(reader["EXPDATE"]);
|
|
data.STATIONNO = Convert.ToString(reader["STATIONNO"]);
|
|
if (reader["PKGS"] != DBNull.Value)
|
|
data.PKGS = Convert.ToDecimal(reader["PKGS"]);
|
|
if (reader["KGS"] != DBNull.Value)
|
|
data.KGS = Convert.ToDecimal(reader["KGS"]);
|
|
if (reader["NETKGS"] != DBNull.Value)
|
|
data.NETKGS = Convert.ToDecimal(reader["NETKGS"]);
|
|
if (reader["TAREKGS"] != DBNull.Value)
|
|
data.TAREKGS = Convert.ToDecimal(reader["TAREKGS"]);
|
|
if (reader["PKGKGS"] != DBNull.Value)
|
|
data.PKGKGS = Convert.ToDecimal(reader["PKGKGS"]);
|
|
data.POUNDNO = Convert.ToString(reader["POUNDNO"]);
|
|
if (reader["TRANSTOTAL"] != DBNull.Value)
|
|
data.TRANSTOTAL = Convert.ToDecimal(reader["TRANSTOTAL"]);
|
|
if (reader["TRANSOT"] != DBNull.Value)
|
|
data.TRANSOT = Convert.ToDecimal(reader["TRANSOT"]);
|
|
data.REMARK = Convert.ToString(reader["Remark"]);
|
|
|
|
|
|
#endregion
|
|
|
|
bodyList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return bodyList;
|
|
}
|
|
|
|
|
|
|
|
static public List<MsOpBulkTrainDetail> GetBodyListTrain(string strCondition,string sort)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BSNO,SerialNo,ACCSTATUS,TRAINNO,TRAINTYPE,TRAINCUST,INVNO,STATIONNO,ARRIVALSTATION");
|
|
strSql.Append(",ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark");
|
|
strSql.Append(" from op_Bulk_railway_detail ");
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by SerialNo");
|
|
|
|
}
|
|
return SetBodyTrainData(strSql);
|
|
}
|
|
|
|
private static List<MsOpBulkTrainDetail> SetBodyTrainData(StringBuilder strSql)
|
|
{
|
|
var bodyList = new List<MsOpBulkTrainDetail>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsOpBulkTrainDetail data = new MsOpBulkTrainDetail();
|
|
#region Set DB data to Object
|
|
data.BSNO = Convert.ToString(reader["BSNO"]);
|
|
if (reader["SERIALNO"] != DBNull.Value)
|
|
data.SerialNo = Convert.ToDecimal(reader["SERIALNO"]);
|
|
if (reader["ACCSTATUS"] != DBNull.Value)
|
|
data.ACCSTATUS = Convert.ToBoolean(reader["ACCSTATUS"]);
|
|
data.TRAINNO = Convert.ToString(reader["TRAINNO"]);
|
|
data.TRAINTYPE = Convert.ToString(reader["TRAINTYPE"]);
|
|
data.TRAINCUST = Convert.ToString(reader["TRAINCUST"]);
|
|
data.INVNO = Convert.ToString(reader["INVNO"]);
|
|
data.STATIONNO = Convert.ToString(reader["STATIONNO"]);
|
|
data.ARRIVALSTATION = Convert.ToString(reader["ARRIVALSTATION"]);
|
|
data.EXPDATE = Convert.ToString(reader["EXPDATE"]);
|
|
if (reader["PKGS"] != DBNull.Value)
|
|
data.PKGS = Convert.ToDecimal(reader["PKGS"]);
|
|
if (reader["KGS"] != DBNull.Value)
|
|
data.KGS = Convert.ToDecimal(reader["KGS"]);
|
|
if (reader["TRANSTOTAL"] != DBNull.Value)
|
|
data.TRANSTOTAL = Convert.ToDecimal(reader["TRANSTOTAL"]);
|
|
if (reader["TRANSOT"] != DBNull.Value)
|
|
data.TRANSOT = Convert.ToDecimal(reader["TRANSOT"]);
|
|
data.REMARK = Convert.ToString(reader["Remark"]);
|
|
|
|
|
|
#endregion
|
|
|
|
bodyList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return bodyList;
|
|
}
|
|
|
|
|
|
|
|
static public MsOpBulkStationKc GetStationKc(string strCondition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("STATIONNO,SUM(PKGS) PKGS,SUM(KGS) KGS ");
|
|
strSql.Append(" from V_OP_BULK_KC ");
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
strSql.Append(" Group by STATIONNO ");
|
|
|
|
var list = SetStationKcData(strSql);
|
|
if (list.Count > 0)
|
|
return list[0];
|
|
|
|
return new MsOpBulkStationKc();
|
|
}
|
|
|
|
static public List<MsOpBulkStationKc> GetStationKcList(string strCondition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("STATIONNO,SUM(PKGS) PKGS,SUM(KGS) KGS ");
|
|
strSql.Append(" from V_OP_BULK_KC ");
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
strSql.Append(" Group by STATIONNO ");
|
|
|
|
return SetStationKcData(strSql);
|
|
|
|
}
|
|
|
|
private static List<MsOpBulkStationKc> SetStationKcData(StringBuilder strSql)
|
|
{
|
|
var bodyList = new List<MsOpBulkStationKc>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsOpBulkStationKc data = new MsOpBulkStationKc();
|
|
#region Set DB data to Object
|
|
data.STATION = Convert.ToString(reader["STATIONNO"]);
|
|
// data.GOODCODE = Convert.ToString(reader["GOODCODE"]);
|
|
data.PKGS = Convert.ToDecimal(reader["PKGS"]);
|
|
data.KGS = Convert.ToDecimal(reader["KGS"]);
|
|
|
|
#endregion
|
|
|
|
bodyList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return bodyList;
|
|
}
|
|
|
|
|
|
static public List<MsOpBulkStationKcDetail> GetStationKcDetail(string strCondition,string sort)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BSNO,BSTYPE,STATIONNO,CUSTNO,EXPDATE,PKGS,KGS ");
|
|
strSql.Append(" from V_OP_BULK_KC ");
|
|
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by EXPDATE");
|
|
|
|
}
|
|
|
|
return SetStationKcDetailData(strSql);
|
|
}
|
|
|
|
private static List<MsOpBulkStationKcDetail> SetStationKcDetailData(StringBuilder strSql)
|
|
{
|
|
var bodyList = new List<MsOpBulkStationKcDetail>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsOpBulkStationKcDetail data = new MsOpBulkStationKcDetail();
|
|
#region Set DB data to Object
|
|
data.BSNO = Convert.ToString(reader["BSNO"]);
|
|
data.BSTYPE = Convert.ToString(reader["BSTYPE"]);
|
|
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
|
|
data.EXPDATE = Convert.ToString(reader["EXPDATE"]);
|
|
data.STATIONNO = Convert.ToString(reader["STATIONNO"]);
|
|
data.PKGS = Convert.ToDecimal(reader["PKGS"]);
|
|
data.KGS = Convert.ToDecimal(reader["KGS"]);
|
|
|
|
#endregion
|
|
|
|
bodyList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return bodyList;
|
|
}
|
|
|
|
|
|
static public MsOpBulkKcModify GetKcModifyData(string condition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("GID,OP,STATIONNO,CUSTNO,BSDATE,PKGS,KGS,REMARK ");
|
|
strSql.Append(" from op_Bulk_station_kc_modify ");
|
|
|
|
if (!string.IsNullOrEmpty(condition))
|
|
{
|
|
strSql.Append(" where " + condition);
|
|
}
|
|
|
|
|
|
var list = SetKcModifyData(strSql);
|
|
if (list.Count > 0)
|
|
return list[0];
|
|
|
|
return new MsOpBulkKcModify();
|
|
}
|
|
|
|
|
|
private static List<MsOpBulkKcModify> SetKcModifyData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<MsOpBulkKcModify>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsOpBulkKcModify data = new MsOpBulkKcModify();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
if (reader["BSDATE"] != DBNull.Value)
|
|
data.BSDATE = Convert.ToString(reader["BSDATE"]);
|
|
data.OP = Convert.ToString(reader["OP"]);
|
|
data.STATIONNO = Convert.ToString(reader["STATIONNO"]);
|
|
data.CUSTNO = Convert.ToString(reader["CUSTNO"]);
|
|
data.PKGS = Convert.ToInt32(reader["PKGS"]);
|
|
data.KGS = Convert.ToDecimal(reader["KGS"]);
|
|
data.REMARK = Convert.ToString(reader["REMARK"]);
|
|
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
public static DBResult SaveDetail(string BsNo, List<MsOpBulkDetail> bodyList,bool acc)
|
|
{
|
|
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 op_Bulk_detail (BSNO, SerialNo,IsTrain,TruckNo,DrvName,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo)
|
|
values (@BSNO, @SerialNo,@IsTrain, @TruckNo,@DrvName,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) ");
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update op_Bulk_detail set TruckNo=@TruckNo,DrvName=@DrvName,ExpDate=@ExpDate,PKGS=@PKGS,KGS=@KGS,TRANSTOTAL=@TRANSTOTAL
|
|
,TRANSOT=@TRANSOT,Remark=@Remark,ACCSTATUS=@ACCSTATUS,StationNo=@StationNo where BSNO=@BSNO AND SerialNo=@SerialNo and IsTrain=@IsTrain ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
|
|
if (enumValue.BSNO == "*" || enumValue.BSNO == "")
|
|
{
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@BSNO", DbType.String, BsNo);
|
|
db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo);
|
|
db.AddInParameter(cmdInsert, "@ISTRAIN", DbType.String, enumValue.ISTRAIN);
|
|
db.AddInParameter(cmdInsert, "@TruckNo", DbType.String, enumValue.TRUCKNO);
|
|
db.AddInParameter(cmdInsert, "@DrvName", DbType.String, enumValue.DRVNAME);
|
|
if (enumValue.EXPDATE != "" && enumValue.EXPDATE !=null)
|
|
{
|
|
db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1,10));
|
|
}
|
|
else {
|
|
db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE);
|
|
|
|
}
|
|
|
|
db.AddInParameter(cmdInsert, "@PKGS", DbType.Decimal, enumValue.PKGS);
|
|
db.AddInParameter(cmdInsert, "@KGS", DbType.Decimal, enumValue.KGS);
|
|
db.AddInParameter(cmdInsert, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL);
|
|
db.AddInParameter(cmdInsert, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT);
|
|
if (acc) {
|
|
db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, "True");
|
|
} else {
|
|
|
|
db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS);
|
|
}
|
|
db.AddInParameter(cmdInsert, "@StationNo", DbType.String, enumValue.STATIONNO);
|
|
db.AddInParameter(cmdInsert, "@Remark", DbType.String, enumValue.REMARK);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
}
|
|
else
|
|
{
|
|
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo);
|
|
db.AddInParameter(cmdUpdate, "@SerialNo", DbType.Int32, enumValue.SerialNo);
|
|
db.AddInParameter(cmdUpdate, "@ISTRAIN", DbType.String, enumValue.ISTRAIN);
|
|
db.AddInParameter(cmdUpdate, "@TruckNo", DbType.String, enumValue.TRUCKNO);
|
|
db.AddInParameter(cmdUpdate, "@DrvName", DbType.String, enumValue.DRVNAME);
|
|
if (enumValue.EXPDATE != "" && enumValue.EXPDATE != null)
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1, 10));
|
|
}
|
|
else
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE);
|
|
|
|
}
|
|
db.AddInParameter(cmdUpdate, "@PKGS", DbType.Decimal, enumValue.PKGS);
|
|
db.AddInParameter(cmdUpdate, "@KGS", DbType.Decimal, enumValue.KGS);
|
|
db.AddInParameter(cmdUpdate, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL);
|
|
db.AddInParameter(cmdUpdate, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT);
|
|
if (acc)
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True");
|
|
}
|
|
else
|
|
{
|
|
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS);
|
|
}
|
|
db.AddInParameter(cmdUpdate, "@StationNo", DbType.String, enumValue.STATIONNO);
|
|
db.AddInParameter(cmdUpdate, "@Remark", DbType.String, enumValue.REMARK);
|
|
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 AuditDetail(string BsNo, bool acc)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update op_Bulk_detail set ACCSTATUS=@ACCSTATUS where BSNO=@BSNO ");
|
|
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo);
|
|
if (acc)
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True");
|
|
}
|
|
else
|
|
{
|
|
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "False");
|
|
}
|
|
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 DeleteDetail(List<MsOpBulkDetail> bodyList)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
var cmdInsert =
|
|
db.GetSqlStringCommand(
|
|
@"delete from op_Bulk_detail where BSNO=@BSNO AND SERIALNO=@SerialNo AND IsTrain=@IsTrain ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@BSNO", DbType.String,enumValue.BSNO);
|
|
db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo);
|
|
db.AddInParameter(cmdInsert, "@ISTRAIN", DbType.String, enumValue.ISTRAIN);
|
|
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 SaveTrainDetail(string BsNo, List<MsOpBulkTrainDetail> bodyList, bool acc)
|
|
{
|
|
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 op_Bulk_railway_detail (BSNO, SerialNo,TrainNo,TrainType,InvNo,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo)
|
|
values (@BSNO, @SerialNo, @TrainNo,@TrainType,@InvNo,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) ");
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update op_Bulk_railway_detail set TrainNo=@TrainNo,TrainType=@TrainType,InvNo=@InvNo,ExpDate=@ExpDate,PKGS=@PKGS,KGS=@KGS,TRANSTOTAL=@TRANSTOTAL
|
|
,TRANSOT=@TRANSOT,Remark=@Remark,ACCSTATUS=@ACCSTATUS,StationNo=@StationNo where BSNO=@BSNO AND SerialNo=@SerialNo ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
|
|
if (enumValue.BSNO == "*" || enumValue.BSNO == "")
|
|
{
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@BSNO", DbType.String, BsNo);
|
|
db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo);
|
|
db.AddInParameter(cmdInsert, "@TrainNo", DbType.String, enumValue.TRAINNO);
|
|
db.AddInParameter(cmdInsert, "@TrainType", DbType.String, enumValue.TRAINTYPE);
|
|
db.AddInParameter(cmdInsert, "@InvNo", DbType.String, enumValue.INVNO);
|
|
if (enumValue.EXPDATE != "" && enumValue.EXPDATE != null)
|
|
{
|
|
db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1, 10));
|
|
}
|
|
else
|
|
{
|
|
db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE);
|
|
|
|
}
|
|
db.AddInParameter(cmdInsert, "@PKGS", DbType.Decimal, enumValue.PKGS);
|
|
db.AddInParameter(cmdInsert, "@KGS", DbType.Decimal, enumValue.KGS);
|
|
db.AddInParameter(cmdInsert, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL);
|
|
db.AddInParameter(cmdInsert, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT);
|
|
if (acc)
|
|
{
|
|
db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, "True");
|
|
}
|
|
else
|
|
{
|
|
|
|
db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS);
|
|
}
|
|
db.AddInParameter(cmdInsert, "@StationNo", DbType.String, enumValue.STATIONNO);
|
|
db.AddInParameter(cmdInsert, "@Remark", DbType.String, enumValue.REMARK);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
}
|
|
else
|
|
{
|
|
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo);
|
|
db.AddInParameter(cmdUpdate, "@SerialNo", DbType.Int32, enumValue.SerialNo);
|
|
db.AddInParameter(cmdUpdate, "@TrainNo", DbType.String, enumValue.TRAINNO);
|
|
db.AddInParameter(cmdUpdate, "@TrainType", DbType.String, enumValue.TRAINTYPE);
|
|
db.AddInParameter(cmdUpdate, "@InvNo", DbType.String, enumValue.INVNO);
|
|
if (enumValue.EXPDATE != "" && enumValue.EXPDATE != null)
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1, 10));
|
|
}
|
|
else
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE);
|
|
|
|
}
|
|
db.AddInParameter(cmdUpdate, "@PKGS", DbType.Decimal, enumValue.PKGS);
|
|
db.AddInParameter(cmdUpdate, "@KGS", DbType.Decimal, enumValue.KGS);
|
|
db.AddInParameter(cmdUpdate, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL);
|
|
db.AddInParameter(cmdUpdate, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT);
|
|
if (acc)
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True");
|
|
}
|
|
else
|
|
{
|
|
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS);
|
|
}
|
|
db.AddInParameter(cmdUpdate, "@StationNo", DbType.String, enumValue.STATIONNO);
|
|
db.AddInParameter(cmdUpdate, "@Remark", DbType.String, enumValue.REMARK);
|
|
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 AuditTrainDetail(string BsNo, bool acc)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update op_Bulk_railway_detail set ACCSTATUS=@ACCSTATUS where BSNO=@BSNO ");
|
|
|
|
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo);
|
|
if (acc)
|
|
{
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True");
|
|
}
|
|
else
|
|
{
|
|
|
|
db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String,"False");
|
|
}
|
|
|
|
|
|
|
|
|
|
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 DeleteTrainDetail(List<MsOpBulkTrainDetail> bodyList)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
var cmdInsert =
|
|
db.GetSqlStringCommand(
|
|
@"delete from op_Bulk_railway_detail where BSNO=@BSNO AND SERIALNO=@SerialNo ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.BSNO);
|
|
db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo);
|
|
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 DeleteKcModifyDetail(MsOpBulkStationKcDetail bodyList)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
var cmdInsert =
|
|
db.GetSqlStringCommand(
|
|
@"delete from op_Bulk_station_kc_modify where GID=@BSNO ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
|
|
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@BSNO", DbType.String, bodyList.BSNO);
|
|
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 DeleteDetail(MsOpBulk headData)
|
|
{
|
|
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_Bulk_detail where BSNO='" + headData.BSNO + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
|
|
cmdDelete = db.GetSqlStringCommand("delete from op_Bulk_railway_detail where BSNO='" + headData.BSNO + "'");
|
|
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 bool ImportTruckDetailData(string bsno, HttpRequestBase request, DataTable table, out string msg, out List<MsOpBulkDetail> headList)
|
|
{
|
|
var isSucess = false;
|
|
msg = "";
|
|
headList = new List<MsOpBulkDetail>();
|
|
if (table == null) throw new ArgumentNullException("table");
|
|
|
|
var billNoList = new List<string>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (DbConnection connection = db.CreateConnection())
|
|
{
|
|
connection.Open();
|
|
DbTransaction idbTran = connection.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
var orgCode = CookieConfig.GetCookie_OrgCode(request);
|
|
var userCode = CookieConfig.GetCookie_UserCode(request);
|
|
var userName = CookieConfig.GetCookie_UserName(request);
|
|
int SerialNo = 0;
|
|
var objSerialNo = db.ExecuteScalar(CommandType.Text, "select MAX(SerialNo) AS SerialNo from op_Bulk_detail where ISTRAIN='1' AND BSNO='" + bsno + "'");
|
|
var StrSerialNo = Convert.ToString(objSerialNo);
|
|
if (string.IsNullOrEmpty(StrSerialNo))
|
|
SerialNo = 0;
|
|
else
|
|
SerialNo = Convert.ToInt16(StrSerialNo);
|
|
|
|
const string deleteSql = @"delete from op_Bulk_detail where bsno=@bsno and ISTRAIN=@ISTRAIN";
|
|
|
|
DbCommand cmddelete = db.GetSqlStringCommand(deleteSql);
|
|
db.AddInParameter(cmddelete, "BSNO", DbType.String, bsno);
|
|
db.AddInParameter(cmddelete, "ISTRAIN", DbType.String, '0');
|
|
|
|
db.ExecuteNonQuery(cmddelete, idbTran);
|
|
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
var custName = Convert.ToString(row["日期"]);
|
|
if (custName != "")
|
|
{
|
|
#region 托单数据生成
|
|
|
|
SerialNo = SerialNo + 1;
|
|
|
|
|
|
const string insertSql =@"insert into op_Bulk_detail (BSNO, SerialNo,IsTrain,TruckNo,DrvName,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo)
|
|
values (@BSNO, @SerialNo,@IsTrain, @TruckNo,@DrvName,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) ";
|
|
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(insertSql);
|
|
db.AddInParameter(cmd, "BSNO", DbType.String, bsno);
|
|
db.AddInParameter(cmd, "SerialNo", DbType.Int16, SerialNo);
|
|
db.AddInParameter(cmd, "IsTrain", DbType.String, '0');
|
|
db.AddInParameter(cmd, "TruckNo", DbType.String, "");
|
|
db.AddInParameter(cmd, "DrvName", DbType.String, "");
|
|
var custDate = Convert.ToString(row["日期"]).Trim();
|
|
if (!string.IsNullOrEmpty(custDate))
|
|
{
|
|
custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim();
|
|
}
|
|
db.AddInParameter(cmd, "ExpDate", DbType.String, custDate);
|
|
|
|
db.AddInParameter(cmd, "PKGS", DbType.Decimal, Convert.ToDecimal(row["包数"]));
|
|
db.AddInParameter(cmd, "KGS", DbType.Decimal, Convert.ToDecimal(row["吨数"]));
|
|
db.AddInParameter(cmd, "TRANSTOTAL", DbType.Decimal, 0);
|
|
db.AddInParameter(cmd, "TRANSOT", DbType.Decimal, 0);
|
|
db.AddInParameter(cmd, "Remark", DbType.String, "");
|
|
db.AddInParameter(cmd, "ACCSTATUS", DbType.Boolean, false);
|
|
db.AddInParameter(cmd, "StationNo", DbType.String, "");
|
|
|
|
|
|
db.ExecuteNonQuery(cmd, idbTran);
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
}
|
|
}
|
|
|
|
idbTran.Commit();
|
|
|
|
|
|
|
|
isSucess = true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
idbTran.Rollback();
|
|
isSucess = false;
|
|
msg = exception.Message;
|
|
}
|
|
}
|
|
|
|
return isSucess;
|
|
}
|
|
|
|
|
|
public static bool ImportDetailData(string bsno, HttpRequestBase request, DataTable table, out string msg, out List<MsOpBulkDetail> headList)
|
|
{
|
|
var isSucess = false;
|
|
msg = "";
|
|
headList = new List<MsOpBulkDetail>();
|
|
if (table == null) throw new ArgumentNullException("table");
|
|
|
|
var billNoList = new List<string>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (DbConnection connection = db.CreateConnection())
|
|
{
|
|
connection.Open();
|
|
DbTransaction idbTran = connection.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
var orgCode = CookieConfig.GetCookie_OrgCode(request);
|
|
var userCode = CookieConfig.GetCookie_UserCode(request);
|
|
var userName = CookieConfig.GetCookie_UserName(request);
|
|
int SerialNo = 0;
|
|
var objSerialNo = db.ExecuteScalar(CommandType.Text, "select MAX(SerialNo) AS SerialNo from op_Bulk_detail where ISTRAIN='0' AND BSNO='" + bsno + "'");
|
|
var StrSerialNo = Convert.ToString(objSerialNo);
|
|
if (string.IsNullOrEmpty(StrSerialNo))
|
|
SerialNo = 0;
|
|
else
|
|
SerialNo = Convert.ToInt16(StrSerialNo);
|
|
|
|
const string deleteSql = @"delete from op_Bulk_detail where bsno=@bsno and ISTRAIN=@ISTRAIN";
|
|
|
|
DbCommand cmddelete = db.GetSqlStringCommand(deleteSql);
|
|
db.AddInParameter(cmddelete, "BSNO", DbType.String, bsno);
|
|
db.AddInParameter(cmddelete, "ISTRAIN", DbType.String, '1');
|
|
|
|
db.ExecuteNonQuery(cmddelete, idbTran);
|
|
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
var custName = Convert.ToString(row["日期"]);
|
|
if (custName != "")
|
|
{
|
|
#region 托单数据生成
|
|
|
|
SerialNo = SerialNo + 1;
|
|
|
|
|
|
const string insertSql = @"insert into op_Bulk_detail (BSNO, SerialNo,IsTrain,TruckNo,DrvName,ExpDate,PKGS,KGS,NETKGS,TAREKGS,PKGKGS,POUNDNO,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo)
|
|
values (@BSNO, @SerialNo,@IsTrain, @TruckNo,@DrvName,@ExpDate,@PKGS,@KGS,@NETKGS,@TAREKGS,@PKGKGS,@POUNDNO,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) ";
|
|
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(insertSql);
|
|
db.AddInParameter(cmd, "BSNO", DbType.String, bsno);
|
|
db.AddInParameter(cmd, "SerialNo", DbType.Int16, Convert.ToInt16(row["序号"]));
|
|
db.AddInParameter(cmd, "IsTrain", DbType.String, '1');
|
|
db.AddInParameter(cmd, "TruckNo", DbType.String,Convert.ToString(row["车号"]));
|
|
db.AddInParameter(cmd, "DrvName", DbType.String, "");
|
|
var custDate = Convert.ToString(row["日期"]).Trim();
|
|
if (!string.IsNullOrEmpty(custDate))
|
|
{
|
|
custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim();
|
|
}
|
|
db.AddInParameter(cmd, "ExpDate", DbType.String, custDate);
|
|
|
|
db.AddInParameter(cmd, "PKGS", DbType.Decimal, Convert.ToDecimal(row["袋数"]));
|
|
db.AddInParameter(cmd, "KGS", DbType.Decimal, Convert.ToDecimal(row["毛重"]));
|
|
db.AddInParameter(cmd, "NETKGS", DbType.Decimal, Convert.ToDecimal(row["净重"]));
|
|
db.AddInParameter(cmd, "TAREKGS", DbType.Decimal, Convert.ToDecimal(row["皮重"]));
|
|
db.AddInParameter(cmd, "PKGKGS", DbType.Decimal, Convert.ToDecimal(row["包装物重"]));
|
|
db.AddInParameter(cmd, "TRANSTOTAL", DbType.Decimal, 0);
|
|
db.AddInParameter(cmd, "POUNDNO", DbType.String, Convert.ToString(row["磅单编号"]));
|
|
db.AddInParameter(cmd, "TRANSOT", DbType.Decimal, 0);
|
|
db.AddInParameter(cmd, "Remark", DbType.String, "");
|
|
db.AddInParameter(cmd, "ACCSTATUS", DbType.Boolean, false);
|
|
db.AddInParameter(cmd, "StationNo", DbType.String, "");
|
|
|
|
|
|
db.ExecuteNonQuery(cmd, idbTran);
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
}
|
|
}
|
|
|
|
idbTran.Commit();
|
|
|
|
|
|
|
|
isSucess = true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
idbTran.Rollback();
|
|
isSucess = false;
|
|
msg = exception.Message;
|
|
}
|
|
}
|
|
|
|
return isSucess;
|
|
}
|
|
|
|
public static bool ImportTrainDetailData(string bsno, HttpRequestBase request, DataTable table, out string msg, out List<MsOpBulkDetail> headList)
|
|
{
|
|
var isSucess = false;
|
|
msg = "";
|
|
headList = new List<MsOpBulkDetail>();
|
|
if (table == null) throw new ArgumentNullException("table");
|
|
|
|
var billNoList = new List<string>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (DbConnection connection = db.CreateConnection())
|
|
{
|
|
connection.Open();
|
|
DbTransaction idbTran = connection.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
int SerialNo = 0;
|
|
var objSerialNo = db.ExecuteScalar(CommandType.Text, "select MAX(SerialNo) AS SerialNo from op_Bulk_railway_detail where BSNO='" + bsno + "'");
|
|
var StrSerialNo = Convert.ToString(objSerialNo);
|
|
if (string.IsNullOrEmpty(StrSerialNo))
|
|
SerialNo = 0;
|
|
else
|
|
SerialNo = Convert.ToInt16(StrSerialNo);
|
|
|
|
const string deleteSql = @"delete from op_Bulk_railway_detail where bsno=@bsno";
|
|
|
|
DbCommand cmddelete = db.GetSqlStringCommand(deleteSql);
|
|
db.AddInParameter(cmddelete, "BSNO", DbType.String, bsno);
|
|
|
|
db.ExecuteNonQuery(cmddelete, idbTran);
|
|
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
var custName = Convert.ToString(row["日期"]);
|
|
if (custName != "")
|
|
{
|
|
#region 托单数据生成
|
|
|
|
SerialNo = SerialNo + 1;
|
|
|
|
|
|
const string insertSql = @"insert into op_Bulk_railway_detail (BSNO, SerialNo,TrainNo,TrainType,InvNo,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo,ARRIVALSTATION)
|
|
values (@BSNO, @SerialNo, @TrainNo,@TrainType,@InvNo,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo,@ARRIVALSTATION) ";
|
|
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(insertSql);
|
|
db.AddInParameter(cmd, "BSNO", DbType.String, bsno);
|
|
db.AddInParameter(cmd, "SerialNo", DbType.Int16, Convert.ToInt16(row["序号"]));
|
|
db.AddInParameter(cmd, "ARRIVALSTATION", DbType.String, Convert.ToString(row["到站"]).Trim());
|
|
db.AddInParameter(cmd, "TrainNo", DbType.String, Convert.ToString(row["车号"]).Trim());
|
|
db.AddInParameter(cmd, "TrainType", DbType.String, Convert.ToString(row["车型"]).Trim());
|
|
db.AddInParameter(cmd, "InvNo", DbType.String, Convert.ToString(row["票号"]).Trim());
|
|
var custDate = Convert.ToString(row["日期"]).Trim();
|
|
if (!string.IsNullOrEmpty(custDate))
|
|
{
|
|
custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim();
|
|
}
|
|
db.AddInParameter(cmd, "ExpDate", DbType.String, custDate);
|
|
|
|
db.AddInParameter(cmd, "PKGS", DbType.Decimal, Convert.ToDecimal(row["包数"]));
|
|
db.AddInParameter(cmd, "KGS", DbType.Decimal, Convert.ToDecimal(row["吨数"]));
|
|
db.AddInParameter(cmd, "TRANSTOTAL", DbType.Decimal, Convert.ToDecimal(row["金额"]));
|
|
db.AddInParameter(cmd, "TRANSOT", DbType.Decimal, 0);
|
|
db.AddInParameter(cmd, "Remark", DbType.String, "");
|
|
db.AddInParameter(cmd, "ACCSTATUS", DbType.Boolean, false);
|
|
db.AddInParameter(cmd, "StationNo", DbType.String, "");
|
|
|
|
db.ExecuteNonQuery(cmd, idbTran);
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
}
|
|
}
|
|
|
|
idbTran.Commit();
|
|
|
|
|
|
|
|
isSucess = true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
idbTran.Rollback();
|
|
isSucess = false;
|
|
msg = exception.Message;
|
|
}
|
|
}
|
|
|
|
return isSucess;
|
|
}
|
|
|
|
public static int p_update_OVERKGS(string bsno)
|
|
{
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
|
var cmd = db.GetStoredProcCommand("p_update_OVERKGS");
|
|
db.AddInParameter(cmd, "@con_no", DbType.String, bsno);
|
|
db.ExecuteNonQuery(cmd);
|
|
return 0;
|
|
}
|
|
|
|
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 [op_bulk] 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 "";
|
|
}
|
|
|
|
public static bool GetFeeCount(string bsno)
|
|
{
|
|
var isfee = false;
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("Select count(*) as count ");
|
|
strSql.Append(" from ch_fee ");
|
|
strSql.Append(" where bsno='" + bsno+"'");
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var evData = Convert.ToInt32(reader["count"]);
|
|
if (evData > 0) { isfee = true; };
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return isfee;
|
|
}
|
|
|
|
//判断单号是否重复
|
|
static public int GetRdCount(string strCondition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT Count(BSNO) AS CT from op_bulk");
|
|
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;
|
|
}
|
|
|
|
#region 委托业务费用加锁
|
|
/// <summary>
|
|
/// 委托业务费用加锁
|
|
/// </summary>
|
|
/// <param name="tempBSNO">委托业务编号</param>
|
|
/// <returns>值等于1表示加锁成功 值不等于1表示加锁失败</returns>
|
|
public static int LockFeeStatus(string tempBSNO)
|
|
{
|
|
int iResult = 0;
|
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
{
|
|
try
|
|
{
|
|
SqlParameter[] parms = new SqlParameter[] {
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100)
|
|
};
|
|
|
|
parms[0].Value = tempBSNO;
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, "UPDATE op_Bulk SET FEESTATUS = 1 WHERE BSNO = @bsno", parms);
|
|
|
|
sqlTran.Commit();
|
|
iResult = 1;//状态为1表示更新成功
|
|
}
|
|
catch (Exception execError)
|
|
{
|
|
iResult = -1;//有异常,更新失败
|
|
sqlTran.Rollback();
|
|
iResult = -2;//更新异常,事务已回滚成功
|
|
throw execError;
|
|
}
|
|
finally
|
|
{
|
|
SqlHelper.CloseConnection();
|
|
}
|
|
}
|
|
|
|
return iResult;
|
|
}
|
|
#endregion
|
|
|
|
#region 委托业务费用解锁
|
|
/// <summary>
|
|
/// 委托业务费用解锁
|
|
/// </summary>
|
|
/// <param name="tempBSNO">委托业务编号</param>
|
|
/// <returns>值等于1表示解锁成功 值不等于1表示解锁失败</returns>
|
|
public static int UnLockFeeStatus(string tempBSNO)
|
|
{
|
|
int iResult = 0;
|
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
{
|
|
try
|
|
{
|
|
SqlParameter[] parms = new SqlParameter[] {
|
|
new SqlParameter("@bsno",SqlDbType.VarChar,100)
|
|
};
|
|
|
|
parms[0].Value = tempBSNO;
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, "UPDATE op_bulk SET FEESTATUS =0 WHERE BSNO = @bsno", parms);
|
|
|
|
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 username, string companyid)
|
|
{
|
|
string str = "";
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append(" VISIBLERANGE,OPERATERANGE ");
|
|
strSql.Append(" from VW_User_Authority ");
|
|
strSql.Append(" where [NAME]='modOpBulkOpwt' and USERID='" + userid + "' and ISDELETE=0");
|
|
|
|
string visiblerange = "4";
|
|
string operaterange = "4";
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
visiblerange = Convert.ToString(reader["VISIBLERANGE"]);
|
|
operaterange = Convert.ToString(reader["OPERATERANGE"]);
|
|
break;
|
|
}
|
|
reader.Close();
|
|
}
|
|
if (visiblerange == "4")
|
|
{
|
|
str = " (OP='" + username + "' OR INPUTBY='"+username+"')";
|
|
}
|
|
else if (visiblerange == "3")
|
|
{
|
|
str = " (OP='" + username + "' OR INPUTBY='" + username + "')";
|
|
}
|
|
else if (visiblerange == "2")
|
|
{
|
|
var rangeDa = new RangeDA();
|
|
var deptname = rangeDa.GetDEPTNAME(userid);
|
|
str = " (OP 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 INPUTBY 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 = " (OP in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))"
|
|
+ " OR INPUTBY in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "')))";
|
|
}
|
|
else if (visiblerange == "0")
|
|
{
|
|
str = " 1=1 ";
|
|
}
|
|
|
|
|
|
return str;
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
}
|