using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.MsOpCtnStatus; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.EntityDA; using HcUtility.Comm; using System.Web; using System.Text.RegularExpressions; using System.Data.Common; namespace DSWeb.Areas.MvcShipping.DAL { public class MsOpCtnStatusDAL { #region 集装箱资料 static public List GetCtnList(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("[cntrid],[cntrno],selltime,[cntrowner],[cntype],[cnsource],[buytime],[rent_begintime],[rent_endtime]"); strSql.Append(",[status],[position],[positionName],[REMARK],[vessel],[Voyage],beginpostion"); strSql.Append(",(case when status=1 then '可用' when status=2 then '已用' when status=3 then '维修' when status=4 then '停用' when status=5 then '退租' when status=6 then '灭失' when status=7 then '卖出' end) statusref"); strSql.Append(",(select port from code_disport where portid=container_info.position) positioncname"); strSql.Append(",(select port from code_disport where portid=container_info.beginpostion) beginpostionref"); strSql.Append(" from container_info "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } strSql.Append(" order by cntrid "); return SetData(strSql); } static public MsCtn GetData(string condition) { var list = GetCtnList(condition); if (list.Count > 0) return list[0]; return new MsCtn(); } private static List SetData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsCtn data = new MsCtn(); #region Set DB data to Object data.cntrid = Convert.ToInt16(reader["cntrid"]); data.cntrno = Convert.ToString(reader["cntrno"]); data.cntrowner = Convert.ToString(reader["cntrowner"]); data.cntype = Convert.ToString(reader["cntype"]); data.cnsource = Convert.ToString(reader["cnsource"]); if (reader["buytime"] != DBNull.Value) { data.buytime = Convert.ToDateTime(reader["buytime"]).ToString("yyyy-MM-dd"); } if (reader["rent_begintime"] != DBNull.Value) { data.rent_begintime = Convert.ToDateTime(reader["rent_begintime"]).ToString("yyyy-MM-dd"); } if (reader["rent_endtime"] != DBNull.Value) { data.rent_endtime = Convert.ToDateTime(reader["rent_endtime"]).ToString("yyyy-MM-dd"); } if (reader["selltime"] != DBNull.Value) { data.selltime = Convert.ToDateTime(reader["selltime"]).ToString("yyyy-MM-dd"); } if (reader["status"] != DBNull.Value) data.status = Convert.ToInt16(reader["status"]); data.statusref = Convert.ToString(reader["statusref"]); data.position = Convert.ToString(reader["position"]); data.positioncname = Convert.ToString(reader["positioncname"]); data.positionname = Convert.ToString(reader["positionName"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.vessel = Convert.ToString(reader["vessel"]); data.voyage = Convert.ToString(reader["Voyage"]); data.beginpostion = Convert.ToString(reader["beginpostion"]); data.beginpostionref = Convert.ToString(reader["beginpostionref"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 业务状态保存，删除 public static DBResult SaveCtn(MsCtn Ctn) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var ctnnum = GetRdCtn(Ctn.cntrno); if (ctnnum== 0) { var cmdInsert = db.GetSqlStringCommand( @"insert into container_info (cntrno,cntrowner,cntype,cnsource,buytime,rent_begintime,rent_endtime,status,position,vessel,voyage,positionName,REMARK,beginpostion) values (@cntrno,@cntrowner,@cntype,@cnsource,@buytime,@rent_begintime,@rent_endtime,@status,@position,@vessel,@voyage,@positionName,@REMARK,@beginpostion) "); cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@cntrno", DbType.String, Ctn.cntrno); db.AddInParameter(cmdInsert, "@cntrowner", DbType.String, Ctn.cntrowner); db.AddInParameter(cmdInsert, "@cntype", DbType.String, Ctn.cntype); db.AddInParameter(cmdInsert, "@cnsource", DbType.String, Ctn.cnsource); db.AddInParameter(cmdInsert, "@buytime", DbType.String, Ctn.buytime); db.AddInParameter(cmdInsert, "@rent_begintime", DbType.String, Ctn.rent_begintime); db.AddInParameter(cmdInsert, "@rent_endtime", DbType.String, Ctn.rent_endtime); db.AddInParameter(cmdInsert, "@status", DbType.Int16, Ctn.status); db.AddInParameter(cmdInsert, "@position", DbType.String, Ctn.position); db.AddInParameter(cmdInsert, "@vessel", DbType.String, Ctn.vessel); db.AddInParameter(cmdInsert, "@voyage", DbType.String, Ctn.voyage); db.AddInParameter(cmdInsert, "@positionName", DbType.String, Ctn.positionname); db.AddInParameter(cmdInsert, "@REMARK", DbType.String, Ctn.REMARK); db.AddInParameter(cmdInsert, "@beginpostion", DbType.String, Ctn.beginpostion); db.ExecuteNonQuery(cmdInsert, tran); } else { var cmdUpdate = db.GetSqlStringCommand( @"update container_info set cntrno=@cntrno,cntrowner=@cntrowner,cntype=@cntype,cnsource=@cnsource, buytime=@buytime,rent_begintime=@rent_begintime,rent_endtime=@rent_endtime,status=@status,position=@position, vessel=@vessel,voyage=@voyage,positionName=@positionName,REMARK=@REMARK,beginpostion=@beginpostion where cntrid=@cntrid"); cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@cntrid", DbType.String, Ctn.cntrid); db.AddInParameter(cmdUpdate, "@cntrno", DbType.String, Ctn.cntrno); db.AddInParameter(cmdUpdate, "@cntrowner", DbType.String, Ctn.cntrowner); db.AddInParameter(cmdUpdate, "@cntype", DbType.String, Ctn.cntype); db.AddInParameter(cmdUpdate, "@cnsource", DbType.String, Ctn.cnsource); db.AddInParameter(cmdUpdate, "@buytime", DbType.String, Ctn.buytime); db.AddInParameter(cmdUpdate, "@rent_begintime", DbType.String, Ctn.rent_begintime); db.AddInParameter(cmdUpdate, "@rent_endtime", DbType.String, Ctn.rent_endtime); db.AddInParameter(cmdUpdate, "@status", DbType.Int16, Ctn.status); db.AddInParameter(cmdUpdate, "@position", DbType.String, Ctn.position); db.AddInParameter(cmdUpdate, "@vessel", DbType.String, Ctn.vessel); db.AddInParameter(cmdUpdate, "@voyage", DbType.String, Ctn.voyage); db.AddInParameter(cmdUpdate, "@positionName", DbType.String, Ctn.positionname); db.AddInParameter(cmdUpdate, "@REMARK", DbType.String, Ctn.REMARK); db.AddInParameter(cmdUpdate, "@beginpostion", DbType.String, Ctn.beginpostion); 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; } static public int GetRdCtn(string CtnNo) { var strSql = new StringBuilder(); strSql.Append("SELECT Count(cntrno) AS CT from container_info where cntrno='"+CtnNo+"'"); var ct = 0; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ct = Convert.ToInt16(reader["CT"]); } reader.Close(); } return ct; } public static DBResult DeleteCtn(List 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 container_info where cntrid=@cntrid "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@cntrid", DbType.String, enumValue.cntrid); 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; } #endregion #region 集装箱动态 static public List GetDataStatusList(string strCondition,string orderby="") { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("[b_id],[box_no],[blno],[vessel],[voyage],[etd],[reachtime],[opertime],sdzt"); strSql.Append(",[state],[position],[operman],[fobport],[Remarks],ONEWAY"); strSql.Append(",jcyy,cz,cctime,jctime,(case when state='1' then '空箱' when state='2' then '重箱' end) stateref");// strSql.Append(",fobportname,positionname"); strSql.Append(",(select cnsource from container_info where cntrno=container_business.box_no) cnsource"); strSql.Append(",(select cntype from container_info where cntrno=container_business.box_no) cntrsize"); strSql.Append(",(select TEU from op_ctn where CNTRNO = 'TCNU5968130') TEU "); strSql.Append(" from container_business "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } if (orderby=="") { strSql.Append(" order by box_no, opertime "); } else { strSql.Append(" "+orderby); } return SetStatusData(strSql); } static public MsCtnStatus GetStatusData(string condition) { var list = GetDataStatusList(condition); if (list.Count > 0) return list[0]; return new MsCtnStatus(); } private static List SetStatusData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsCtnStatus data = new MsCtnStatus(); #region Set DB data to Object data.b_id = Convert.ToInt32(reader["b_id"]); data.box_no = Convert.ToString(reader["box_no"]); data.blno = Convert.ToString(reader["blno"]); data.vessel = Convert.ToString(reader["vessel"]); data.sdzt = Convert.ToString(reader["sdzt"]); data.voyage = Convert.ToString(reader["voyage"]); if (reader["etd"] != DBNull.Value) data.etd = Convert.ToDateTime(reader["etd"]).ToString("yyyy-MM-dd"); if (reader["reachtime"] != DBNull.Value) data.reachtime = Convert.ToDateTime(reader["reachtime"]).ToString("yyyy-MM-dd"); if (reader["opertime"] != DBNull.Value) data.opertime = string.Format("{0:d}", Convert.ToDateTime(reader["opertime"])); if (reader["state"] != DBNull.Value) data.state = Convert.ToString(reader["state"]); data.stateref = Convert.ToString(reader["stateref"]); data.position = Convert.ToString(reader["position"]); data.positionname = Convert.ToString(reader["positionname"]); data.operman = Convert.ToString(reader["operman"]); data.fobport = Convert.ToString(reader["fobport"]); data.fobportname = Convert.ToString(reader["fobportname"]); data.ONEWAY = Convert.ToString(reader["ONEWAY"]); data.Remarks = Convert.ToString(reader["Remarks"]); data.cnsource = Convert.ToString(reader["cnsource"]); data.cntrsize = Convert.ToString(reader["cntrsize"]); data.jcyy = Convert.ToString(reader["jcyy"]); data.cz = Convert.ToString(reader["cz"]); data.cctime = Convert.ToString(reader["cctime"]); if (data.cctime == null || data.cctime == "") { } else { if (Convert.ToDateTime(data.cctime).ToString("HH:mm:ss")=="00:00:00") { data.cctime = Convert.ToDateTime(data.cctime).ToString("yyyy-MM-dd"); } else { data.cctime = Convert.ToDateTime(data.cctime).ToString(); } } data.jctime = Convert.ToString(reader["jctime"]); if (data.jctime == null || data.jctime == "") { } else { string a = Convert.ToDateTime(data.jctime).ToString("HH:mm:ss"); if (Convert.ToDateTime(data.jctime).ToString("HH:mm:ss") == "00:00:00") { data.jctime = Convert.ToDateTime(data.jctime).ToString("yyyy-MM-dd"); } else { data.jctime = Convert.ToDateTime(data.jctime).ToString(); } } data.TEU = Convert.ToString(reader["TEU"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #region 锁定业务 public static DBResult sdyw(List bodyList,string type) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); string aaa = ""; using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { string sql = ""; if (bodyList != null) { foreach (var enumValue in bodyList) { if (type=="1") { sql = "update container_business set sdzt='锁定' where b_id='"+enumValue.b_id+"'"; aaa = "锁定"; } else { sql = "update container_business set sdzt='' where b_id='" + enumValue.b_id + "'"; aaa = "解锁"; } var cmdmx = db.GetSqlStringCommand(sql); db.ExecuteNonQuery(cmdmx, tran); } } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = aaa+"出现错误,请联系管理员"; return result; } } result.Success = true; result.Message = aaa+"成功" + result.Message; return result; } #endregion public static DBResult DeleteStatus(List 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 container_business where b_id=@b_id "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@b_id", DbType.String, enumValue.b_id); 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 ModifyStatus(List bodyList,MsCtnStatus modifydata) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var strSql = new StringBuilder(); strSql.Append("update container_business set blno=blno "); if (modifydata.vessel != "" && modifydata.vessel != null) strSql.Append(",Vessel=@Vessel"); if (modifydata.voyage != "" && modifydata.voyage != null) strSql.Append(",Voyage=@Voyage"); if (modifydata.etd != "" && modifydata.etd != null) strSql.Append(",Etd=@Etd"); if (modifydata.state !="0") strSql.Append(",State=@State"); if (modifydata.position != "" && modifydata.position != null) strSql.Append(",Position=@Position"); if (modifydata.reachtime != "" && modifydata.reachtime != null) strSql.Append(",ReachTime=@ReachTime"); if (modifydata.fobport != "" && modifydata.fobport != null) strSql.Append(",fobport=@fobport"); if (modifydata.Remarks != "" && modifydata.Remarks != null) strSql.Append(",Remarks=@Remarks"); strSql.Append(" where b_id=@b_id "); var cmdupdate = db.GetSqlStringCommand(strSql.ToString()); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdupdate.Parameters.Clear(); if (modifydata.vessel != "" && modifydata.vessel != null) db.AddInParameter(cmdupdate, "@Vessel", DbType.String, modifydata.vessel); if (modifydata.voyage != "" && modifydata.voyage != null) db.AddInParameter(cmdupdate, "@Voyage", DbType.String, modifydata.voyage); if (modifydata.etd != "" && modifydata.etd != null) db.AddInParameter(cmdupdate, "@Etd", DbType.String, modifydata.etd); if (modifydata.state !="0") db.AddInParameter(cmdupdate, "@State", DbType.String, modifydata.state); if (modifydata.position != "" && modifydata.position != null) db.AddInParameter(cmdupdate, "@Position", DbType.String, modifydata.position); if (modifydata.reachtime != "" && modifydata.reachtime != null) db.AddInParameter(cmdupdate, "@ReachTime", DbType.String, modifydata.reachtime); if (modifydata.fobport != "" && modifydata.fobport != null) db.AddInParameter(cmdupdate, "@fobport", DbType.String, modifydata.fobport); if (modifydata.Remarks != "" && modifydata.Remarks != null) db.AddInParameter(cmdupdate, "@Remarks", DbType.String, modifydata.Remarks); db.AddInParameter(cmdupdate, "@b_id", DbType.String, enumValue.b_id); 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 ModifyCtn(List bodyList, MsCtn modifydata) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var strSql = new StringBuilder(); strSql.Append("update container_info set cntrno=cntrno "); if (modifydata.status != 0) strSql.Append(",status=@status"); if (modifydata.rent_begintime != "" && modifydata.rent_begintime != null) strSql.Append(",rent_begintime=@rent_begintime"); if (modifydata.rent_endtime != "" && modifydata.rent_endtime != null) strSql.Append(",rent_endtime=@rent_endtime"); strSql.Append(" where cntrid=@cntrid "); var cmdupdate = db.GetSqlStringCommand(strSql.ToString()); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdupdate.Parameters.Clear(); if (modifydata.status != 0) db.AddInParameter(cmdupdate, "@status", DbType.String, modifydata.status); if (modifydata.rent_begintime != "" && modifydata.rent_begintime != null) db.AddInParameter(cmdupdate, "@rent_begintime", DbType.String, modifydata.rent_begintime); if (modifydata.rent_endtime != "" && modifydata.rent_endtime != null) db.AddInParameter(cmdupdate, "@rent_endtime", DbType.String, modifydata.rent_endtime); db.AddInParameter(cmdupdate, "@cntrid", DbType.String, enumValue.cntrid); 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 string importBox(MsCtnStatus headdata,string username) { string str = ""; if (isCntrNO(headdata.box_no)) { try { T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); DataSet ds = T_ALL_DA.GetAllSQL("select * from container_business where vessel='" + headdata.vessel + "' and Voyage='" + headdata.voyage + "' and Box_No='" +headdata.box_no+ "'"); if (ds != null && ds.Tables[0].Rows.Count > 0) { Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdupdate = db.GetSqlStringCommand("update container_business set BLNO=@BLNO,Etd=@Etd,State=@State,positionname=@positionname,ReachTime=@ReachTime,fobportname=@fobportname,operman=@operman ,opertime=@opertime,Remarks=@Remarks,OneWay=@OneWay where Vessel=@Vessel and Voyage=@Voyage and Box_No=@Box_No"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BLNO", DbType.String, headdata.blno); db.AddInParameter(cmdupdate, "@Vessel", DbType.String, headdata.vessel); db.AddInParameter(cmdupdate, "@Voyage", DbType.String, headdata.voyage); db.AddInParameter(cmdupdate, "@Box_No", DbType.String, headdata.box_no); db.AddInParameter(cmdupdate, "@Etd", DbType.String, sqlnull(headdata.etd)); db.AddInParameter(cmdupdate, "@State", DbType.String, headdata.state); db.AddInParameter(cmdupdate, "@positionname", DbType.String, headdata.positionname); db.AddInParameter(cmdupdate, "@ReachTime", DbType.String, sqlnull(headdata.reachtime)); db.AddInParameter(cmdupdate, "@fobportname", DbType.String, headdata.fobportname); db.AddInParameter(cmdupdate, "@operman", DbType.String, username); db.AddInParameter(cmdupdate, "@opertime", DbType.String, headdata.etd); db.AddInParameter(cmdupdate, "@Remarks", DbType.String, headdata.Remarks); db.AddInParameter(cmdupdate, "@OneWay", DbType.String, headdata.ONEWAY); db.ExecuteNonQuery(cmdupdate, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); } } } else { Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdupdate = db.GetSqlStringCommand("insert into container_business (BLNO,Vessel,Voyage,Box_No,Etd,State,positionname,ReachTime,fobportname,operman,opertime,oneway)values(@BLNO,@Vessel,@Voyage,@Box_No,@Etd,@State,@positionname,@ReachTime,@fobportname,@operman,@opertime,@oneway)"); cmdupdate.Parameters.Clear(); db.AddInParameter(cmdupdate, "@BLNO", DbType.String, headdata.blno); db.AddInParameter(cmdupdate, "@Vessel", DbType.String, headdata.vessel); db.AddInParameter(cmdupdate, "@Voyage", DbType.String, headdata.voyage); db.AddInParameter(cmdupdate, "@Box_No", DbType.String, headdata.box_no); db.AddInParameter(cmdupdate, "@Etd", DbType.String, sqlnull(headdata.etd)); db.AddInParameter(cmdupdate, "@State", DbType.String, headdata.state); db.AddInParameter(cmdupdate, "@positionname", DbType.String, headdata.positionname); db.AddInParameter(cmdupdate, "@ReachTime", DbType.String,sqlnull( headdata.reachtime)); db.AddInParameter(cmdupdate, "@fobportname", DbType.String, headdata.fobportname); db.AddInParameter(cmdupdate, "@operman", DbType.String, username); db.AddInParameter(cmdupdate, "@opertime", DbType.String, headdata.etd); db.AddInParameter(cmdupdate, "@oneway", DbType.String, headdata.ONEWAY); db.ExecuteNonQuery(cmdupdate, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); } } } // BaseClass.db.SetSqlStrNonQuery("update container_info set status=" + setFE(dr["ISFULL"].ToString()) + ",Position='" + posi + "',positionname='" + posiname + "',Vessel='" + vsl + "',Voyage='" + val + "' where CntrNo='" + dr["CNTR NO"].ToString().Trim() + "'"); } catch { } } else { str += headdata.box_no + ","; } if (!string.IsNullOrEmpty(str.TrimEnd(','))) { str = "已导入，以下箱号不合法未能导入：\\n" + str.TrimEnd(','); } else str += "导入成功"; return str; } //public static string getPosition(string position) //{ // string str = ""; // try // { // str = BaseClass.db.GetSqlStrScalar("select portid from code_disport where cname='" + position + "'").ToString(); // } // catch { } // return str; //} public static string getDate(string date) { string str = null; try { str = DateTime.Parse(date).ToString("yyyy-MM-dd"); } catch { } return str; } public static string getStatus(string s) { string str = null; switch (s) { case "可用": str = "1"; break; case "已用": str = "2"; break; case "维修": str = "3"; break; case "停用": str = "4"; break; case "退租": str = "5"; break; case "灭失": str = "6"; break; case "卖出": str = "7"; break; } return str; } public static string setFE(string s) { string str = null; if (s.ToUpper() == "F") str = "2"; else if (s.ToUpper() == "E") str = "1"; return str; } public static bool isCntrNO(string no) { String regex = @"^[a-zA-Z]{4}\d{7}$"; if (Regex.IsMatch(no, regex)) { return true; } else { return false; } } #region 查询当前登录用户 static public string getuser(string userid) { string sql = "select showname from [user] where gid='" + userid + "'"; return SetUser(sql); } private static string SetUser(string sb) { string s = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, sb.ToString())) { while (reader.Read()) { #region Set DB data to Object s= Convert.ToString(reader["showname"]); #endregion } reader.Close(); } return s; } #endregion #endregion #region 导入Excel public static DBResult ImportCtnDetailData(string data, HttpRequestBase request, DataTable table, out string msg, out List headList,string userid) { var result = new DBResult(); StringBuilder sb = new StringBuilder(""); 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 T = connection.BeginTransaction(); try { string jcyy = string.Empty; string vessel = string.Empty; string voyage = string.Empty; string blno = string.Empty; string state = string.Empty; string jctime = string.Empty; string cctime = string.Empty; foreach (DataRow row in table.Rows) { if (isCntrNO(row["箱号"].ToString())==false) { sb.Append(row["箱号"].ToString()+","); } else { if (data == "1")//what happend? { jcyy = Convert.ToString(row["进场原因"]).Trim(); vessel = Convert.ToString(row["进场船名"]).Trim(); voyage = Convert.ToString(row["进场航次"]).Trim(); blno = Convert.ToString(row["进场提单号"]).Trim(); state = Convert.ToString(row["进场箱况"]).Trim(); jctime = sqlnull(row["进场日期"].ToString()); cctime = "null"; } else { jcyy = Convert.ToString(row["出场性质"]).Trim(); vessel = Convert.ToString(row["出场船名"]).Trim(); voyage = Convert.ToString(row["出场航次"]).Trim(); blno = Convert.ToString(row["出场提单号"]).Trim(); cctime = sqlnull(row["出场日期时间"].ToString()); jctime = "null"; } string sql = " update container_info set cntype='" + Convert.ToString(row["箱型"]).Trim() + "' where cntrno='" + Convert.ToString(row["箱号"]).Trim() + "'"; var cmd = db.GetSqlStringCommand(sql); int a = db.ExecuteNonQuery(cmd, T); if (a == 0) { sb.Append(Convert.ToString(row["箱号"]).Trim() + ","); } else { string sqlmx = string.Format("insert into container_business(jcyy,box_no,CNTROWNER,jctime,vessel," + "voyage,blno,[state],cz,operman,opertime,cctime) values('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}'," + cctime + ")", jcyy, Convert.ToString(row["箱号"]).Trim(), Convert.ToString(row["箱属"]).Trim(), jctime , vessel, voyage, blno, state, Convert.ToString(row["场站名"]).Trim(), getuser(userid), DateTime.Now.ToString("yyyy-MM-dd")); var cmdmx = db.GetSqlStringCommand(sqlmx); db.ExecuteNonQuery(cmdmx, T); } } } T.Commit(); string nmsg = sb.ToString(); if (nmsg!="") { result.Message = "导入成功,以下箱号不存在,故未能导入:
" + nmsg; } else { result.Message = "导入成功"; } result.Success = true; } catch (Exception exception) { T.Rollback(); result.Success = false; result.Message = exception.Message; } } return result; } public static string DTS(string s) { if (s == "" || s == null) { s = "0"; } return s; } static public string sqlnull(string s) { if (s == "" || s == null) { return "null"; } else { return "\'" + s + "\'"; } } #endregion #region 参照部分 #endregion } }