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.
387 lines
21 KiB
C#
387 lines
21 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Text;
|
|
using System.Web;
|
|
using DSWeb.Areas.CommMng.DAL;
|
|
using DSWeb.Areas.TruckMng.Models.MsWlBs;
|
|
using DSWeb.TruckMng.Comm.Cookie;
|
|
using HcUtility.Core;
|
|
using HcUtility.Logging;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
|
|
namespace DSWeb.Areas.TruckMng.DAL.MsWlBs
|
|
{
|
|
public partial class MsWlBsDAL
|
|
{
|
|
static public List<MsWlBsCtn> GetCtnBodyList(string strCondition)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ");
|
|
strSql.Append("BillNo,SerialNo,ContainerType,");
|
|
strSql.Append("(select CodeAndName from vMsTruckCtn where CtnCode=tMsWlBsCtn.ContainerType) as ContainerType_Ref");
|
|
strSql.Append(",ContainerQty,ContainerNo,SealNo,PCQTY,GID");
|
|
//strSql.Append(",ContainerQty,ContainerNo,SealNo,PCQTY,GID,UNITPRICE,FREIGHT,ORUNITPRICE,ORFREIGHT");
|
|
strSql.Append(" from tMsWlBsCtn ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
return SetCtnBodyData(strSql);
|
|
}
|
|
|
|
private static List<MsWlBsCtn> SetCtnBodyData(StringBuilder strSql)
|
|
{
|
|
var bodyList = new List<MsWlBsCtn>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
MsWlBsCtn data = new MsWlBsCtn();
|
|
#region Set DB data to Object
|
|
data.BillNo = Convert.ToString(reader["BillNo"]);
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.SerialNo = Convert.ToDecimal(reader["SerialNo"]);
|
|
data.ContainerType = Convert.ToString(reader["ContainerType"]);
|
|
data.ContainerType_Ref = Convert.ToString(reader["ContainerType_Ref"]);
|
|
data.ContainerQty = Convert.ToDecimal(reader["ContainerQty"]);
|
|
if (reader["PCQTY"] != DBNull.Value)
|
|
data.PCQTY = Convert.ToDecimal(reader["PCQTY"]);
|
|
data.NPCQTY = data.ContainerQty - data.PCQTY;
|
|
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
|
|
data.SealNo = Convert.ToString(reader["SealNo"]);
|
|
/*
|
|
if (reader["UNITPRICE"] != DBNull.Value)
|
|
data.UNITPRICE = Convert.ToDecimal(reader["UNITPRICE"]);
|
|
if (reader["FREIGHT"] != DBNull.Value)
|
|
data.FREIGHT = Convert.ToDecimal(reader["FREIGHT"]);
|
|
if (reader["ORUNITPRICE"] != DBNull.Value)
|
|
data.ORUNITPRICE = Convert.ToDecimal(reader["ORUNITPRICE"]);
|
|
if (reader["ORFREIGHT"] != DBNull.Value)
|
|
data.ORFREIGHT = Convert.ToDecimal(reader["ORFREIGHT"]);
|
|
*/
|
|
#endregion
|
|
|
|
bodyList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return bodyList;
|
|
}
|
|
|
|
private static string GetBsType(string BsTypeName)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("Select top 1 EnumValueID,EnumValueName ");
|
|
strSql.Append(" from tSysEnumValue ");
|
|
strSql.Append(" where EnumTypeID=99025 and EnumValueName='" + BsTypeName+ "'");
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
var evList = "";
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
evList = Convert.ToString(reader["EnumValueID"]);
|
|
}
|
|
reader.Close();
|
|
}
|
|
|
|
return evList;
|
|
}
|
|
|
|
public static bool ImportExcelData(HttpRequestBase request, DataTable table, out string msg, out List<MsWlBsHead> headList)
|
|
{
|
|
var isSucess = false;
|
|
msg = "";
|
|
headList=new List<MsWlBsHead>();
|
|
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);
|
|
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
var custName = Convert.ToString(row["委托单位"]);
|
|
if (custName!=""){
|
|
#region 托单数据生成
|
|
var billNo = PubSysDAL.GetBillNo("0112");
|
|
billNoList.Add(billNo);
|
|
var gid = "TMSBS" + Guid.NewGuid().ToString("N").ToUpper();
|
|
|
|
|
|
var objCustCode = db.ExecuteScalar(CommandType.Text, "select CustCode from vMsTruckClient where CustName='" + custName +"'");
|
|
var custCode = Convert.ToString(objCustCode);
|
|
if (string.IsNullOrEmpty(custCode))
|
|
custCode = "*";
|
|
|
|
const string insertSql = @"insert into tMsWlBsHead (BillNo,GId,OrgCode,UserCode,UserName,LrDate,CustDate,CustCode,CustName,VoyVeg,MblNo,
|
|
DetiNation,DstArea,DetiLinkMan,DetiLinkTel,Remark,GoodsName,BsType)
|
|
values(@BillNo,@Gid,@OrgCode,@UserCode,@UserName,@LrDate,@CustDate,@CustCode,@CustName,@VoyVeg,@MblNo,
|
|
@DetiNation,@DstArea,@DetiLinkMan,@DetiLinkTel,@Remark,@GoodsName,@BsType)";
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(insertSql);
|
|
db.AddInParameter(cmd, "BillNo", DbType.String, billNo);
|
|
db.AddInParameter(cmd, "GId", DbType.String, gid);
|
|
|
|
db.AddInParameter(cmd, "OrgCode", DbType.String, orgCode);
|
|
db.AddInParameter(cmd, "UserCode", DbType.String, userCode);
|
|
db.AddInParameter(cmd, "UserName", DbType.String, userName);
|
|
db.AddInParameter(cmd, "LrDate", DbType.Date, DateTime.Now);
|
|
|
|
var custDate = Convert.ToString(row["委托日期"]).Trim();
|
|
if (!string.IsNullOrEmpty(custDate))
|
|
{
|
|
custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim();
|
|
}
|
|
db.AddInParameter(cmd, "CustDate", DbType.String,custDate);
|
|
db.AddInParameter(cmd, "CustCode", DbType.String, custCode);
|
|
db.AddInParameter(cmd, "CustName", DbType.String, custName);
|
|
db.AddInParameter(cmd, "VoyVeg", DbType.String, Convert.ToString(row["船名/航次"]));
|
|
db.AddInParameter(cmd, "MblNo", DbType.String, Convert.ToString(row["运单号"]));
|
|
db.AddInParameter(cmd, "DetiNation", DbType.String, Convert.ToString(row["送货地"]));//工厂地址
|
|
db.AddInParameter(cmd, "DstArea", DbType.String, Convert.ToString(row["目的地"]));//目的地]
|
|
db.AddInParameter(cmd, "DetiLinkMan", DbType.String, Convert.ToString(row["联系人"]));//工厂联系人
|
|
db.AddInParameter(cmd, "DetiLinkTel", DbType.String, Convert.ToString(row["电话"]));//工厂联系电话
|
|
db.AddInParameter(cmd, "Remark", DbType.String, Convert.ToString(row["备注"]));//备注
|
|
db.AddInParameter(cmd, "GoodsName", DbType.String, Convert.ToString(row["货名"]));//备注
|
|
db.AddInParameter(cmd, "BsType", DbType.String,GetBsType(Convert.ToString(row["类型"])));//备注
|
|
|
|
db.ExecuteNonQuery(cmd, idbTran);
|
|
|
|
#endregion
|
|
|
|
#region 生成集装箱
|
|
var containerSeals = Convert.ToString(row["箱号/封号"]).Trim();
|
|
var containerList = containerSeals.Split(' ');
|
|
// var containerList = containerSeals.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
|
|
|
|
var ctnSerialNo = 1;
|
|
var containerType = Convert.ToString(row["箱型"]);
|
|
foreach (var container in containerList)
|
|
{
|
|
var convalue = container.Split('/');
|
|
const string insertCtnSql = @"insert into tMsWlBsCtn (BillNo,SerialNo,ContainerType,ContainerQty,ContainerNo,SealNo)
|
|
values(@BillNo,@SerialNo,@ContainerType,@ContainerQty,@ContainerNo,@SealNo)";
|
|
DbCommand cmdCtn = db.GetSqlStringCommand(insertCtnSql);
|
|
db.AddInParameter(cmdCtn, "BillNo", DbType.String, gid);
|
|
db.AddInParameter(cmdCtn, "SerialNo", DbType.Decimal, ctnSerialNo);
|
|
db.AddInParameter(cmdCtn, "ContainerType", DbType.String, containerType);
|
|
db.AddInParameter(cmdCtn, "ContainerQty", DbType.Decimal, 1);
|
|
db.AddInParameter(cmdCtn, "ContainerNo", DbType.String, convalue[0]);
|
|
db.AddInParameter(cmdCtn, "SealNo", DbType.String, convalue[1]);
|
|
db.ExecuteNonQuery(cmdCtn, idbTran);
|
|
|
|
ctnSerialNo = ctnSerialNo + 1;
|
|
}
|
|
#endregion
|
|
|
|
|
|
// var containerCount = containerList.Length;
|
|
//var price = Convert.ToDecimal(Convert.ToString(row["单柜价格"]).Substring(1));
|
|
|
|
/*
|
|
var price = Convert.ToDecimal(row["单柜价格"]);
|
|
var feeName = "拖车费";
|
|
const string insertFeeSql = @"insert into ch_fee (GId,BsNo,FeeStatus,FeeType,FeeName,CustomerName,Unit,UnitPrice,Quantity,Amount,Currency,ExChangerate,Remark,
|
|
Commissionrate,Settlement,Invoice,OrderAmount,OrderInvoice,EnteroPerator,AuditStatus)
|
|
values(@GId,@BsNo,@FeeStatus,@FeeType,@FeeName,@CustomerName,@Unit,@UnitPrice,@Quantity,@Amount,@Currency,@ExChangerate,@Remark,
|
|
@Commissionrate,@Settlement,@Invoice,@OrderAmount,@OrderInvoice,@EnteroPerator,@AuditStatus)";
|
|
DbCommand cmdFee = db.GetSqlStringCommand(insertFeeSql);
|
|
db.AddInParameter(cmdFee, "GId", DbType.String, Guid.NewGuid().ToString());
|
|
db.AddInParameter(cmdFee, "BsNo", DbType.String, gid);
|
|
db.AddInParameter(cmdFee, "FeeStatus", DbType.Int32, 1);
|
|
db.AddInParameter(cmdFee, "FeeType", DbType.Int32, 1);
|
|
db.AddInParameter(cmdFee, "FeeName", DbType.String, "拖车费");
|
|
db.AddInParameter(cmdFee, "CustomerName", DbType.String, custName);
|
|
db.AddInParameter(cmdFee, "Unit", DbType.String, "");
|
|
db.AddInParameter(cmdFee, "UnitPrice", DbType.Decimal, price);
|
|
db.AddInParameter(cmdFee, "Quantity", DbType.Decimal, containerCount);
|
|
db.AddInParameter(cmdFee, "Amount", DbType.Decimal, price*containerCount);
|
|
db.AddInParameter(cmdFee, "Currency", DbType.String, "RMB");
|
|
db.AddInParameter(cmdFee, "ExChangerate", DbType.Decimal, 1);
|
|
db.AddInParameter(cmdFee, "Remark", DbType.String, "");
|
|
|
|
db.AddInParameter(cmdFee, "Commissionrate", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee, "Settlement", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee, "Invoice", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee, "OrderAmount", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee, "OrderInvoice", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee, "EnteroPerator", DbType.String, CookieConfig.GetCookie_UserId(request));
|
|
db.AddInParameter(cmdFee, "AuditStatus", DbType.String, "0");
|
|
|
|
db.ExecuteNonQuery(cmdFee, idbTran);
|
|
|
|
|
|
var price2 = Convert.ToDecimal(row["单柜THC"]);
|
|
var feeName2 = "THC";
|
|
const string insertFeeSql2 = @"insert into ch_fee (GId,BsNo,FeeStatus,FeeType,FeeName,CustomerName,Unit,UnitPrice,Quantity,Amount,Currency,ExChangerate,Remark,
|
|
Commissionrate,Settlement,Invoice,OrderAmount,OrderInvoice,EnteroPerator,AuditStatus)
|
|
values(@GId,@BsNo,@FeeStatus,@FeeType,@FeeName,@CustomerName,@Unit,@UnitPrice,@Quantity,@Amount,@Currency,@ExChangerate,@Remark,
|
|
@Commissionrate,@Settlement,@Invoice,@OrderAmount,@OrderInvoice,@EnteroPerator,@AuditStatus)";
|
|
DbCommand cmdFee2 = db.GetSqlStringCommand(insertFeeSql2);
|
|
db.AddInParameter(cmdFee2, "GId", DbType.String, Guid.NewGuid().ToString());
|
|
db.AddInParameter(cmdFee2, "BsNo", DbType.String, gid);
|
|
db.AddInParameter(cmdFee2, "FeeStatus", DbType.Int32, 1);
|
|
db.AddInParameter(cmdFee2, "FeeType", DbType.Int32, 1);
|
|
db.AddInParameter(cmdFee2, "FeeName", DbType.String, "THC");
|
|
db.AddInParameter(cmdFee2, "CustomerName", DbType.String, custName);
|
|
db.AddInParameter(cmdFee2, "Unit", DbType.String, "");
|
|
db.AddInParameter(cmdFee2, "UnitPrice", DbType.Decimal, price2);
|
|
db.AddInParameter(cmdFee2, "Quantity", DbType.Decimal, containerCount);
|
|
db.AddInParameter(cmdFee2, "Amount", DbType.Decimal, price2 * containerCount);
|
|
db.AddInParameter(cmdFee2, "Currency", DbType.String, "RMB");
|
|
db.AddInParameter(cmdFee2, "ExChangerate", DbType.Decimal, 1);
|
|
db.AddInParameter(cmdFee2, "Remark", DbType.String, "");
|
|
|
|
db.AddInParameter(cmdFee2, "Commissionrate", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee2, "Settlement", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee2, "Invoice", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee2, "OrderAmount", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee2, "OrderInvoice", DbType.Decimal, 0);
|
|
db.AddInParameter(cmdFee2, "EnteroPerator", DbType.String, CookieConfig.GetCookie_UserId(request));
|
|
db.AddInParameter(cmdFee2, "AuditStatus", DbType.String, "0");
|
|
|
|
db.ExecuteNonQuery(cmdFee2, idbTran);
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
#region 调用托单的保存后处理过程
|
|
DbCommand storedProcCommand = db.GetStoredProcCommand("sMsSysAfterSave");
|
|
|
|
db.AddInParameter(storedProcCommand, "ps_BillNo", DbType.String, gid);
|
|
db.AddInParameter(storedProcCommand, "ps_YwType", DbType.String, "0112");
|
|
|
|
db.AddInParameter(storedProcCommand, "ps_UserCode", DbType.String, userCode);
|
|
db.AddInParameter(storedProcCommand, "ps_UserName", DbType.String, userName);
|
|
db.AddOutParameter(storedProcCommand, "pi_Result", DbType.Int32, 0x20);
|
|
db.AddOutParameter(storedProcCommand, "ps_Message", DbType.String, 0x7d0);
|
|
db.ExecuteNonQuery(storedProcCommand, idbTran);
|
|
|
|
var success = Convert.ToInt32(db.GetParameterValue(storedProcCommand, "pi_Result")) == 1;
|
|
var message = Convert.ToString(db.GetParameterValue(storedProcCommand, "ps_Message"));
|
|
if (!success)
|
|
{
|
|
throw new Exception(message);
|
|
}
|
|
#endregion
|
|
*/
|
|
}
|
|
}
|
|
|
|
idbTran.Commit();
|
|
|
|
#region 获取新生成的托单费用
|
|
|
|
foreach (var billNo in billNoList)
|
|
{
|
|
var head = MsWlBsDAL.GetHeadDataByBillNo(billNo,"", CookieConfig.GetCookie_UserCode(request), CookieConfig.GetCookie_OrgCode(request));
|
|
headList.Add(head);
|
|
}
|
|
|
|
|
|
#endregion
|
|
isSucess = true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
idbTran.Rollback();
|
|
isSucess = false;
|
|
msg = exception.Message;
|
|
}
|
|
}
|
|
|
|
return isSucess;
|
|
}
|
|
|
|
|
|
public static bool ImportExcelUpdateData(HttpRequestBase request, DataTable table, out string msg, out List<MsWlBsHead> headList)
|
|
{
|
|
var isSucess = false;
|
|
msg = "";
|
|
headList = new List<MsWlBsHead>();
|
|
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);
|
|
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
|
|
#region 托单数据生成
|
|
const string insertSql = @"update tMsWlBsHead set VoyVegSe=@VoyVegSe,EtDate=@EtDate,EndPortDate=@EndPortDate where MblNo=@MblNo";
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(insertSql);
|
|
db.AddInParameter(cmd, "VoyVegSe", DbType.String, Convert.ToString(row["二程船名航次"]));
|
|
|
|
var EtDate = Convert.ToString(row["到港日期"]).Trim();
|
|
if (!string.IsNullOrEmpty(EtDate))
|
|
{
|
|
EtDate = Convert.ToDateTime(EtDate).ToString("yyyy-MM-dd").Trim();
|
|
}
|
|
db.AddInParameter(cmd, "EtDate", DbType.String, EtDate);
|
|
|
|
var EndPortDate = Convert.ToString(row["截港日期"]).Trim();
|
|
if (!string.IsNullOrEmpty(EndPortDate))
|
|
{
|
|
EndPortDate = Convert.ToDateTime(EndPortDate).ToString("yyyy-MM-dd").Trim();
|
|
}
|
|
db.AddInParameter(cmd, "EndPortDate", DbType.String, EndPortDate);
|
|
|
|
db.AddInParameter(cmd, "MblNo", DbType.String, Convert.ToString(row["运单号"]));
|
|
|
|
|
|
db.ExecuteNonQuery(cmd, idbTran);
|
|
|
|
#endregion
|
|
|
|
}
|
|
|
|
idbTran.Commit();
|
|
|
|
#region 获取新生成的托单费用
|
|
|
|
|
|
#endregion
|
|
isSucess = true;
|
|
}
|
|
catch (Exception exception)
|
|
{
|
|
idbTran.Rollback();
|
|
isSucess = false;
|
|
msg = exception.Message;
|
|
}
|
|
}
|
|
|
|
return isSucess;
|
|
}
|
|
|
|
}
|
|
} |