using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Text; using Microsoft.Practices.EnterpriseLibrary.Data; using System.Data; using HcUtility.Comm; using DSWeb.Areas.MvcShipping.Models.Disport; using System.Text.RegularExpressions; using DSWeb.Areas.MvcShipping.Models.MsOpCtnRentBuy; using WebSqlHelper; using System.Data.Common; using DSWeb.MvcShipping.Models.MsOpCtnStatus; using DSWeb.MvcShipping.Models.PortRef; using DSWeb.Areas.MvcShipping.Comm; namespace DSWeb.Areas.MvcShipping.DAL.MsOpCtnRentBuy { public class op_ctn_outin { // 查询op_ctn_outin语句 static public List GetDataList(string strCondition, string userid, string usercode, string orgcode, string sort = null) { StringBuilder sb = new StringBuilder(" select o.gid,"); sb.Append("(case when o.status=1 then '新建' when o.status=2 then '提交审核' when o.status=3 then '审核' end) statusref, "); sb.Append("(case when ctntype=1 then '买箱' when ctntype=2 then '租入' when ctntype=3 then '租出' when ctntype=4 then '卖箱' end) ctntyperef, "); sb.Append("cntrowner,o.cnsource,buytime,selltime,rent_begintime,rent_endtime,o.REMARKS,BSDATE,INPUTBY,AUDITBY,BSNO,SHOWNAME,SELLBY,RENTDAY,(select showname from [user] where gid=auditby) as shr "); sb.Append(" from op_ctn_outin as o inner join [user] as u on(u.gid=o.inputby)"); if (!string.IsNullOrEmpty(strCondition)) { sb.Append(" where " + strCondition + " order by bsdate desc"); } else { sb.Append(" order by bsdate desc"); } return SetData(sb); } #region 根据ID进行查询 static public List op_ctn_outinByID(string GID) { StringBuilder sb = new StringBuilder(" select o.gid,"); sb.Append("(case when status=1 then '新建' when status=2 then '提交审核' when status=3 then '审核' end) statusref, "); sb.Append("(case when ctntype=1 then '买箱' when ctntype=2 then '租入' when ctntype=3 then '租出' when ctntype=4 then '卖箱' end) ctntyperef,"); sb.Append("cntrowner,cnsource,buytime,selltime,rent_begintime,rent_endtime,REMARKS,BSDATE,INPUTBY,AUDITBY,BSNO,SHOWNAME,SELLBY,RENTDAY"); sb.Append(",(select showname from [user] where gid=auditby) as shr from op_ctn_outin as o inner join [user] as u on(u.gid=o.inputby) where o.gid='" + GID + "'"); return SetData(sb); } #endregion #region 查询当前登录用户 static public List getuser(string userid) { string sql = "select showname from [user] where gid='" + userid + "'"; return SetUser(sql); } private static List SetUser(string sb) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, sb.ToString())) { while (reader.Read()) { op_ctn_outinModel data = new op_ctn_outinModel(); #region Set DB data to Object data.INPUTBY = Convert.ToString(reader["showname"]); data.BSDATE = DateTime.Now.ToShortDateString().ToString(); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion private static List SetData(StringBuilder sb) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, sb.ToString())) { while (reader.Read()) { op_ctn_outinModel data = new op_ctn_outinModel(); #region Set DB data to Object data.GID = Convert.ToString(reader["gid"]); data.STATUSREF = Convert.ToString(reader["statusref"]); data.CTNTYPEREF = Convert.ToString(reader["ctntyperef"]); data.CNTROWNER = Convert.ToString(reader["cntrowner"]); data.CNSOURCE = Convert.ToString(reader["cnsource"]); data.RENTDAY = Convert.ToString(reader["RENTDAY"]); data.SELLTIME = Convert.ToString(reader["selltime"]); if (data.SELLTIME == null || data.SELLTIME == "") { } else { data.SELLTIME = Convert.ToDateTime(data.SELLTIME).ToString("yyyy-MM-dd"); } data.RENT_BEGINTIME = Convert.ToString(reader["rent_begintime"]); if (data.RENT_BEGINTIME == null || data.RENT_BEGINTIME == "") { } else { data.RENT_BEGINTIME = Convert.ToDateTime(data.RENT_BEGINTIME).ToString("yyyy-MM-dd"); } data.RENT_ENDTIME = Convert.ToString(reader["rent_endtime"]); if (data.RENT_ENDTIME == null || data.RENT_ENDTIME == "") { } else { data.RENT_ENDTIME = Convert.ToDateTime(data.RENT_ENDTIME).ToString("yyyy-MM-dd"); } data.BUYTIME = Convert.ToString(reader["buytime"]); if (data.BUYTIME==null||data.BUYTIME=="") { } else { data.BUYTIME = Convert.ToDateTime(data.BUYTIME).ToString("yyyy-MM-dd"); } data.Remarks = Convert.ToString(reader["REMARKS"]); data.BSDATE = Convert.ToString(reader["BSDATE"]); if (data.BSDATE == null || data.BSDATE == "") { } else { data.BSDATE = Convert.ToDateTime(data.BSDATE).ToString("yyyy-MM-dd"); } data.INPUTBY = Convert.ToString(reader["SHOWNAME"]); data.AUDITBY = Convert.ToString(reader["shr"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.SELLBY = Convert.ToString(reader["SELLBY"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #region 删除 public static DBResult DeleteDetail(op_ctn_outinModel headData) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmd = db.GetSqlStringCommand("delete from op_ctn_outin where gid='" + headData.GID + "'"); db.ExecuteNonQuery(cmd, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "删除成功"; return result; } #endregion #region 根据GID删除 public static void Deletexh(op_ctn_outinModel xh) { Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmd = db.GetSqlStringCommand(" delete from container_info where gid='" + xh.GID + "'"); db.ExecuteNonQuery(cmd, tran); tran.Commit(); } catch (Exception) { throw; } finally { conn.Close(); } } } #endregion #region 提交审核 public static DBResult UpdateDetail(op_ctn_outinModel headData,string st,string userid) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { #region 数据状态处理 if (st=="3") { if (headData.STATUSREF=="新建") { result.Success = false; result.Message = "该数据还未提交审核"; return result; } else if (headData.STATUSREF == "审核") { result.Success = false; result.Message = "该数据已经审核通过,无需再次审核"; return result; } else { int ys = 0; string fy = ""; string status = ""; switch (headData.CTNTYPEREF) { case "买箱": ys = 2;//应付 status = "1"; InsertCon(status,headData); fy = "买箱费"; break; case "租入": ys = 2;//应付 status = "1"; InsertCon(status,headData); fy = "租入箱费"; break; case "租出": ys = 1;//应收 status = "4"; InsertCon(status,headData); fy = "租出箱费"; break; case "卖箱": ys = 1;//应收 status = "7"; InsertCon(status,headData); fy = "卖箱费"; break; default: break; } string id = " linkgid='" + headData.GID + "'"; if (GetBodyList(id).Count>0) { InsertShr(headData,userid); SaveFee(GetBodyList(id),headData,userid,ys,fy); } } } if (st == "2") { if (headData.STATUSREF == "审核") { result.Success = false; result.Message = "该数据已经审核通过"; return result; } else if (headData.STATUSREF == "提交审核") { result.Success = false; result.Message = "该数据已经提交审核,无需再次提交"; return result; } } if (st == "4") { if (headData.STATUSREF == "新建") { result.Success = false; result.Message = "该数据还未通过审核,无法提交"; return result; } else if (headData.STATUSREF == "提交审核") { result.Success = false; result.Message = "该数据还未通过审核,无法提交"; return result; } else { Deletexh(headData); } var deletefree = db.GetSqlStringCommand("delete from ch_fee where bsno='" + headData.GID + "'"); db.ExecuteNonQuery(deletefree, tran); var c = db.GetSqlStringCommand("update op_ctn_outin set [status]='" + 1 + "' where gid='" + headData.GID + "'"); db.ExecuteNonQuery(c, tran); tran.Commit(); InsertShr(headData,""); result.Success = true; result.Message = "取消审核成功"; return result; } if (st == "1") { if (headData.STATUSREF == "审核") { result.Success = false; result.Message = "该数据已经审核通过"; return result; } else if (headData.STATUSREF == "新建") { result.Success = false; result.Message = "该数据还未提交审核"; return result; } } #endregion var cmd = db.GetSqlStringCommand("update op_ctn_outin set [status]='"+st+"' where gid='" + headData.GID + "'"); db.ExecuteNonQuery(cmd, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; if (st=="1") { result.Message = "取消成功"; } else if (st=="2") { result.Message = "提交审核成功"; } else if (st=="4") { result.Message = "取消审核成功"; } else { result.Message = "审核通过"; } return result; } #region 审核完之后插入审核人 public static void InsertShr(op_ctn_outinModel xh,string userid) { Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmd = db.GetSqlStringCommand("update op_ctn_outin set AUDITBY='" + userid + "' where gid='" + xh.GID + "'"); db.ExecuteNonQuery(cmd, tran); tran.Commit(); } catch (Exception) { throw; } } } #endregion #endregion #region 查看有没有重复的箱子 public static int cfBSNO(string bsno) { try { string csql = "select count(bsno) as c from op_ctn_outin where bsno='" + bsno+ "'"; int findresult = Convert.ToInt32(findname(csql)); return findresult; } catch (Exception) { throw; } } #endregion #region 读取数据库返回结果 private static string findname(string strSql) { string c = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { #region Set DB data to Object c = Convert.ToString(reader["c"]); #endregion } reader.Close(); } return c; } #endregion public static DBResult SDetail(op_ctn_outinModel headData, List bodyList, string userid, string status, string opstatus) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (opstatus=="add") { if (cfBSNO(headData.BSNO) > 0) { result.Success = false; result.Message = "保存出现错误,委托单号不可以重复"; return result; } } string sql = ""; var cmdDelete = db.GetSqlStringCommand("delete from op_ctn_outin where gid='" + headData.GID + "'"); db.ExecuteNonQuery(cmdDelete, tran); headData.GID = headData.DM_ID; switch (status) { case "1": string youmaobing = headData.SELLBY; sql = string.Format("insert into op_ctn_outin(gid,[status],ctnType,CNTROWNER,CNSOURCE,BUYTIME,BSNO,REMARKS,INPUTBY,BSDATE,sellby) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", headData.GID, 1, status, headData.CNTROWNER, headData.CNSOURCE, Convert.ToDateTime(headData.BUYTIME).ToString("yyyy-MM-dd"), headData.BSNO, headData.Remarks, userid, DateTime.Now.ToString("yyyy-MM-dd"),youmaobing); break; case "2": sql = string.Format("insert into op_ctn_outin(gid,[status],ctnType,CNTROWNER,CNSOURCE,SELLBY,RENT_BEGINTIME,RENT_ENDTIME,BSNO,RENTDAY,REMARKS,INPUTBY,BSDATE) values" + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','" + headData.Remarks + "','" + userid + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "')", headData.GID, 1, status, headData.CNTROWNER,headData.CNSOURCE,headData.SELLBY,headData.RENT_BEGINTIME,headData.RENT_ENDTIME,headData.BSNO,headData.RENTDAY); break; case "3": sql = string.Format("insert into op_ctn_outin(gid,[status],ctnType,CNTROWNER,CNSOURCE,SELLBY,RENT_BEGINTIME,RENT_ENDTIME,BSNO,RENTDAY,REMARKS,INPUTBY,BSDATE) values" + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','" + headData.Remarks + "','" + userid + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "')", headData.GID, 1, status, headData.CNTROWNER, headData.CNSOURCE, headData.SELLBY, headData.RENT_BEGINTIME, headData.RENT_ENDTIME, headData.BSNO, headData.RENTDAY); break; case "4": sql = string.Format("insert into op_ctn_outin(gid,[status],ctnType,CNTROWNER,CNSOURCE,SELLBY,SELLTIME,BSNO,REMARKS,INPUTBY,BSDATE) values" + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", headData.GID, 1, status, headData.CNTROWNER, headData.CNSOURCE, headData.SELLBY, headData.SELLTIME, headData.BSNO, headData.Remarks, userid, DateTime.Now.ToString("yyyy-MM-dd")); break; default: break; } var cmd = db.GetSqlStringCommand(sql); db.ExecuteNonQuery(cmd, tran); tran.Commit(); var t = conn.BeginTransaction(); var cmdDeletemx = db.GetSqlStringCommand("delete from op_ctn_outinmx where LINKGID='" + headData.GID + "'"); db.ExecuteNonQuery(cmdDeletemx, t); t.Commit(); var tn = conn.BeginTransaction(); if (bodyList != null) { #region 处理数据 foreach (var enumValue in bodyList) { string GidMx = Guid.NewGuid().ToString(); switch (enumValue.STATUS) { case "可用": enumValue.STATUS = "1"; break; case "已用": enumValue.STATUS = "2"; break; case "维修": enumValue.STATUS = "3"; break; case "停用": enumValue.STATUS = "4"; break; case "退租": enumValue.STATUS = "5"; break; case "灭失": enumValue.STATUS = "6"; break; case "卖出": enumValue.STATUS = "7"; break; default: break; } switch (enumValue.ONEWAY) { case "√": enumValue.ONEWAY = "0"; break; case "×": enumValue.ONEWAY = "1"; break; } switch (enumValue.FREEUSE) { case "√": enumValue.FREEUSE = "0"; break; case "×": enumValue.FREEUSE = "1"; break; } if (enumValue.UnitPrice == null || enumValue.UnitPrice == "") { enumValue.UnitPrice = "0"; } if (enumValue.cntage == null || enumValue.cntage == "") { } else { string[] a = Regex.Split(enumValue.cntage, "GMT", RegexOptions.IgnoreCase); enumValue.cntage = DateTime.Parse(a[0]).ToString("yyyy-MM-dd HH:mm:ss"); } #endregion string sqlc = "delete from op_ctn_outinmx where cntrno='" + enumValue.CNTRNO + "'"; var cd = db.GetSqlStringCommand(sqlc); db.ExecuteNonQuery(cd, tn); if (isCntrNO(enumValue.CNTRNO)==false) { result.Message = "箱号不符合规范"; result.Success = false; return result; } string sqlmxz = string.Format("insert into op_ctn_outinmx(gid,linkgid,cnttype,cntrno,unitprice," +"currency,[status],cnsource,sport,scustname,remarks,cntage,eport,ecustname,oneway,freeuse)" + "values('" + GidMx + "','" + headData.GID + "','" + enumValue.cntype + "','" + enumValue.CNTRNO + "','" + enumValue.UnitPrice + "','{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}')", enumValue.Currency, enumValue.STATUS, enumValue.cnsource, enumValue.SPORT, enumValue.sCustName, enumValue.REMARKS, sqlnull(enumValue.cntage), enumValue.EPORT, enumValue.eCustName, enumValue.ONEWAY, enumValue.FREEUSE); var cmdmx = db.GetSqlStringCommand(sqlmxz); db.ExecuteNonQuery(cmdmx, tn); } } tn.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } #region 查询明细表根据linkGID static public List GetBodyList(string strCondition, string sort = null) { var strSql = new StringBuilder(); //2018年3月22日12:01:53 修改bug //strSql.Append("select gid,linkgid,cnttype,cntrno,unitprice,currency,[status],cnsource,sport,scustname,remarks,"); //strSql.Append("cntage,eport,ecustname,oneway,freeuse,(select defaultrate from code_currency where codename=op_ctn_outinmx.currency) as code_currency from op_ctn_outinmx "); strSql.Append(@"select ox.*, cc.defaultrate as code_currency from op_ctn_outinmx ox left join code_currency cc on ox.currency = cc.CODENAME "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition + " order by id"); } return SetBodyData(strSql); } private static List SetBodyData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { op_ctn_outinmxModel data = new op_ctn_outinmxModel(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.LINKGID = Convert.ToString(reader["LINKGID"]); data.cntype = Convert.ToString(reader["cnttype"]); data.CNTRNO = Convert.ToString(reader["cntrno"]); data.UnitPrice = Convert.ToString(reader["unitprice"]); data.Currency = Convert.ToString(reader["currency"]); data.STATUS = Convert.ToString(reader["status"]); data.code_currency = Convert.ToString(reader["code_currency"]); switch (data.STATUS) { case "1": data.STATUS = "可用"; break; case "2": data.STATUS = "已用"; break; case "3": data.STATUS = "维修"; break; case "4": data.STATUS = "停用"; break; case "5": data.STATUS = "退租"; break; case "6": data.STATUS = "灭失"; break; case "7": data.STATUS = "卖出"; break; default: break; } data.cnsource = Convert.ToString(reader["cnsource"]); data.SPORT = Convert.ToString(reader["sport"]); data.sCustName = Convert.ToString(reader["scustname"]); data.REMARKS = Convert.ToString(reader["remarks"]); data.EPORT = Convert.ToString(reader["eport"]); data.eCustName = Convert.ToString(reader["ecustname"]); data.ONEWAY = Convert.ToString(reader["oneway"]); if (data.ONEWAY == "0") { data.ONEWAY = "√"; } else if (data.ONEWAY == "1") { data.ONEWAY = "×"; } data.FREEUSE = Convert.ToString(reader["freeuse"]); if (data.FREEUSE=="0") { data.FREEUSE = "√"; } else if (data.FREEUSE == "1") { data.FREEUSE = "×"; } data.cntage = Convert.ToString(reader["cntage"]); if (data.cntage == null || data.cntage == "") { } else { data.cntage = Convert.ToDateTime(data.cntage).ToString("yyyy-MM-dd"); } #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion #region 导入Excel public static DBResult ImportTrainDetailData(string data, HttpRequestBase request, DataTable table, out string msg, out List headList) { var result = new DBResult(); msg = ""; headList = new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); Database db = DatabaseFactory.CreateDatabase(); StringBuilder sb = new StringBuilder(""); using (DbConnection connection = db.CreateConnection()) { connection.Open(); try { DbTransaction T = connection.BeginTransaction(); foreach (DataRow row in table.Rows) { string GidMx = Guid.NewGuid().ToString(); if (Convert.ToString(row["箱龄"]).Trim() == null || Convert.ToString(row["箱龄"]).Trim() == "") { } else { row["箱龄"] = Convert.ToDateTime(row["箱龄"]).ToString("yyyy-MM-dd"); } if (isCntrNO(row["箱号"].ToString().Trim())==false) { sb.Append(row["箱号"].ToString()+","); } else { if (Convert.ToString(row["单价(金额)"]).Trim() == "") { } string sql = "delete from op_ctn_outinmx where cntrno='" + Convert.ToString(row["箱号"]).Trim() + "'"; var cd = db.GetSqlStringCommand(sql); db.ExecuteNonQuery(cd, T); string sqlmx = string.Format("insert into op_ctn_outinmx(gid,linkgid,cnttype,cntrno,unitprice," + "currency,[status],cnsource,sport,scustname,remarks,cntage,eport,ecustname,oneway,freeuse)" + "values('" + GidMx + "','" + data + "','" + Convert.ToString(row["箱型"]).Trim() + "','" + Convert.ToString(row["箱号"]).Trim() + "','" + DTS(Convert.ToString(row["单价(金额)"]).Trim()) + "','{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}')", Convert.ToString(row["币别"]).Trim(), business.getStatus(Convert.ToString(row["状态"]).Trim()), Convert.ToString(row["箱源"]).Trim(), Convert.ToString(row["启用港口"]).Trim(), Convert.ToString(row["启用场站"]).Trim(), Convert.ToString(row["备注"]).Trim(), sqlnull(Convert.ToString(row["箱龄"]).Trim()), Convert.ToString(row["结束位置港口"]).Trim(), Convert.ToString(row["结束位置场站"]).Trim(), Convert.ToString(row["oneway"]).Trim(), Convert.ToString(row["freeuse"]).Trim()); var cmdmx = db.GetSqlStringCommand(sqlmx); db.ExecuteNonQuery(cmdmx, T); } } T.Commit(); string ss = ""; ss = sb.ToString(); if (ss!="") { result.Message = "导入成功,以下箱号不符合规范,未能导入:
"+ss; } else { result.Message = "导入成功"; } result.Success = true; } catch (Exception exception) { //Tran.Rollback(); result.Success = false; result.Message = exception.Message; } } return result; } public static string DTS(string s) { if (s == ""||s==null) { s = "0"; } return s; } #endregion #region 查询需要插入集装箱表(container_info)中的数据 static public void InsertCon(string st,op_ctn_outinModel headData) { var strSql = new StringBuilder(); //2018年3月21日11:33:27 中司 租箱提交报错 SQL逻辑有BUG //strSql.Append("select o.gid,m.cntrno,o.cntrowner,m.CNTTYPE,m.cnsource,o.BUYTIME,o.rent_begintime,o.rent_endtime,o.selltime,"); //strSql.Append("m.status,m.REMARKS,m.sport,(select portid from code_disport where port=m.sport) as s,m.sCustName,m.eport,(select portid from code_disport where port=m.eport) as e,m.eCustName from op_ctn_outin as o left join "); //strSql.Append("op_ctn_outinmx as m on(o.gid=m.linkgid) where o.gid='" + headData.GID + "'"); strSql.Append(@"select o.gid,m.cntrno,o.cntrowner,m.CNTTYPE,m.cnsource,o.BUYTIME,o.rent_begintime,o.rent_endtime, o.selltime,m.status,m.REMARKS,m.sport, scd.PORTID s, m.sCustName,m.eport, ecd.PORTID e,m.eCustName from op_ctn_outin as o left join op_ctn_outinmx as m on(o.gid=m.linkgid) left join code_disport scd on scd.PORT = m.SPORT left join code_disport ecd on ecd.PORT = m.EPORT where o.gid='"+headData.GID+"'"); GetInsertData(st,strSql); } private static List GetInsertData(string st,StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); while (reader.Read()) { MsCtn data = new MsCtn(); #region Set DB data to Object data.gid = Convert.ToString(reader["gid"]); data.cntrno = Convert.ToString(reader["cntrno"]); data.cntrowner = Convert.ToString(reader["cntrowner"]); data.cntype = Convert.ToString(reader["CNTTYPE"]); data.cnsource = Convert.ToString(reader["cnsource"]); data.buytime = Convert.ToString(reader["BUYTIME"]); if (data.buytime == null || data.buytime == "") { } else { data.buytime = Convert.ToDateTime(data.buytime).ToString("yyyy-MM-dd"); } data.rent_begintime = Convert.ToString(reader["rent_begintime"]); if (data.rent_begintime == null || data.rent_begintime == "") { } else { data.rent_begintime = Convert.ToDateTime(data.rent_begintime).ToString("yyyy-MM-dd"); } data.rent_endtime = Convert.ToString(reader["rent_endtime"]); if (data.rent_endtime == null || data.rent_endtime == "") { } else { data.rent_endtime = Convert.ToDateTime(data.rent_endtime).ToString("yyyy-MM-dd"); } data.selltime = Convert.ToString(reader["selltime"]); if (data.selltime == null || data.selltime == "") { } else { data.selltime = Convert.ToDateTime(data.selltime).ToString("yyyy-MM-dd"); } int status = -1; if (reader["status"].ToString().Trim()!=null&&reader["status"].ToString().Trim()!="") { status = Convert.ToInt32(st); } data.REMARK = Convert.ToString(reader["REMARKS"]); data.beginpostion = reader["s"].ToString();//启用位置 data.position = reader["e"].ToString(); #endregion #region 添加数据库 bodyList.Add(data); if (data!=null) { string sql = string.Format("insert into container_info (gid,cntrno,cntrowner,cntype,cnsource,buytime,rent_begintime" + ",rent_endtime,[status],position,REMARK,beginpostion,selltime) values('" + data.gid + "','{0}','{1}','{2}','{3}',{4},{5},{6},'{7}','{8}','{9}','{10}'," + sqlnull(data.selltime) + ")", data.cntrno, data.cntrowner, data.cntype, data.cnsource, sqlnull(data.buytime), sqlnull(data.rent_begintime), sqlnull(data.rent_endtime), status, data.position, data.REMARK, data.beginpostion); var cmdmx = db.GetSqlStringCommand(sql); db.ExecuteNonQuery(cmdmx, tran); } #endregion } tran.Commit(); reader.Close(); } } return bodyList; } #endregion #region 插入费用明细 public static DBResult SaveFee(List bodyList,op_ctn_outinModel headData,string userid, int ctnnum,string fy) { 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 ch_fee(GID, BSNO, FEETYPE, FEENAME, FEEDESCRIPTION, CUSTOMERTYPE, CUSTOMERNAME, UNIT, UNITPRICE, QUANTITY, AMOUNT, CURRENCY, EXCHANGERATE, REASON, REMARK, COMMISSIONRATE,ENTEROPERATOR, ENTERDATE,ISOPEN, ISADVANCEDPAY, SORT, FEESTATUS, FEEFRT, TAXRATE,NOTAXAMOUNT,ACCTAXRATE,LINENUM,TAXUNITPRICE,MODIFIEDUSER,MODIFIEDTIME,WMSOUTBSNO,PREAMOUNT,INPUTMODE,IsCrmOrderFee) VALUES (@gid,@bsno,@fee_type,@fee_name,@fee_description,@customer_type,@customer_name,@unit,@unit_price,@quantity,@amount,@currency, @exchange_rate,@reason,@remark,@comm_rate,@enter_operator,@enter_date,@is_open,@is_advance_pay,@sort,@fee_status,@fee_frt, @taxrate,@notaxamount,@acctaxrate,@LINENUM,@taxunit_price,@MODIFIEDUSER,@MODIFIEDTIME,@WMSOUTBSNO,@PREAMOUNT,@INPUTMODE,@IsCrmOrderFee) "); if (bodyList != null) { foreach (var enumValue in bodyList) { if (fy=="买箱费"||fy=="卖箱费") { headData.RENTDAY = "1"; } cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@gid", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmdInsert, "@bsno", DbType.String, headData.GID); db.AddInParameter(cmdInsert, "@fee_type", DbType.Int16, ctnnum); db.AddInParameter(cmdInsert, "@fee_name", DbType.String, fy); db.AddInParameter(cmdInsert, "@customer_type", DbType.String, ""); db.AddInParameter(cmdInsert, "@customer_name", DbType.String, headData.SELLBY); db.AddInParameter(cmdInsert, "@unit", DbType.String, enumValue.cntype); if (ctnnum != 0) db.AddInParameter(cmdInsert, "@unit_price", DbType.Decimal, enumValue.UnitPrice); else db.AddInParameter(cmdInsert, "@unit_price", DbType.Decimal,enumValue.UnitPrice); // db.AddInParameter(cmdInsert, "@unit_price", DbType.Decimal, enumValue.UNITPRICE); db.AddInParameter(cmdInsert, "@fee_description", DbType.String, ""); db.AddInParameter(cmdInsert, "@quantity", DbType.Decimal, Convert.ToDouble(headData.RENTDAY)); if (ctnnum != 0) db.AddInParameter(cmdInsert, "@amount", DbType.Decimal, Convert.ToDouble(enumValue.UnitPrice) * Convert.ToDouble(headData.RENTDAY)); else db.AddInParameter(cmdInsert, "@amount", DbType.Decimal, Convert.ToDouble(enumValue.UnitPrice) * Convert.ToDouble(headData.RENTDAY)); db.AddInParameter(cmdInsert, "@currency", DbType.String, enumValue.Currency); if (enumValue.code_currency=="") { enumValue.code_currency = "0"; } db.AddInParameter(cmdInsert, "@exchange_rate", DbType.Decimal, Convert.ToDouble(enumValue.code_currency)); db.AddInParameter(cmdInsert, "@reason", DbType.String, ""); db.AddInParameter(cmdInsert, "@remark", DbType.String, ""); // db.AddInParameter(cmdInsert, "@CARGO_GID", DbType.String, enumValue.PRICETYPE); db.AddInParameter(cmdInsert, "@comm_rate", DbType.Decimal, 0); db.AddInParameter(cmdInsert, "@enter_operator", DbType.String, userid); db.AddInParameter(cmdInsert, "@enter_date", DbType.String, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); db.AddInParameter(cmdInsert, "@is_open", DbType.String, "0"); db.AddInParameter(cmdInsert, "@is_advance_pay", DbType.String, "0"); db.AddInParameter(cmdInsert, "@sort", DbType.Int16,0); db.AddInParameter(cmdInsert, "@fee_status", DbType.Int16, 0); db.AddInParameter(cmdInsert, "@fee_frt", DbType.String, ""); db.AddInParameter(cmdInsert, "@taxrate", DbType.Decimal, 0); if (ctnnum != 0) db.AddInParameter(cmdInsert, "@notaxamount", DbType.Decimal, Convert.ToDouble(enumValue.UnitPrice) * Convert.ToDouble(headData.RENTDAY)); else db.AddInParameter(cmdInsert, "@notaxamount", DbType.Decimal, Convert.ToDouble(enumValue.UnitPrice) * Convert.ToDouble(headData.RENTDAY)); db.AddInParameter(cmdInsert, "@acctaxrate", DbType.Decimal, 0); db.AddInParameter(cmdInsert, "@LINENUM", DbType.Int16, 0); if (ctnnum != 0) db.AddInParameter(cmdInsert, "@taxunit_price", DbType.Decimal,enumValue.UnitPrice); else db.AddInParameter(cmdInsert, "@taxunit_price", DbType.Decimal, enumValue.UnitPrice); // db.AddInParameter(cmdInsert, "@taxunit_price", DbType.Decimal, enumValue.UNITPRICE); db.AddInParameter(cmdInsert, "@MODIFIEDUSER", DbType.String, userid); db.AddInParameter(cmdInsert, "@MODIFIEDTIME", DbType.String, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); db.AddInParameter(cmdInsert, "@WMSOUTBSNO", DbType.String, ""); if (ctnnum != 0) db.AddInParameter(cmdInsert, "@PREAMOUNT", DbType.Decimal, Convert.ToDouble(enumValue.UnitPrice) * Convert.ToDouble(headData.RENTDAY)); else db.AddInParameter(cmdInsert, "@PREAMOUNT", DbType.Decimal, Convert.ToDouble(enumValue.UnitPrice) * Convert.ToDouble(headData.RENTDAY)); if (headData.CTNTYPEREF == "买箱" || headData.CTNTYPEREF == "租入") db.AddInParameter(cmdInsert, "@INPUTMODE", DbType.String, "应付费用"); else if (headData.CTNTYPEREF == "卖出" || headData.CTNTYPEREF == "租出") db.AddInParameter(cmdInsert, "@INPUTMODE", DbType.String, "应收费用"); else db.AddInParameter(cmdInsert, "@INPUTMODE", DbType.String, ""); db.AddInParameter(cmdInsert, "@IsCrmOrderFee", DbType.String, "1"); 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 箱号是否规范 public static bool isCntrNO(string no) { if (no==null||no=="") { return false; } String regex = @"^[a-zA-Z]{4}\d{7}$"; if (Regex.IsMatch(no, regex)) { return true; } else { return false; } } #endregion static public object sqlnull(string s) { if (s==""||s==null) { return "null"; } else { return "\'"+s+"\'"; } } } }