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 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 SetCtnBodyData(StringBuilder strSql) { var bodyList = new List(); 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 headList) { var isSucess = false; msg = ""; headList=new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); 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 headList) { var isSucess = false; msg = ""; headList = new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); 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; } } }