DS7/DSWeb/TruckMng/DAL/MsWlBs_HT/MsWlBsDALPartial.cs

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_HT
{
    public partial class MsWlBs_HTDAL
    {
        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");
            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.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"]);

                    data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
                    data.SealNo = Convert.ToString(reader["SealNo"]);
                    #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 = MsWlBs_HTDAL.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;
        }

    }
}