using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.MsOpRailway; using DSWeb.MvcShipping.Models.MsOpRailwayBill; using DSWeb.MvcShipping.Models.RailWayPort; using DSWeb.MvcShipping.Models.MsOpSeae; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.EntityDA; using DSWeb.Areas.CommMng.Models; using HcUtility.Comm; using System.Data.SqlClient; using DSWeb.DataAccess; using System.Web; using System.Data.Common; using DSWeb.TruckMng.Comm.Cookie; using DSWeb.MvcShipping.Models.MsOpCtnr; using HcUtility.Core; using DSWeb.MvcShipping.DAL.MsSysBillNoSet; namespace DSWeb.MvcShipping.DAL.MsOpRailwayDAL { public class MsOpRailwayDAL { #region Inquery DataList static public List GetDataList(string strCondition, string userid, string usercode, string companyid, string sort = null) { var rangstr = GetRangDAStr("index", userid, usercode, companyid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } // var strSql = new StringBuilder(); strSql.Append("SELECT B.BSNO,MASTERNO,BSSTATUS,FEESTATUS,BSDATE,ACCDATE,MBLNO,HBLNO,ORDERNO,CUSTNO,TRANSNO,CUSTOMERNAME,BLTYPE,TRANSTYPE"); strSql.Append(",TRANSDIRECT,TRAINNO,BSSOURCE,BSSOURCEDETAIL,PLACETRANSITENAME,ISVOU,VOUNO,REMARK"); strSql.Append(",CARRIER,PlACERECEIPT,PLACELOADID,PLACELOAD,PLACELOADENAME,DESTINATIONID,DESTINATION,DESTINATIONDETAIL,DESTINATIONENAME,PLACETRANSITID,PLACETRANSIT,SETOUTDATE,ASETOUTDATE"); strSql.Append(",TRAINREPORTDATE,TRAINDAY,STATIONTIME,ETA,PREVESSEL,PREVOY,VESSELDAY,REPLACEDOCDATE,CUSTOMDATE,CUSTOMCLEARDATE,CUSTOMTRANSITCORP,SHENCODE,CODESTATUS,SHENCODETIME,GIVECODETIME"); strSql.Append(",ARRIVEPLACETRANSIT,SETOUTPLACETRANSIT,TRANSITDAY,RETRAINNO,RETRAINDATE,FRPLACETRANSITID,FRPLACETRANSIT,ARRIVEFRPLACETRANSIT,RECTNTYPE,RECTNDATE"); strSql.Append(",ARRIVALDATE,ONROADDATE,ONROADPLACE,RETURNCTNCORP,RETURNDATE,RECEIVECTNDATE,RECEIVEYARD,CTNSOURCE,CTNALL,CTNNUM,CNTRNO,SEALNO,FRSEALNO,CTNWEIGHT"); strSql.Append(",PKGS,KINDPKGS,KGS,CBM,GOODSNAME,INPUTBY,OP,CUSTSERVICE,LANE,SALE,DOC,CORPID,SALEDEPT,CREATETIME,STLNAME,STLDATE,ORDERTYPE,OPERATORCODE"); strSql.Append(",OPERATOR,OPERATOREMAIL,OPERATORTEL,OPERATORFAX,FINANCIALSTAFFCODE,FINANCIALSTAFF,FINANCIALSTAFFEMAIL,FINANCIALSTAFFTEL,FINANCIALSTAFFFAX"); strSql.Append(", BSSTATUSREF "); strSql.Append(",FEESTATUSREF,CNTR1,CNTR2,CNTR3,CNTR4,CNTR5,CNTR6,CNTR7,CNTR8,CNTR9,CNTR10,OTCNTR,TEU,SALECORPID "); strSql.Append(",(select [NAME] from [company] where GID=B.SALECORPID) as SALECORP"); strSql.Append(",dbo.F_GetBillDrFeeStatus(B.BSNO) DRFEESTATUS"); strSql.Append(",dbo.F_GetBillCrFeeStatus(B.BSNO) CRFEESTATUS"); strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR"); strSql.Append(",OPSTATUS,(select TOP 1 COLOR FROM code_op_status_detail WHERE OPSTATUS = (CASE WHEN (SELECT TOP 1 [STATUS] FROM OP_STATUS WHERE BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) is null THEN '接受委托' else (SELECT TOP 1[STATUS] FROM OP_STATUS WHERE BSNO = B.BSNO ORDER BY COMPTIME DESC, INPUTTIME DESC) end))AS COLOR "); strSql.Append(" FROM V_OP_RAILWAY B "); strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=B.BSNO) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by CUSTNO desc"); } return SetData(strSql); } static public string GetDataListStr(string strCondition, string userid, string usercode, string companyid, string sort = null) { var rangstr = GetRangDAStr("index", userid, usercode, companyid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } // var strSql = new StringBuilder(); strSql.Append("SELECT B.BSNO,MASTERNO,BSSTATUS,FEESTATUS,BSDATE,ACCDATE,MBLNO,HBLNO,ORDERNO,CUSTNO,TRANSNO,CUSTOMERNAME,BLTYPE,TRANSTYPE"); strSql.Append(",TRANSDIRECT,TRAINNO,BSSOURCE,BSSOURCEDETAIL,PLACETRANSITENAME,ISVOU,VOUNO,REMARK"); strSql.Append(",CARRIER,PlACERECEIPT,PLACELOADID,PLACELOAD,PLACELOADENAME,DESTINATIONID,DESTINATION,DESTINATIONDETAIL,DESTINATIONENAME,PLACETRANSITID,PLACETRANSIT,SETOUTDATE,ASETOUTDATE"); strSql.Append(",TRAINREPORTDATE,TRAINDAY,STATIONTIME,ETA,PREVESSEL,PREVOY,VESSELDAY,REPLACEDOCDATE,CUSTOMDATE,CUSTOMCLEARDATE,CUSTOMTRANSITCORP,SHENCODE,CODESTATUS,SHENCODETIME,GIVECODETIME"); strSql.Append(",ARRIVEPLACETRANSIT,SETOUTPLACETRANSIT,TRANSITDAY,RETRAINNO,RETRAINDATE,FRPLACETRANSITID,FRPLACETRANSIT,ARRIVEFRPLACETRANSIT,RECTNTYPE,RECTNDATE"); strSql.Append(",ARRIVALDATE,ONROADDATE,ONROADPLACE,RETURNCTNCORP,RETURNDATE,RECEIVECTNDATE,RECEIVEYARD,CTNSOURCE,CTNALL,CTNNUM,CNTRNO,SEALNO,FRSEALNO,CTNWEIGHT"); strSql.Append(",PKGS,KINDPKGS,KGS,CBM,GOODSNAME,INPUTBY,OP,CUSTSERVICE,LANE,SALE,DOC,CORPID,SALEDEPT,CREATETIME,STLNAME,STLDATE,ORDERTYPE,OPERATORCODE"); strSql.Append(",OPERATOR,OPERATOREMAIL,OPERATORTEL,OPERATORFAX,FINANCIALSTAFFCODE,FINANCIALSTAFF,FINANCIALSTAFFEMAIL,FINANCIALSTAFFTEL,FINANCIALSTAFFFAX"); strSql.Append(",(CASE BSSTATUS WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF "); strSql.Append(",(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,SALECORPID "); strSql.Append(",(select [NAME] from [company] where GID=B.SALECORPID) as SALECORP"); strSql.Append(",dbo.F_GetBillDrFeeStatus(B.BSNO) DRFEESTATUS"); strSql.Append(",dbo.F_GetBillCrFeeStatus(B.BSNO) CRFEESTATUS"); strSql.Append(",I.TTLDR,I.TTLINVDR,I.TTLDUIDR"); strSql.Append(",(SELECT TOP 1 STATUS FROM OP_STATUS WHERE BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS,CNTR1,CNTR2,CNTR3,CNTR4,CNTR5,CNTR6,CNTR7,CNTR8,CNTR9,CNTR10,OTCNTR,TEU "); strSql.Append(" FROM OP_RAILWAY B "); strSql.Append(" LEFT JOIN v_op_gain_dr_INV I ON (I.BSNO=B.BSNO) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } //var sortstring = DatasetSort.Getsortstring(sort); //if (!string.IsNullOrEmpty(sortstring)) //{ // strSql.Append(" order by " + sortstring); //} //else //{ strSql.Append(" order by BSDATE desc"); //} return strSql.ToString(); } static public MsOpRailway GetData(string condition) { var strSql = new StringBuilder(); strSql.Append("SELECT BSNO,MASTERNO,BSSTATUS,FEESTATUS,BSDATE,ACCDATE,MBLNO,HBLNO,ORDERNO,CUSTNO,TRANSNO,CUSTOMERNAME,BLTYPE,TRANSTYPE"); strSql.Append(",TRANSDIRECT,TRAINNO,BSSOURCE,BSSOURCEDETAIL,PLACETRANSITENAME,ISVOU,VOUNO,REMARK"); strSql.Append(",CARRIER,PlACERECEIPT,PLACELOADID,PLACELOAD,PLACELOADENAME,DESTINATIONID,DESTINATION,DESTINATIONDETAIL,DESTINATIONENAME,PLACETRANSITID,PLACETRANSIT,SETOUTDATE,ASETOUTDATE"); strSql.Append(",TRAINREPORTDATE,TRAINDAY,STATIONTIME,ETA,PREVESSEL,PREVOY,VESSELDAY,REPLACEDOCDATE,CUSTOMDATE,CUSTOMCLEARDATE,CUSTOMTRANSITCORP,SHENCODE,CODESTATUS,SHENCODETIME,GIVECODETIME"); strSql.Append(",ARRIVEPLACETRANSIT,SETOUTPLACETRANSIT,TRANSITDAY,RETRAINNO,RETRAINDATE,FRPLACETRANSITID,FRPLACETRANSIT,ARRIVEFRPLACETRANSIT,RECTNTYPE,RECTNDATE"); strSql.Append(",ARRIVALDATE,ONROADDATE,ONROADPLACE,RETURNCTNCORP,RETURNDATE,RECEIVECTNDATE,RECEIVEYARD,CTNSOURCE,CTNALL,CTNNUM,CNTRNO,SEALNO,FRSEALNO,CTNWEIGHT"); strSql.Append(",PKGS,KINDPKGS,KGS,CBM,GOODSNAME,INPUTBY,OP,CUSTSERVICE,SALE,DOC,CORPID,SALEDEPT,CREATETIME,STLNAME,STLDATE,ORDERTYPE,OPERATORCODE"); strSql.Append(",OPERATOR,OPERATOREMAIL,OPERATORTEL,OPERATORFAX,FINANCIALSTAFFCODE,FINANCIALSTAFF,FINANCIALSTAFFEMAIL,FINANCIALSTAFFTEL,FINANCIALSTAFFFAX"); strSql.Append(",(CASE BSSTATUS WHEN 1 THEN '锁定' else '未锁定' end) as BSSTATUSREF "); strSql.Append(",(CASE FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF,SALECORPID "); strSql.Append(",(select [NAME] from [company] where GID=B.SALECORPID) as SALECORP"); strSql.Append(",dbo.F_GetBillDrFeeStatus(B.BSNO) DRFEESTATUS"); strSql.Append(",dbo.F_GetBillCrFeeStatus(B.BSNO) CRFEESTATUS"); strSql.Append(",(SELECT TOP 1 STATUS FROM OP_STATUS WHERE BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS,CNTR1,CNTR2,CNTR3,CNTR4,CNTR5,CNTR6,CNTR7,CNTR8,CNTR9,CNTR10,OTCNTR,TEU "); strSql.Append(",0 TTLDR,0 TTLINVDR,0 TTLDUIDR"); strSql.Append(" FROM OP_RAILWAY B "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition); } strSql.Append(" order by BSDATE desc"); var list=SetData(strSql); if (list.Count > 0) { return list[0]; } return new MsOpRailway(); } 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()) { MsOpRailway data = new MsOpRailway(); #region Set DB data to Object reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'COLOR'"; if (reader.GetSchemaTable().DefaultView.Count > 0) data.COLOR = Convert.ToString(reader["COLOR"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.BSSTATUS = Convert.ToBoolean(reader["BSSTATUS"]); data.FEESTATUS = Convert.ToBoolean(reader["FEESTATUS"]); data.BSSTATUSREF = Convert.ToString(reader["BSSTATUSREF"]); data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]); data.DRFEESTATUS = getfeestatus(Convert.ToString(reader["DRFEESTATUS"])); data.CRFEESTATUS = getfeestatus(Convert.ToString(reader["CRFEESTATUS"])); data.OPSTATUS = Convert.ToString(reader["OPSTATUS"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.HBLNO = Convert.ToString(reader["HBLNO"]); data.ORDERNO = Convert.ToString(reader["ORDERNO"]); data.TRANSNO = Convert.ToString(reader["TRANSNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); if (reader["BSDATE"] != DBNull.Value) data.BSDATE = Convert.ToDateTime(reader["BSDATE"]).ToString("yyyy-MM-dd"); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.BSSOURCE = Convert.ToString(reader["BSSOURCE"]); data.BSSOURCEDETAIL = Convert.ToString(reader["BSSOURCEDETAIL"]); data.OP = Convert.ToString(reader["OP"]); data.INPUTBY = Convert.ToString(reader["INPUTBY"]); data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]); data.SALE = Convert.ToString(reader["SALE"]); data.CORPID = Convert.ToString(reader["CORPID"]); data.BLTYPE = Convert.ToString(reader["BLTYPE"]); data.TRANSTYPE = Convert.ToString(reader["TRANSTYPE"]); data.TRANSDIRECT = Convert.ToString(reader["TRANSDIRECT"]); data.TRAINNO = Convert.ToString(reader["TRAINNO"]); data.CARRIER = Convert.ToString(reader["CARRIER"]); data.PlACERECEIPT = Convert.ToString(reader["PlACERECEIPT"]); data.PLACELOADID = Convert.ToString(reader["PLACELOADID"]); data.PLACELOAD = Convert.ToString(reader["PLACELOAD"]); data.PLACELOADENAME = Convert.ToString(reader["PLACELOADENAME"]); data.DESTINATIONID = Convert.ToString(reader["DESTINATIONID"]); data.DESTINATION = Convert.ToString(reader["DESTINATION"]); data.DESTINATIONDETAIL = Convert.ToString(reader["DESTINATIONDETAIL"]); data.DESTINATIONENAME = Convert.ToString(reader["DESTINATIONENAME"]); data.PLACETRANSITID = Convert.ToString(reader["PLACETRANSITID"]); data.PLACETRANSIT = Convert.ToString(reader["PLACETRANSIT"]); data.PLACETRANSITENAME = Convert.ToString(reader["PLACETRANSITENAME"]); if (reader["SETOUTDATE"] != DBNull.Value) data.SETOUTDATE = Convert.ToDateTime(reader["SETOUTDATE"]).ToString("yyyy-MM-dd"); if (reader["ASETOUTDATE"] != DBNull.Value) data.ASETOUTDATE = Convert.ToDateTime(reader["ASETOUTDATE"]).ToString("yyyy-MM-dd"); if (reader["TRAINREPORTDATE"] != DBNull.Value) data.TRAINREPORTDATE = Convert.ToDateTime(reader["TRAINREPORTDATE"]).ToString("yyyy-MM-dd"); if (reader["TRAINDAY"] != DBNull.Value) data.TRAINDAY = Convert.ToInt16(reader["TRAINDAY"]); if (reader["STATIONTIME"] != DBNull.Value) data.STATIONTIME = Convert.ToDateTime(reader["STATIONTIME"]).ToString("yyyy-MM-dd"); if (reader["ETA"] != DBNull.Value) data.ETA = Convert.ToDateTime(reader["ETA"]).ToString("yyyy-MM-dd"); data.PREVESSEL = Convert.ToString(reader["PREVESSEL"]); data.PREVOY = Convert.ToString(reader["PREVOY"]); if (reader["VESSELDAY"] != DBNull.Value) data.VESSELDAY = Convert.ToInt16(reader["VESSELDAY"]); if (reader["REPLACEDOCDATE"] != DBNull.Value) data.REPLACEDOCDATE = Convert.ToDateTime(reader["REPLACEDOCDATE"]).ToString("yyyy-MM-dd"); if (reader["CUSTOMDATE"] != DBNull.Value) data.CUSTOMDATE = Convert.ToDateTime(reader["CUSTOMDATE"]).ToString("yyyy-MM-dd"); if (reader["CUSTOMCLEARDATE"] != DBNull.Value) data.CUSTOMCLEARDATE = Convert.ToDateTime(reader["CUSTOMCLEARDATE"]).ToString("yyyy-MM-dd"); if (reader["SHENCODETIME"] != DBNull.Value) data.SHENCODETIME = Convert.ToDateTime(reader["SHENCODETIME"]).ToString("yyyy-MM-dd"); data.CUSTOMTRANSITCORP = Convert.ToString(reader["CUSTOMTRANSITCORP"]); data.SHENCODE = Convert.ToString(reader["SHENCODE"]); data.CODESTATUS = Convert.ToString(reader["CODESTATUS"]); if (reader["GIVECODETIME"] != DBNull.Value) data.GIVECODETIME = Convert.ToDateTime(reader["GIVECODETIME"]).ToString("yyyy-MM-dd"); if (reader["ARRIVEPLACETRANSIT"] != DBNull.Value) data.ARRIVEPLACETRANSIT = Convert.ToDateTime(reader["ARRIVEPLACETRANSIT"]).ToString("yyyy-MM-dd"); if (reader["SETOUTPLACETRANSIT"] != DBNull.Value) data.SETOUTPLACETRANSIT = Convert.ToDateTime(reader["SETOUTPLACETRANSIT"]).ToString("yyyy-MM-dd"); if (reader["TRANSITDAY"] != DBNull.Value) data.TRANSITDAY = Convert.ToInt16(reader["TRANSITDAY"]); data.RETRAINNO = Convert.ToString(reader["RETRAINNO"]); data.FRPLACETRANSITID = Convert.ToString(reader["FRPLACETRANSITID"]); data.FRPLACETRANSIT = Convert.ToString(reader["FRPLACETRANSIT"]); if (reader["ARRIVEFRPLACETRANSIT"] != DBNull.Value) data.ARRIVEFRPLACETRANSIT = Convert.ToDateTime(reader["ARRIVEFRPLACETRANSIT"]).ToString("yyyy-MM-dd"); if (reader["ARRIVALDATE"] != DBNull.Value) data.ARRIVALDATE = Convert.ToDateTime(reader["ARRIVALDATE"]).ToString("yyyy-MM-dd"); if (reader["RECTNDATE"] != DBNull.Value) data.RECTNDATE = Convert.ToDateTime(reader["RECTNDATE"]).ToString("yyyy-MM-dd"); data.RECTNTYPE = Convert.ToString(reader["RECTNTYPE"]); if (reader["ONROADDATE"] != DBNull.Value) data.ONROADDATE = Convert.ToDateTime(reader["ONROADDATE"]).ToString("yyyy-MM-dd"); data.ONROADPLACE = Convert.ToString(reader["ONROADPLACE"]); data.RETURNCTNCORP = Convert.ToString(reader["RETURNCTNCORP"]); if (reader["RETURNDATE"] != DBNull.Value) data.RETURNDATE = Convert.ToDateTime(reader["RETURNDATE"]).ToString("yyyy-MM-dd"); if (reader["RECEIVECTNDATE"] != DBNull.Value) data.RECEIVECTNDATE = Convert.ToDateTime(reader["RECEIVECTNDATE"]).ToString("yyyy-MM-dd"); data.RECEIVEYARD = Convert.ToString(reader["RECEIVEYARD"]); data.CTNSOURCE = Convert.ToString(reader["CTNSOURCE"]); data.CTNALL = Convert.ToString(reader["CTNALL"]); if (reader["CTNNUM"] != DBNull.Value) data.CTNNUM = Convert.ToInt16(reader["CTNNUM"]); data.CNTRNO = Convert.ToString(reader["CNTRNO"]); data.SEALNO = Convert.ToString(reader["SEALNO"]); data.FRSEALNO = Convert.ToString(reader["FRSEALNO"]); if (reader["CTNWEIGHT"] != DBNull.Value) data.CTNWEIGHT = Convert.ToDecimal(reader["CTNWEIGHT"]); if (reader["PKGS"] != DBNull.Value) data.PKGS = Convert.ToInt32(reader["PKGS"]); data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]); data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]); if (reader["KGS"] != DBNull.Value) data.KGS = Convert.ToDecimal(reader["KGS"]); if (reader["CBM"] != DBNull.Value) data.CBM = Convert.ToDecimal(reader["CBM"]); data.DOC = Convert.ToString(reader["DOC"]); data.SALEDEPT = Convert.ToString(reader["SALEDEPT"]); data.CREATETIME = Convert.ToDateTime(reader["CREATETIME"]); data.STLNAME = Convert.ToString(reader["STLNAME"]);//结算方式 if (reader["STLDATE"] != DBNull.Value) data.STLDATE = Convert.ToDateTime(reader["STLDATE"]).ToString("yyyy-MM-dd");//截单日期 //data.STLDATE = Convert.ToString(reader["STLDATE"]);//结算时间 data.OPERATORCODE = Convert.ToString(reader["OPERATORCODE"]);//客户操作代码 data.OPERATOR = Convert.ToString(reader["OPERATOR"]);//客户操作名称 data.OPERATOREMAIL = Convert.ToString(reader["OPERATOREMAIL"]);//操作电子邮箱 data.OPERATORTEL = Convert.ToString(reader["OPERATORTEL"]);//操作电话 data.OPERATORFAX = Convert.ToString(reader["OPERATORFAX"]);//操作传真 data.FINANCIALSTAFFCODE = Convert.ToString(reader["FINANCIALSTAFFCODE"]);//客户财务代码 data.FINANCIALSTAFF = Convert.ToString(reader["FINANCIALSTAFF"]);//客户财务名称 data.FINANCIALSTAFFEMAIL = Convert.ToString(reader["FINANCIALSTAFFEMAIL"]);//财务电子邮箱 data.FINANCIALSTAFFTEL = Convert.ToString(reader["FINANCIALSTAFFTEL"]);//财务电话 data.FINANCIALSTAFFFAX = Convert.ToString(reader["FINANCIALSTAFFFAX"]);//财务传真 data.ISVOU = Convert.ToBoolean(reader["ISVOU"]); data.VOUNO = Convert.ToString(reader["VOUNO"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.ORDERNO = Convert.ToString(reader["ORDERNO"]); data.SALECORPID = Convert.ToString(reader["SALECORPID"]); data.SALECORP = Convert.ToString(reader["SALECORP"]); if (reader["RETRAINDATE"] != DBNull.Value) data.RETRAINDATE = Convert.ToDateTime(reader["RETRAINDATE"]).ToString("yyyy-MM-dd"); // data.ISPRINTPR = Convert.ToString(reader["ISPRINTPR"]); if (reader["CNTR1"] != DBNull.Value) data.CNTR1 = Convert.ToInt32(reader["CNTR1"]);//箱型1 if (reader["CNTR2"] != DBNull.Value) data.CNTR2 = Convert.ToInt32(reader["CNTR2"]);//箱型2 if (reader["CNTR3"] != DBNull.Value) data.CNTR3 = Convert.ToInt32(reader["CNTR3"]);//箱型3 if (reader["CNTR4"] != DBNull.Value) data.CNTR4 = Convert.ToInt32(reader["CNTR4"]);//箱型4 if (reader["CNTR5"] != DBNull.Value) data.CNTR5 = Convert.ToInt32(reader["CNTR5"]);//箱型5 if (reader["CNTR6"] != DBNull.Value) data.CNTR6 = Convert.ToInt32(reader["CNTR6"]);//箱型6 if (reader["CNTR7"] != DBNull.Value) data.CNTR7 = Convert.ToInt32(reader["CNTR7"]);//箱型7 if (reader["CNTR8"] != DBNull.Value) data.CNTR8 = Convert.ToInt32(reader["CNTR8"]);//箱型8 if (reader["CNTR9"] != DBNull.Value) data.CNTR9 = Convert.ToInt32(reader["CNTR9"]);//箱型9 if (reader["CNTR10"] != DBNull.Value) data.CNTR10 = Convert.ToInt32(reader["CNTR10"]);//箱型10 if (reader["OTCNTR"] != DBNull.Value) data.OTCNTR = Convert.ToInt32(reader["OTCNTR"]);//其他箱型 if (reader["TEU"] != DBNull.Value) data.TEU = Convert.ToInt32(reader["TEU"]);//箱TEU decimal ttldr = 0; if (reader["TTLDR"] != DBNull.Value) ttldr = Convert.ToDecimal(reader["TTLDR"]); decimal ttlinvdr = 0; if (reader["TTLDR"] != DBNull.Value) ttlinvdr = Convert.ToDecimal(reader["TTLINVDR"]); if (ttlinvdr == 0) data.DRINVSTATUS = "未开票"; else if (ttlinvdr == ttldr) data.DRINVSTATUS = "已开票"; else if (ttlinvdr != ttldr) data.DRINVSTATUS = "部分开票"; decimal ttlduidr = 0; if (reader["TTLDUIDR"] != DBNull.Value) ttlduidr = Convert.ToDecimal(reader["TTLDUIDR"]); if (ttlduidr == 0) data.DRDUISTATUS = "未对账"; else if (ttlduidr == ttldr) data.DRDUISTATUS = "已对账"; else if (ttlduidr != ttldr) data.DRDUISTATUS = "部分对账"; #endregion headList.Add(data); } reader.Close(); } return headList; } public static string getfeestatus(string feestatusint) { var result = ""; if (feestatusint == "0:") { return "审核通过"; } else if (feestatusint == "1:") { return "录入状态"; } else if (feestatusint == "2:") { return "提交审核"; } else if (feestatusint == "8:") { return "部分结算"; } else if (feestatusint == "9:") { return "结算完毕"; } else if (feestatusint == "") { return "未录入"; } else if (feestatusint == "3:") { return "申请修改"; } else if (feestatusint == "4:") { return "申请删除"; } else if (feestatusint == "6:") { return "驳回提交"; } else if (feestatusint == "7:") { return "驳回申请"; } else if (feestatusint.IndexOf("9:") > -1) { return "部分结算"; } else if (feestatusint.IndexOf("8:") > -1) { return "部分结算"; } else if (feestatusint.IndexOf("0:") > -1) { return "部分审核"; } else if (feestatusint.IndexOf("2:") > -1) { return "部分提交"; } else if (feestatusint.IndexOf("6:") > -1) { return "驳回提交"; } else if (feestatusint.IndexOf("7:") > -1) { return "驳回申请"; } return result; } #endregion #region 明细表 static public List GetBodyTrackingList(string strCondition,bool isfirst) { var strSql = new StringBuilder(); if (!isfirst) { strSql.Append("SELECT TK_ID,BSNO,isnull(SORTNO,1) as SORTNO,TRAINNO,FRTRAINNO,INPUTBY,INPUTDATE,TRACKDATE,"); strSql.Append("CNTRNO,SEALNO,EFCNTR,ACTIVITY,LOCATION,LOCATIONID,DISTANCE,DISTANCEDEST,[REMARK] from op_railway_tracking"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" order by BSNO,SORTNO"); } else { strSql.Append("SELECT TK_ID,BSNO,SORTNO,TRAINNO,FRTRAINNO,INPUTBY,INPUTDATE,TRACKDATE,"); strSql.Append("CNTRNO,SEALNO,EFCNTR,ACTIVITY,LOCATION,LOCATIONID,DISTANCE,DISTANCEDEST,[REMARK] from ("); strSql.Append("SELECT TK_ID,BSNO,SORTNO,TRAINNO,FRTRAINNO,INPUTBY,INPUTDATE,TRACKDATE,"); strSql.Append("CNTRNO,SEALNO,EFCNTR,ACTIVITY,LOCATION,LOCATIONID,DISTANCE,DISTANCEDEST,[REMARK] "); strSql.Append(",row_number() over (partition by CNTRNO order by SORTNO desc) as rowno from op_railway_tracking"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" ) x where x.rowno=1"); } return SetBodyTrackingData(strSql); } private static List SetBodyTrackingData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpRailwayTracking data = new MsOpRailwayTracking(); #region Set DB data to Object data.TK_ID = Convert.ToString(reader["TK_ID"]);//编号 data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号 data.SORTNO = Convert.ToString(reader["SORTNO"]);// data.TRAINNO = Convert.ToString(reader["TRAINNO"]);// data.FRTRAINNO = Convert.ToString(reader["FRTRAINNO"]);// data.INPUTBY = Convert.ToString(reader["INPUTBY"]);// data.INPUTDATE = Convert.ToString(reader["INPUTDATE"]);// if (reader["TRACKDATE"] != DBNull.Value) data.TRACKDATE = Convert.ToDateTime(reader["TRACKDATE"]).ToString("yyyy-MM-dd"); // data.CNTRNO = Convert.ToString(reader["CNTRNO"]);// data.SEALNO = Convert.ToString(reader["SEALNO"]);// data.EFCNTR = Convert.ToString(reader["EFCNTR"]);// data.ACTIVITY = Convert.ToString(reader["ACTIVITY"]);// data.LOCATION = Convert.ToString(reader["LOCATION"]);// data.LOCATIONID = Convert.ToString(reader["LOCATIONID"]);// if (reader["DISTANCE"] != DBNull.Value) data.DISTANCE = Convert.ToDecimal(reader["DISTANCE"]); if (reader["DISTANCEDEST"] != DBNull.Value) data.DISTANCEDEST = Convert.ToDecimal(reader["DISTANCEDEST"]); data.REMARK = Convert.ToString(reader["REMARK"]);//备注 #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } public static DBResult SaveCtnDetail(string bsno, List bodyList, string userid) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { int TEUALL = 0; List cntrsumlist = new List(); if (bodyList != null) { var cmddelete = db.GetSqlStringCommand(@"delete from op_ctn where BSNO='" + bsno + "'"); db.ExecuteNonQuery(cmddelete, tran); foreach (var enumValue in bodyList) { int SIZE = int.Parse(enumValue.CTNALL.ToString().Trim().Substring(0, 2)); string CTN = enumValue.CTNALL.ToString().Trim().Substring(2); int TEU = 1; int iTEU = int.Parse(enumValue.CTNALL.ToString().Trim().Substring(0, 1)); if (iTEU == 4) { TEU = TEU * 2; } TEUALL = TEUALL + TEU; var cmdInsert = db.GetSqlStringCommand(@"insert into op_ctn (CTN_ID,BSNO,CTNCODE,[SIZE],CTN,CTNNUM,TEU,CTNALL,CNTRNO,SEALNO,PKGS,KGS,CBM,REMARK,KINDPKGS,TAREWEIGHT,GOODSNAME) values ('" + enumValue.CTN_ID + "','" + bsno + "','" + enumValue.CTNCODE + "','',''," + enumValue.CTNNUM + ",1,'" + enumValue.CTNALL + "','" + enumValue.CNTRNO + "','" + enumValue.SEALNO + "','" + enumValue.PKGS + "'," + enumValue.KGS + "," + enumValue.CBM + ",'" + enumValue.REMARK + "','" + enumValue.KINDPKGS + "'," + enumValue.TAREWEIGHT + ",'')"); db.ExecuteNonQuery(cmdInsert, tran); var isfind = false; cntrsumlist.ForEach(i => { if (i.CTNALL == enumValue.CTNALL) { i.CTNNUM = i.CTNNUM + 1; isfind = true; } }); if (!isfind) { var cntrsum = new CNTRSUM(); cntrsum.CTNALL = enumValue.CTNALL; cntrsum.CTNNUM = 1; cntrsumlist.Add(cntrsum); } } } T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); DataSet dsDisp = T_ALL_DA.GetAllSQL("select * from code_ctn_disp");// where CORPID = '" + strCORPID + "'"; var str = ""; int otctn = 0; foreach (var enumValue in cntrsumlist) { int j = 0; for (int i = 1; i < 11; i++) { if (dsDisp.Tables[0].Rows[0][i].ToString().Trim().Equals(enumValue.CTNALL)) { str = str + ",CNTR" + i + "=" + enumValue.CTNNUM; j = j + 1; break; } else { str = str + ",CNTR" + i + "=0" ; } } if (j == 0) { otctn = otctn + enumValue.CTNNUM; } } str = "update op_railway set OP=OP " + str + ",OTCNTR=" + otctn.ToString() + ",TEU="+ TEUALL.ToString() + " WHERE BSNO='" + bsno + "'"; bool bl2 = T_ALL_DA.GetExecuteSqlCommand(str); // 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 SaveTrackingDetail(string bsno, List bodyList, string user) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (bodyList != null) { foreach (var enumValue in bodyList) { if (enumValue.BSNO == "*" || enumValue.BSNO == "") { if (enumValue.INPUTDATE == "") enumValue.INPUTDATE = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (enumValue.TRACKDATE == "") enumValue.TRACKDATE = null; var cmdInsert = db.GetSqlStringCommand(@"insert into op_railway_tracking (TK_ID,BSNO,SORTNO,TRAINNO,FRTRAINNO,INPUTBY, INPUTDATE,TRACKDATE,CNTRNO,SEALNO,EFCNTR,ACTIVITY,LOCATION,LOCATIONID,DISTANCE,DISTANCEDEST,REMARK) values (newid(),'" + bsno + "'," + enumValue.SORTNO + ",'" + enumValue.TRAINNO + "','" + enumValue.FRTRAINNO + "','" + user + "','" + enumValue.INPUTDATE + "','" + enumValue.TRACKDATE + "','" + enumValue.CNTRNO + "','" + enumValue.SEALNO + "','" + enumValue.EFCNTR + "','" + enumValue.ACTIVITY + "','" + enumValue.LOCATION + "','" + enumValue.LOCATIONID + "'," + enumValue.DISTANCE + "," + enumValue.DISTANCEDEST + ",'" + enumValue.REMARK + "')"); db.ExecuteNonQuery(cmdInsert, tran); } else { if (enumValue.TRACKDATE == "") enumValue.TRACKDATE = null; var cmdUpdate = db.GetSqlStringCommand(@"update op_railway_tracking set BSNO=@BSNO,SORTNO=@SORTNO,TRAINNO=@TRAINNO ,FRTRAINNO=@FRTRAINNO,INPUTBY=@INPUTBY,INPUTDATE=@INPUTDATE,TRACKDATE=@TRACKDATE,CNTRNO=@CNTRNO,SEALNO=@SEALNO, EFCNTR=@EFCNTR,ACTIVITY=@ACTIVITY,LOCATION=@LOCATION,LOCATIONID=@LOCATIONID,DISTANCE=@DISTANCE,DISTANCEDEST=@DISTANCEDEST,REMARK=@REMARK where TK_ID=@TK_ID"); cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@TK_ID", DbType.String, enumValue.TK_ID); db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, enumValue.BSNO); db.AddInParameter(cmdUpdate, "@SORTNO", DbType.Int16, enumValue.SORTNO); db.AddInParameter(cmdUpdate, "@TRAINNO", DbType.String, enumValue.TRAINNO); db.AddInParameter(cmdUpdate, "@FRTRAINNO", DbType.String, enumValue.FRTRAINNO); db.AddInParameter(cmdUpdate, "@INPUTBY", DbType.String, enumValue.INPUTBY); db.AddInParameter(cmdUpdate, "@INPUTDATE", DbType.String, enumValue.INPUTDATE); db.AddInParameter(cmdUpdate, "@TRACKDATE", DbType.String, enumValue.TRACKDATE); db.AddInParameter(cmdUpdate, "@CNTRNO", DbType.String, enumValue.CNTRNO); db.AddInParameter(cmdUpdate, "@SEALNO", DbType.String, enumValue.SEALNO); db.AddInParameter(cmdUpdate, "@EFCNTR", DbType.String, enumValue.EFCNTR); db.AddInParameter(cmdUpdate, "@ACTIVITY", DbType.String, enumValue.ACTIVITY); db.AddInParameter(cmdUpdate, "@LOCATION", DbType.String, enumValue.LOCATION); db.AddInParameter(cmdUpdate, "@LOCATIONID", DbType.String, enumValue.LOCATIONID); db.AddInParameter(cmdUpdate, "@DISTANCE", DbType.Decimal, enumValue.DISTANCE); db.AddInParameter(cmdUpdate, "@DISTANCEDEST", DbType.Decimal, enumValue.DISTANCEDEST); db.AddInParameter(cmdUpdate, "@REMARK", DbType.String, enumValue.REMARK); 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 DeleteTrackingDetail(MsOpRailwayTracking bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (bodyList != null) { var cmddelete = db.GetSqlStringCommand(@"delete from op_railway_tracking where TK_ID='" + bodyList.TK_ID + "'"); db.ExecuteNonQuery(cmddelete, 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 DeleteDetail(string BSNO) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("delete from op_railway_bill where BSNO='" + BSNO + "'"); db.ExecuteNonQuery(cmdDelete, tran); var cmdDelete2 = db.GetSqlStringCommand("delete from op_railway_tracking where BSNO='" + BSNO + "'"); db.ExecuteNonQuery(cmdDelete2, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } #endregion #region 生成编码 public static String getCodeRule(string strRULENAME, int strRULETYPE, string strfield, string strETD, string strACCDATE, string strUserID, string strCompanyID) { //调用编码规则 if (strETD.Trim().IndexOf("0001") > -1) { strETD = ""; } string strRULEYEAR = ""; string strRULECONTENT = ""; string strCODENAME = ""; string strDEPTNO = ""; string strRULENOLENGTH = ""; int iRULENOLENGTH = 0; int inum = 0; string strCONTENT = ""; T_ALL_DA T_ALL_DA = new T_ALL_DA(); // DataSet ds = T_ALL_DA.GetAllSQL("select * from code_rule where RULENAME='" + strRULENAME.Trim() + "' and RULETYPE=" + strRULETYPE); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["ISSTARTUSING"].ToString().Trim().Equals("True")) { if (ds.Tables[0].Rows[0]["RULEDATE"].ToString().Trim().Equals("业务日期")) { if (strETD.Trim() == "") { return ""; } DateTime dETD = System.Convert.ToDateTime(strETD.Trim()); if (ds.Tables[0].Rows[0]["ISCLEAREDINEARLY"].ToString().Trim().Equals("True")) { if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4") { strRULEYEAR = dETD.ToString("yyyy") + dETD.ToString("MM"); } else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2") { strRULEYEAR = dETD.ToString("yy") + dETD.ToString("MM"); } } else { if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4") { strRULEYEAR = dETD.ToString("yyyy"); } else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2") { strRULEYEAR = dETD.ToString("yy"); } } } else if (ds.Tables[0].Rows[0]["RULEDATE"].ToString().Trim().Equals("会计期间")) { if (strACCDATE.Trim() == "") { return ""; } DateTime dACCDATE = System.Convert.ToDateTime(strACCDATE.Trim() + "-01"); if (ds.Tables[0].Rows[0]["ISCLEAREDINEARLY"].ToString().Trim().Equals("True")) { if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4") { strRULEYEAR = dACCDATE.ToString("yyyy") + dACCDATE.ToString("MM"); } else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2") { strRULEYEAR = dACCDATE.ToString("yy") + dACCDATE.ToString("MM"); } } else { if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4") { strRULEYEAR = dACCDATE.ToString("yyyy"); } else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2") { strRULEYEAR = dACCDATE.ToString("yy"); } } } else if (ds.Tables[0].Rows[0]["RULEDATE"].ToString().Trim().Equals("自然月份")) { DateTime fwqdate = System.Convert.ToDateTime(T_ALL_DA.GetStrSQL("fwqdate", "select fwqdate=getdate()")); if (ds.Tables[0].Rows[0]["ISCLEAREDINEARLY"].ToString().Trim().Equals("True")) { if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4") { strRULEYEAR = fwqdate.ToString("yyyy") + fwqdate.ToString("MM"); } else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2") { strRULEYEAR = fwqdate.ToString("yy") + fwqdate.ToString("MM"); } } else { if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "4") { strRULEYEAR = fwqdate.ToString("yyyy"); } else if (ds.Tables[0].Rows[0]["RULEYEAR"].ToString().Trim() == "2") { strRULEYEAR = fwqdate.ToString("yy"); } } } // if (ds.Tables[0].Rows[0]["ISCOMPANY"].ToString().Trim().Equals("True")) { strCODENAME = T_ALL_DA.GetStrSQL("CODENAME", "select CODENAME from [company] where GID=(select top 1 COMPANYID from user_company where USERID='" + strUserID.Trim() + "')"); } if (ds.Tables[0].Rows[0]["ISDEPT"].ToString().Trim().Equals("True")) { strDEPTNO = T_ALL_DA.GetStrSQL("DEPTNO", "select DEPTNO from [sys_dept] where LINKID=(select top 1 COMPANYID from user_company where USERID='" + strUserID.Trim() + "')"); } strRULECONTENT = ds.Tables[0].Rows[0]["RULECONTENT"].ToString().Trim(); strRULECONTENT = strRULECONTENT.Replace("《COMPANY》", strCODENAME); strRULECONTENT = strRULECONTENT.Replace("《DEPT》", strDEPTNO); // iRULENOLENGTH = int.Parse(ds.Tables[0].Rows[0]["RULENOLENGTH"].ToString().Trim()); strCONTENT = strRULECONTENT + strRULEYEAR; //获取分公司的票号头字符 string tempBANKSHEAD = T_ALL_DA.GetStrSQL("BANKSHEAD", "SELECT TOP 1 BANKSHEAD FROM company WHERE GID='" + strCompanyID.Trim() + "'"); strCONTENT = tempBANKSHEAD + strCONTENT; // strRULENOLENGTH = T_ALL_DA.GetStrSQL(strfield, "select top 1 " + strfield + " from [op_railway] where " + strfield + " like '" + strCONTENT.Trim() + "%' order by " + strfield + " DESC"); if (strRULENOLENGTH == "") { strRULENOLENGTH = "1"; } else { strRULENOLENGTH = strRULENOLENGTH.Replace(strCONTENT.Trim(), ""); inum = int.Parse(strRULENOLENGTH.Trim()); inum = inum + 1; strRULENOLENGTH = inum.ToString(); } int j = iRULENOLENGTH - strRULENOLENGTH.Length; for (int i = 1; i <= j; i++) { strRULENOLENGTH = "0" + strRULENOLENGTH; } // strCONTENT = strCONTENT + strRULENOLENGTH; return strCONTENT; } } } return ""; } #endregion #region 判断编码是否有重复 static public int GetRdCount(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" Count(BSNO) AS CT "); strSql.Append(" from op_railway "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } 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; } #endregion #region 判断是否有FEE public static bool GetFeeCount(string BSNO) { var isfee = false; String strSql = "Select count(*) as count from ch_fee where BSNO='" + BSNO + "'"; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql)) { while (reader.Read()) { var evData = Convert.ToInt32(reader["count"]); if (evData > 0) { isfee = true; }; } reader.Close(); } return isfee; } #endregion #region 委托业务费用加锁 /// /// 委托业务费用加锁 /// /// 委托业务编号 /// 值等于1表示加锁成功 值不等于1表示加锁失败 public static int LockFeeStatus(string tempBSNO) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@BSNO",SqlDbType.VarChar,100) }; parms[0].Value = tempBSNO; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, "UPDATE op_railway SET FEESTATUS = 1 WHERE BSNO = @BSNO", parms); sqlTran.Commit(); iResult = 1;//状态为1表示更新成功 } catch (Exception execError) { iResult = -1;//有异常,更新失败 sqlTran.Rollback(); iResult = -2;//更新异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 委托业务费用解锁 /// /// 委托业务费用解锁 /// /// 委托业务编号 /// 值等于1表示解锁成功 值不等于1表示解锁失败 public static int UnLockFeeStatus(string tempBSNO) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@BSNO",SqlDbType.VarChar,100) }; parms[0].Value = tempBSNO; SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, "UPDATE op_railway SET FEESTATUS =0 WHERE BSNO = @BSNO", parms); sqlTran.Commit(); iResult = 1;//状态为1表示更新成功 } catch (Exception execError) { iResult = -1;//有异常,更新失败 sqlTran.Rollback(); iResult = -2;//更新异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region Rang权限范围 public static string GetRangDAStr(string tb, string userid, string username, string companyid) { string str = ""; var strSql = new StringBuilder(); strSql.Append("SELECT"); strSql.Append(" VISIBLERANGE,OPERATERANGE"); strSql.Append(" from VW_User_Authority"); strSql.Append(" where [NAME]='modOpRailwayOpwt' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { visiblerange = Convert.ToString(reader["VISIBLERANGE"]); operaterange = Convert.ToString(reader["OPERATERANGE"]); break; } reader.Close(); } if (visiblerange == "4") { str = " (OP='" + username + "' OR SALE='" + username + "' OR CUSTSERVICE='" + username + "')"; } else if (visiblerange == "3") { str = " (OP='" + username + "' OR SALE='" + username + "' OR CUSTSERVICE='" + username + "')"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " (OP in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "'))" + " )"; } else if (visiblerange == "1") { str = " (OP in (select showname from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "'))" + " )"; } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #endregion #region 运行报单 static public List GetBillList(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("AS_ID,BSNO,CUSTNO,INPUTBY,CREATETIME,SHIPPERID,CONSIGNEEID,SHIPPER,CONSIGNEE,PLACELOADENAME,DESTINATIONENAME,SHENCODE,SHIPPERSTATEMENT,NOBINDINGRECORD,"); strSql.Append("CUSTOMERRECORD,SHIPPERFILES,OTHERFIELD1,PLACETRANSITENAME,NOPKGS,PKGS,KGS,SEALNO,LOADER,WEIGHTMETHODS,CUSTCONTRACTNODATE,CUSTCONTRACTNO,ARRIVALDATE,DESCRIPTION,GOODSNAME,GROSSWEIGHT,TOTALNO,CNTRTOTAL,PLACERECEIPTID"); strSql.Append(",(SELECT TOP 1 TRAINNO FROM op_railway_bill_train WHERE BSNO=op_railway_bill.AS_ID ) as TRAINNO "); strSql.Append(" from op_railway_bill "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } // var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by CREATETIME desc"); } return SetBillData(strSql); } static public MsOpRailwayBill GetBillData(string condition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("AS_ID,BSNO,CUSTNO,INPUTBY,CREATETIME,SHIPPERID,CONSIGNEEID,SHIPPER,CONSIGNEE,PLACELOADENAME,DESTINATIONENAME,SHENCODE,SHIPPERSTATEMENT,NOBINDINGRECORD,"); strSql.Append("CUSTOMERRECORD,SHIPPERFILES,OTHERFIELD1,PLACETRANSITENAME,NOPKGS,PKGS,KGS,SEALNO,LOADER,WEIGHTMETHODS,CUSTCONTRACTNODATE,CUSTCONTRACTNO,ARRIVALDATE,DESCRIPTION,GOODSNAME,GROSSWEIGHT,TOTALNO,CNTRTOTAL"); strSql.Append(",(SELECT TOP 1 TRAINNO FROM op_railway_bill_train WHERE BSNO=op_railway_bill.AS_ID ) as TRAINNO "); strSql.Append(" from op_railway_bill "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition); } var list = SetBillData(strSql); if (list.Count > 0) return list[0]; return new MsOpRailwayBill(); } private static List SetBillData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpRailwayBill data = new MsOpRailwayBill(); #region Set DB data to Object data.AS_ID = Convert.ToString(reader["AS_ID"]);//业务编号 data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号 data.CUSTNO = Convert.ToString(reader["CUSTNO"]);//主提单号 data.INPUTBY = Convert.ToString(reader["INPUTBY"]);//主提单号 data.CREATETIME = Convert.ToDateTime(reader["CREATETIME"]);//分提单号 data.TRAINNO = Convert.ToString(reader["TRAINNO"]);//分提单号 data.SHIPPERID = Convert.ToString(reader["SHIPPERID"]);//发货人 data.CONSIGNEEID = Convert.ToString(reader["CONSIGNEEID"]);//收货人 data.SHIPPER = Convert.ToString(reader["SHIPPER"]);//发货人内容 data.CONSIGNEE = Convert.ToString(reader["CONSIGNEE"]);//收货人内容 data.PLACELOADENAME = Convert.ToString(reader["PLACELOADENAME"]);// 业务状态 data.DESTINATIONENAME = Convert.ToString(reader["DESTINATIONENAME"]);// 费用状态 data.SHENCODE = Convert.ToString(reader["SHENCODE"]);//船名 data.SHIPPERSTATEMENT = Convert.ToString(reader["SHIPPERSTATEMENT"]);//航次 data.NOBINDINGRECORD = Convert.ToString(reader["NOBINDINGRECORD"]);//装货港代码 data.CUSTOMERRECORD = Convert.ToString(reader["CUSTOMERRECORD"]);//装货港 data.SHIPPERFILES = Convert.ToString(reader["SHIPPERFILES"]);//卸货港代码 data.OTHERFIELD1 = Convert.ToString(reader["OTHERFIELD1"]);//卸货港 data.PLACETRANSITENAME = Convert.ToString(reader["PLACETRANSITENAME"]);//集装箱大写 data.NOPKGS = Convert.ToString(reader["NOPKGS"]);//件数包装 data.PKGS = Convert.ToString(reader["PKGS"]);//件数包装 data.KGS = Convert.ToString(reader["KGS"]);//件数包装 data.DESCRIPTION = Convert.ToString(reader["DESCRIPTION"]);//货物描述 data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);//品名 data.GROSSWEIGHT = Convert.ToString(reader["GROSSWEIGHT"]);//货物毛重 data.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]);//集装箱全部内容 data.TOTALNO = Convert.ToString(reader["TOTALNO"]);//件数大写 data.SEALNO = Convert.ToString(reader["SEALNO"]);//件数大写 data.LOADER = Convert.ToString(reader["LOADER"]);//件数大写 data.WEIGHTMETHODS = Convert.ToString(reader["WEIGHTMETHODS"]);//件数大写 data.CUSTCONTRACTNODATE = Convert.ToString(reader["CUSTCONTRACTNODATE"]); data.CUSTCONTRACTNO = Convert.ToString(reader["CUSTCONTRACTNO"]); data.ARRIVALDATE = Convert.ToString(reader["ARRIVALDATE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } static public List GetBodyTrainList(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT GID,[BSNO],isnull(SORTNO,1) as SORTNO,TRAINNO,CARRIER,KGS,SHAFTNUM,TRAINKGS,TANKTYPE,AFTERKGS,AFTERPKGS,[REMARK] from op_railway_bill_train"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" order by BSNO,SORTNO"); return SetBodyTrainData(strSql); } private static List SetBodyTrainData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpRailwayBillTrain data = new MsOpRailwayBillTrain(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]);//编号 data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号 data.SORTNO = Convert.ToString(reader["SORTNO"]);//箱型代码 data.TRAINNO = Convert.ToString(reader["TRAINNO"]);//尺寸 data.CARRIER = Convert.ToString(reader["CARRIER"]);//箱型 if (reader["KGS"] != DBNull.Value) data.KGS = Convert.ToDecimal(reader["KGS"]);//箱量 if (reader["SHAFTNUM"] != DBNull.Value) data.SHAFTNUM = Convert.ToInt32(reader["SHAFTNUM"]);//TEU if (reader["TRAINKGS"] != DBNull.Value) data.TRAINKGS = Convert.ToDecimal(reader["TRAINKGS"]);//重量(货重) if (reader["AFTERKGS"] != DBNull.Value) data.AFTERKGS = Convert.ToDecimal(reader["AFTERKGS"]);//尺码 if (reader["AFTERPKGS"] != DBNull.Value) data.AFTERKGS = Convert.ToInt32(reader["AFTERPKGS"]);//尺码 data.REMARK = Convert.ToString(reader["REMARK"]);//备注 data.TANKTYPE = Convert.ToString(reader["TANKTYPE"]);//包装类型 #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } static public List GetBodyRoadList(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT GID,[BSNO],isnull(SORTNO,1) as SORTNO,CARRIER,FRSTATION,FRSTATIONID,TOSTATION,TOSTATIONID,[REMARK] from op_railway_bill_road"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" order by BSNO,SORTNO"); return SetBodyRoadData(strSql); } private static List SetBodyRoadData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpRailwayBillRoad data = new MsOpRailwayBillRoad(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]);//编号 data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号 data.SORTNO = Convert.ToString(reader["SORTNO"]);//箱型代码 data.CARRIER = Convert.ToString(reader["CARRIER"]);//箱型 data.TOSTATION = Convert.ToString(reader["TOSTATION"]);//包装类型 data.TOSTATIONID = Convert.ToString(reader["TOSTATIONID"]);//包装类型 data.FRSTATION = Convert.ToString(reader["FRSTATION"]);//包装类型 data.FRSTATIONID = Convert.ToString(reader["FRSTATIONID"]);//包装类型 data.REMARK = Convert.ToString(reader["REMARK"]);//备注 #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } public static DBResult SaveBillDetail(string bsno, List bodyList, List bodyroadList, string userid) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (bodyList != null) { var cmddelete = db.GetSqlStringCommand(@"delete from op_railway_bill_train where BSNO='" + bsno + "'"); db.ExecuteNonQuery(cmddelete, tran); foreach (var enumValue in bodyList) { // var cmdInsert = db.GetSqlStringCommand(@"insert into op_railway_bill_train (GID,BSNO,SORTNO,TRAINNO,CARRIER,KGS,SHAFTNUM,TRAINKGS,TANKTYPE,AFTERKGS,AFTERPKGS,REMARK) values (newid(),'" + bsno + "'," + enumValue.SORTNO + ",'" + enumValue.TRAINNO + "','" + enumValue.CARRIER + "'," + enumValue.KGS + "," + enumValue.SHAFTNUM + "," + enumValue.TRAINKGS + ",'" + enumValue.TANKTYPE + "'," + enumValue.AFTERKGS + "," + enumValue.AFTERPKGS + ",'" + enumValue.REMARK + "')"); db.ExecuteNonQuery(cmdInsert, tran); } } if (bodyroadList != null) { var cmddelete = db.GetSqlStringCommand(@"delete from op_railway_bill_road where BSNO='" + bsno + "'"); db.ExecuteNonQuery(cmddelete, tran); foreach (var enumValue in bodyroadList) { // var cmdInsert = db.GetSqlStringCommand(@"insert into op_railway_bill_road (GID,BSNO,SORTNO,CARRIER,FRSTATION,FRSTATIONID,TOSTATION,TOSTATIONID,REMARK) values (newid(),'" + bsno + "'," + enumValue.SORTNO + ",'" + enumValue.CARRIER + "','" + enumValue.FRSTATION + "','" + enumValue.FRSTATIONID + "','" + enumValue.TOSTATION + "','" + enumValue.TOSTATIONID + "','" + enumValue.REMARK + "')"); 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 GetPortRefList(string strCondition,string sort=null,bool isall=false) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" GID,PORTID,PORTENAME,PORTCNAME,REMARKS,(PORTENAME+'-'+PORTCNAME) as CODEANDNAME,"); strSql.Append(" PROVINCE,CITYENAME,CITYCNAME,PORTDETAIL,RAILBUREAU,DTALSHANKOU,DTMANZHOULI,DTERLIAN,"); strSql.Append("DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,"); strSql.Append("IS48,IS50,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,PORTTYPE,ISBORDERPORT,BORDERPORTID"); strSql.Append(",(CASE PORTTYPE WHEN 'F' THEN '境外' ELSE '境内' END) PORTTYPEREF"); strSql.Append(" from code_railwayport "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); if (isall != true) strSql.Append(" and ISOFTEN=1 "); } else { if (isall != true) strSql.Append(" where ISOFTEN=1 "); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by PORTENAME"); } return SetPortRefData(strSql); } static public RailWayPortModel GetPortData(string condition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" GID,PORTID,PORTENAME,PORTCNAME,REMARKS,(PORTENAME+'-'+PORTCNAME) as CODEANDNAME,"); strSql.Append(" PROVINCE,CITYENAME,CITYCNAME,PORTDETAIL,RAILBUREAU,DTALSHANKOU,DTMANZHOULI,DTERLIAN,"); strSql.Append("DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,"); strSql.Append("IS48,IS50,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,PORTTYPE,ISBORDERPORT,BORDERPORTID"); strSql.Append(",(CASE PORTTYPE WHEN 'F' THEN '境外' ELSE '境内' END) PORTTYPEREF"); strSql.Append(" from code_railwayport "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition); } strSql.Append(" order by PORTENAME"); var list = SetPortRefData(strSql); if (list.Count > 0) { return list[0]; } return new RailWayPortModel(); } private static List SetPortRefData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { RailWayPortModel data = new RailWayPortModel(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.PORTID = Convert.ToString(reader["PORTID"]); data.PORTENAME = Convert.ToString(reader["PORTENAME"]); data.PORTCNAME = Convert.ToString(reader["PORTCNAME"]); data.CODEANDNAME = Convert.ToString(reader["CODEANDNAME"]); data.PROVINCE = Convert.ToString(reader["PROVINCE"]); data.CITYENAME = Convert.ToString(reader["CITYENAME"]); data.CITYCNAME = Convert.ToString(reader["CITYCNAME"]); data.PORTDETAIL = Convert.ToString(reader["PORTDETAIL"]); data.RAILBUREAU = Convert.ToString(reader["RAILBUREAU"]); if (reader["DTALSHANKOU"] != DBNull.Value) data.DTALSHANKOU = Convert.ToDecimal(reader["DTALSHANKOU"]); if (reader["DTMANZHOULI"] != DBNull.Value) data.DTMANZHOULI = Convert.ToDecimal(reader["DTMANZHOULI"]); if (reader["DTERLIAN"] != DBNull.Value) data.DTERLIAN = Convert.ToDecimal(reader["DTERLIAN"]); if (reader["DTSUIFENHE"] != DBNull.Value) data.DTSUIFENHE = Convert.ToDecimal(reader["DTSUIFENHE"]); if (reader["DTDANDONG"] != DBNull.Value) data.DTDANDONG = Convert.ToDecimal(reader["DTDANDONG"]); if (reader["DTPINGXIANG"] != DBNull.Value) data.DTPINGXIANG = Convert.ToDecimal(reader["DTPINGXIANG"]); if (reader["DTHORGOS"] != DBNull.Value) data.DTHORGOS = Convert.ToDecimal(reader["DTHORGOS"]); if (reader["ISOFTEN"] != DBNull.Value) data.ISOFTEN = Convert.ToBoolean(reader["ISOFTEN"]); data.WORKPOWER = Convert.ToString(reader["WORKPOWER"]); if (reader["IS20"] != DBNull.Value) data.IS20 = Convert.ToBoolean(reader["IS20"]); if (reader["IS40"] != DBNull.Value) data.IS40 = Convert.ToBoolean(reader["IS40"]); if (reader["IS48"] != DBNull.Value) data.IS48 = Convert.ToBoolean(reader["IS48"]); if (reader["IS50"] != DBNull.Value) data.IS50 = Convert.ToBoolean(reader["IS50"]); if (reader["ISTANK"] != DBNull.Value) data.ISTANK = Convert.ToBoolean(reader["ISTANK"]); if (reader["ISBULK"] != DBNull.Value) data.ISBULK = Convert.ToBoolean(reader["ISBULK"]); if (reader["ISFLAT"] != DBNull.Value) data.ISFLAT = Convert.ToBoolean(reader["ISFLAT"]); if (reader["ISDSCAR"] != DBNull.Value) data.ISDSCAR = Convert.ToBoolean(reader["ISDSCAR"]); data.REMARKS = Convert.ToString(reader["REMARKS"]); if (reader["ISCN"] != DBNull.Value) data.ISCN = Convert.ToBoolean(reader["ISCN"]); data.PORTTYPE = Convert.ToString(reader["PORTTYPE"]); data.PORTTYPEREF = Convert.ToString(reader["PORTTYPEREF"]); data.BORDERPORTID = Convert.ToString(reader["BORDERPORTID"]); if (reader["ISBORDERPORT"] != DBNull.Value) data.ISBORDERPORT = Convert.ToBoolean(reader["ISBORDERPORT"]); #endregion headList.Add(data); } reader.Close(); } RailWayPortModel Adata = new RailWayPortModel(); #region Set DB data to Object Adata.GID =""; Adata.PORTID =""; Adata.PORTENAME =""; Adata.PORTCNAME =""; Adata.CODEANDNAME =""; Adata.PROVINCE =""; Adata.CITYENAME =""; Adata.CITYCNAME =""; Adata.PORTDETAIL = ""; Adata.RAILBUREAU = ""; Adata.DTALSHANKOU =0; Adata.DTMANZHOULI = 0; Adata.DTERLIAN =0; Adata.DTSUIFENHE =0; Adata.DTDANDONG =0; Adata.DTPINGXIANG =0; Adata.DTHORGOS =0; Adata.ISOFTEN =false; Adata.WORKPOWER =""; Adata.IS20 = false; Adata.IS40 = false; Adata.IS48 = false; Adata.IS50 = false; Adata.ISTANK = false; Adata.ISBULK = false; Adata.ISFLAT = false; Adata.ISDSCAR = false; Adata.REMARKS =""; Adata.ISCN = false; Adata.PORTTYPE =""; Adata.PORTTYPEREF = ""; Adata.BORDERPORTID =""; Adata.ISBORDERPORT =false; #endregion headList.Add(Adata); return headList; } public static DBResult DeletePort(string GID) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (GID != "") { var cmdDelete = db.GetSqlStringCommand("delete from code_railwayport where GID='" + GID + "'"); db.ExecuteNonQuery(cmdDelete, tran); } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "删除成功"; return result; } static public List GetAllPortRefList(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" GID,PORTID,PORTENAME,PORTCNAME,PORTRNAME,REMARKS,(PORTENAME+'-'+PORTCNAME) as CODEANDNAME,"); strSql.Append(" COUNTRY,COUNTRYECODE,COUNTRYRCODE,COUNTRYCODE,COUNTRYENAME,COUNTRYRNAME,CITYENAME,CITYCNAME,PORTDETAIL,"); strSql.Append("ADDR,RAILBUREAU,DTALSHANKOU,DTMANZHOULI,DTERLIAN,"); strSql.Append("DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,"); strSql.Append("IS48,IS50,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,'T' PORTTYPE,ISBORDERPORT,BORDERPORTID"); strSql.Append(",'境外' PORTTYPEREF"); strSql.Append(" from code_railway_frport where ISOFTEN=1 "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" AND " + strCondition); } strSql.Append("UNION SELECT "); strSql.Append(" GID,PORTID,PORTENAME,PORTCNAME,'' PORTRNAME,REMARKS,(PORTENAME+'-'+PORTCNAME) as CODEANDNAME,"); strSql.Append(" '' COUNTRY,'' COUNTRYECODE,'' COUNTRYRCODE,'' COUNTRYCODE,'' COUNTRYENAME,'' COUNTRYRNAME,CITYENAME,CITYCNAME,PORTDETAIL,"); strSql.Append(" '' ADDR,RAILBUREAU,DTALSHANKOU,DTMANZHOULI,DTERLIAN,"); strSql.Append("DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,"); strSql.Append("IS48,IS50,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,'F' PORTTYPE,ISBORDERPORT,BORDERPORTID"); strSql.Append(",'境内' PORTTYPEREF"); strSql.Append(" from code_railwayport where ISOFTEN=1 "); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by PORTENAME"); } return SetFrPortRefData(strSql); } static public List GetFrPortRefList(string strCondition, string sort = null, bool isall = false) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" GID,PORTID,PORTENAME,PORTCNAME,PORTRNAME,REMARKS,(PORTENAME+'-'+PORTCNAME) as CODEANDNAME,"); strSql.Append(" COUNTRY,COUNTRYECODE,COUNTRYRCODE,COUNTRYCODE,COUNTRYENAME,COUNTRYRNAME,CITYENAME,CITYCNAME,PORTDETAIL,"); strSql.Append("ADDR,RAILBUREAU,DTALSHANKOU,DTMANZHOULI,DTERLIAN,"); strSql.Append("DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,"); strSql.Append("IS48,IS50,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,PORTTYPE,ISBORDERPORT,BORDERPORTID"); strSql.Append(",(CASE PORTTYPE WHEN 'F' THEN '境外' ELSE '境内' END) PORTTYPEREF"); strSql.Append(" from code_railway_frport "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); if (isall != true) strSql.Append(" and ISOFTEN=1 "); } else { if (isall != true) strSql.Append(" where ISOFTEN=1 "); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by PORTENAME"); } return SetFrPortRefData(strSql); } static public RailWayFrPortModel GetFrPortData(string condition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" GID,PORTID,PORTENAME,PORTCNAME,PORTRNAME,REMARKS,(PORTENAME+'-'+PORTCNAME) as CODEANDNAME,"); strSql.Append(" COUNTRY,COUNTRYECODE,COUNTRYRCODE,COUNTRYCODE,COUNTRYENAME,COUNTRYRNAME,CITYENAME,CITYCNAME,PORTDETAIL,"); strSql.Append("ADDR,RAILBUREAU,DTALSHANKOU,DTMANZHOULI,DTERLIAN,"); strSql.Append("DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,"); strSql.Append("IS48,IS50,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,PORTTYPE,ISBORDERPORT,BORDERPORTID"); strSql.Append(",(CASE PORTTYPE WHEN 'F' THEN '境外' ELSE '境内' END) PORTTYPEREF"); strSql.Append(" from code_railway_frport "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition); } strSql.Append(" order by PORTENAME"); var list = SetFrPortRefData(strSql); if (list.Count > 0) { return list[0]; } return new RailWayFrPortModel(); } private static List SetFrPortRefData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { RailWayFrPortModel data = new RailWayFrPortModel(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.PORTID = Convert.ToString(reader["PORTID"]); data.PORTENAME = Convert.ToString(reader["PORTENAME"]); data.PORTCNAME = Convert.ToString(reader["PORTCNAME"]); data.PORTRNAME = Convert.ToString(reader["PORTRNAME"]); data.CODEANDNAME = Convert.ToString(reader["CODEANDNAME"]); data.COUNTRY = Convert.ToString(reader["COUNTRY"]); data.COUNTRYECODE = Convert.ToString(reader["COUNTRYECODE"]); data.COUNTRYRCODE = Convert.ToString(reader["COUNTRYRCODE"]); data.COUNTRYCODE = Convert.ToString(reader["COUNTRYCODE"]); data.COUNTRYENAME = Convert.ToString(reader["COUNTRYENAME"]); data.COUNTRYRNAME = Convert.ToString(reader["COUNTRYRNAME"]); data.CITYENAME = Convert.ToString(reader["CITYENAME"]); data.CITYCNAME = Convert.ToString(reader["CITYCNAME"]); data.ADDR = Convert.ToString(reader["ADDR"]); data.PORTDETAIL = Convert.ToString(reader["PORTDETAIL"]); data.RAILBUREAU = Convert.ToString(reader["RAILBUREAU"]); if (reader["DTALSHANKOU"] != DBNull.Value) data.DTALSHANKOU = Convert.ToDecimal(reader["DTALSHANKOU"]); if (reader["DTMANZHOULI"] != DBNull.Value) data.DTMANZHOULI = Convert.ToDecimal(reader["DTMANZHOULI"]); if (reader["DTERLIAN"] != DBNull.Value) data.DTERLIAN = Convert.ToDecimal(reader["DTERLIAN"]); if (reader["DTSUIFENHE"] != DBNull.Value) data.DTSUIFENHE = Convert.ToDecimal(reader["DTSUIFENHE"]); if (reader["DTDANDONG"] != DBNull.Value) data.DTDANDONG = Convert.ToDecimal(reader["DTDANDONG"]); if (reader["DTPINGXIANG"] != DBNull.Value) data.DTPINGXIANG = Convert.ToDecimal(reader["DTPINGXIANG"]); if (reader["DTHORGOS"] != DBNull.Value) data.DTHORGOS = Convert.ToDecimal(reader["DTHORGOS"]); if (reader["ISOFTEN"] != DBNull.Value) data.ISOFTEN = Convert.ToBoolean(reader["ISOFTEN"]); data.WORKPOWER = Convert.ToString(reader["WORKPOWER"]); if (reader["IS20"] != DBNull.Value) data.IS20 = Convert.ToBoolean(reader["IS20"]); if (reader["IS40"] != DBNull.Value) data.IS40 = Convert.ToBoolean(reader["IS40"]); if (reader["IS48"] != DBNull.Value) data.IS48 = Convert.ToBoolean(reader["IS48"]); if (reader["IS50"] != DBNull.Value) data.IS50 = Convert.ToBoolean(reader["IS50"]); if (reader["ISTANK"] != DBNull.Value) data.ISTANK = Convert.ToBoolean(reader["ISTANK"]); if (reader["ISBULK"] != DBNull.Value) data.ISBULK = Convert.ToBoolean(reader["ISBULK"]); if (reader["ISFLAT"] != DBNull.Value) data.ISFLAT = Convert.ToBoolean(reader["ISFLAT"]); if (reader["ISDSCAR"] != DBNull.Value) data.ISDSCAR = Convert.ToBoolean(reader["ISDSCAR"]); data.REMARKS = Convert.ToString(reader["REMARKS"]); if (reader["ISCN"] != DBNull.Value) data.ISCN = Convert.ToBoolean(reader["ISCN"]); data.PORTTYPE = Convert.ToString(reader["PORTTYPE"]); data.PORTTYPEREF = Convert.ToString(reader["PORTTYPEREF"]); data.BORDERPORTID = Convert.ToString(reader["BORDERPORTID"]); if (reader["ISBORDERPORT"] != DBNull.Value) data.ISBORDERPORT = Convert.ToBoolean(reader["ISBORDERPORT"]); #endregion headList.Add(data); } reader.Close(); } RailWayFrPortModel Adata = new RailWayFrPortModel(); #region Set DB data to Object Adata.GID = ""; Adata.PORTID = ""; Adata.PORTENAME = ""; Adata.PORTCNAME = ""; Adata.CODEANDNAME = ""; Adata.COUNTRY = ""; Adata.COUNTRYECODE = ""; Adata.COUNTRYRCODE = ""; Adata.COUNTRYCODE = ""; Adata.COUNTRYENAME = ""; Adata.COUNTRYRNAME = ""; Adata.ADDR = ""; Adata.CITYENAME = ""; Adata.CITYCNAME = ""; Adata.PORTDETAIL = ""; Adata.RAILBUREAU = ""; Adata.DTALSHANKOU = 0; Adata.DTMANZHOULI = 0; Adata.DTERLIAN = 0; Adata.DTSUIFENHE = 0; Adata.DTDANDONG = 0; Adata.DTPINGXIANG = 0; Adata.DTHORGOS = 0; Adata.ISOFTEN = false; Adata.WORKPOWER = ""; Adata.IS20 = false; Adata.IS40 = false; Adata.IS48 = false; Adata.IS50 = false; Adata.ISTANK = false; Adata.ISBULK = false; Adata.ISFLAT = false; Adata.ISDSCAR = false; Adata.REMARKS = ""; Adata.ISCN = false; Adata.PORTTYPE = ""; Adata.PORTTYPEREF = ""; Adata.BORDERPORTID = ""; Adata.ISBORDERPORT = false; #endregion headList.Add(Adata); return headList; } public static DBResult UpPortOffen(List bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdUpdate = db.GetSqlStringCommand( @"update code_railwayport set ISOFTEN=@ISOFTEN where GID=@GID "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); if (enumValue.ISOFTEN==true) db.AddInParameter(cmdUpdate, "@ISOFTEN", DbType.Boolean,false); else db.AddInParameter(cmdUpdate, "@ISOFTEN", DbType.Boolean,true); 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 UpFrPortOffen(List bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdUpdate = db.GetSqlStringCommand( @"update code_railway_frport set ISOFTEN=@ISOFTEN where GID=@GID "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); if (enumValue.ISOFTEN==true) db.AddInParameter(cmdUpdate, "@ISOFTEN", DbType.Boolean,false); else db.AddInParameter(cmdUpdate, "@ISOFTEN", DbType.Boolean,true); 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 bool ImportFrPortData(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 { foreach (DataRow row in table.Rows) { var custName = Convert.ToString(row["英文站名"]); if (custName != "") { #region 托单数据生成 const string insertSql = @"insert into code_railway_frport (GID,PORTID,PORTENAME,PORTRNAME,PORTCNAME,COUNTRY,COUNTRYECODE,COUNTRYRCODE,COUNTRYCODE, COUNTRYENAME,COUNTRYRNAME,CITYENAME,CITYCNAME,RAILBUREAU,ADDR,DTALSHANKOU,DTMANZHOULI, DTERLIAN,DTSUIFENHE,DTDANDONG,DTPINGXIANG,DTHORGOS,ISOFTEN,WORKPOWER,IS20,IS40,IS48,IS50 ,ISTANK,ISBULK,ISFLAT,ISDSCAR,REMARKS,ISCN,PORTTYPE,ISBORDERPORT,BORDERPORTID,PORTDETAIL) values (@GID,@PORTID,@PORTENAME,@PORTRNAME,@PORTCNAME,@COUNTRY,@COUNTRYECODE,@COUNTRYRCODE,@COUNTRYCODE, @COUNTRYENAME,@COUNTRYRNAME,@CITYENAME,@CITYCNAME,@RAILBUREAU,@ADDR,@DTALSHANKOU,@DTMANZHOULI, @DTERLIAN,@DTSUIFENHE,@DTDANDONG,@DTPINGXIANG,@DTHORGOS,@ISOFTEN,@WORKPOWER,@IS20,@IS40,@IS48,@IS50 ,@ISTANK,@ISBULK,@ISFLAT,@ISDSCAR,@REMARKS,@ISCN,@PORTTYPE,@ISBORDERPORT,@BORDERPORTID,@PORTDETAIL) "; DbCommand cmd = db.GetSqlStringCommand(insertSql); db.AddInParameter(cmd, "GID", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmd, "PORTID", DbType.String, Convert.ToString(row["Code"])); db.AddInParameter(cmd, "PORTENAME", DbType.String, Convert.ToString(row["英文站名"])); db.AddInParameter(cmd, "PORTRNAME", DbType.String, Convert.ToString(row["俄文站名"])); db.AddInParameter(cmd, "PORTCNAME", DbType.String, Convert.ToString(row["中文站名"])); db.AddInParameter(cmd, "COUNTRY", DbType.String, Convert.ToString(row["国家代码"])); db.AddInParameter(cmd, "COUNTRYECODE", DbType.String, ""); db.AddInParameter(cmd, "COUNTRYRCODE", DbType.String, Convert.ToString(row["国家代码俄文"])); db.AddInParameter(cmd, "COUNTRYCODE", DbType.String, Convert.ToString(row["国家铁路简称"])); db.AddInParameter(cmd, "COUNTRYENAME", DbType.String, Convert.ToString(row["国家"])); db.AddInParameter(cmd, "COUNTRYRNAME", DbType.String, Convert.ToString(row["国家名俄文"])); db.AddInParameter(cmd, "CITYENAME", DbType.String, Convert.ToString(row["所属城市"])); db.AddInParameter(cmd, "CITYCNAME", DbType.String, ""); db.AddInParameter(cmd, "RAILBUREAU", DbType.String, Convert.ToString(row["铁路局"])); db.AddInParameter(cmd, "ADDR", DbType.String, Convert.ToString(row["地址"])); db.AddInParameter(cmd, "DTALSHANKOU", DbType.Decimal, 0); db.AddInParameter(cmd, "DTMANZHOULI", DbType.Decimal, 0); db.AddInParameter(cmd, "DTERLIAN", DbType.Decimal, 0); db.AddInParameter(cmd, "DTSUIFENHE", DbType.Decimal, 0); db.AddInParameter(cmd, "DTDANDONG", DbType.Decimal, 0); db.AddInParameter(cmd, "DTPINGXIANG", DbType.Decimal, 0); db.AddInParameter(cmd, "DTHORGOS", DbType.Decimal, 0); db.AddInParameter(cmd, "ISOFTEN", DbType.Boolean, false); db.AddInParameter(cmd, "WORKPOWER", DbType.String, ""); db.AddInParameter(cmd, "IS20", DbType.Boolean, false); db.AddInParameter(cmd, "IS40", DbType.Boolean, false); db.AddInParameter(cmd, "IS48", DbType.Boolean, false); db.AddInParameter(cmd, "IS50", DbType.Boolean, false); db.AddInParameter(cmd, "ISTANK", DbType.Boolean, false); db.AddInParameter(cmd, "ISBULK", DbType.Boolean, false); db.AddInParameter(cmd, "ISFLAT", DbType.Boolean, false); db.AddInParameter(cmd, "ISDSCAR", DbType.Boolean, false); db.AddInParameter(cmd, "REMARKS", DbType.String, ""); db.AddInParameter(cmd, "ISCN", DbType.Boolean, false); db.AddInParameter(cmd, "PORTTYPE", DbType.String, "T"); db.AddInParameter(cmd, "ISBORDERPORT", DbType.Boolean, false); db.AddInParameter(cmd, "BORDERPORTID", DbType.String, ""); db.AddInParameter(cmd, "PORTDETAIL", DbType.String, ""); db.ExecuteNonQuery(cmd, idbTran); #endregion } } idbTran.Commit(); isSucess = true; } catch (Exception exception) { idbTran.Rollback(); isSucess = false; msg = exception.Message; } } return isSucess; } public static bool ImportExcelData(HttpRequestBase request, DataTable table, out string msg, out Int32 InsertCount, out Int32 UpdateCount, string BSNO, out List headList, string userid, string username, string companyid) { var isSucess = false; msg = ""; headList = new List(); var headData = GetData("BSNO='" + BSNO+ "'"); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); InsertCount = 0; UpdateCount = 0; msg = ""; try { foreach (DataRow row in table.Rows) { headData.BSNO = "railway" + Guid.NewGuid().ToString(); headData.INPUTBY = username; headData.CREATETIME = DateTime.Now; headData.BSSTATUS = false; headData.FEESTATUS = false; headData.DbOperationType = DbOperationType.DbotIns; var billnoset = MsSysBillNoSetDAL.GetData("OPLBNAME='铁路运输'", companyid); if (billnoset.BILLTYPE != "") headData.CUSTNO = MsSysBillNoSetDAL.GetBillNo(billnoset, headData.BSDATE.ToString().Trim(), headData.ACCDATE.ToString().Trim()); else headData.CUSTNO = MsOpRailwayDAL.getCodeRule("委托编号", 7, "CUSTNO", headData.BSDATE.ToString().Trim(), headData.ACCDATE.ToString().Trim(), userid,companyid); headData.CNTRNO = Convert.ToString(row[0]); headData.CTNALL = Convert.ToString(row[1]); headData.CTNSOURCE = Convert.ToString(row[2]); headData.MASTERNO = headData.BSNO; if (headData.BSDATE == "") headData.BSDATE = null; if (headData.ETA == "") headData.ETA = null; if (headData.SETOUTDATE == "") headData.SETOUTDATE = null; if (headData.ASETOUTDATE == "") headData.ASETOUTDATE = null; if (headData.TRAINREPORTDATE == "") headData.TRAINREPORTDATE = null; if (headData.STATIONTIME == "") headData.STATIONTIME = null; if (headData.REPLACEDOCDATE == "") headData.REPLACEDOCDATE = null; if (headData.SHENCODETIME == "") headData.SHENCODETIME = null; if (headData.GIVECODETIME == "") headData.GIVECODETIME = null; if (headData.RECTNDATE == "") headData.RECTNDATE = null; if (headData.ONROADDATE == "") headData.ONROADDATE = null; if (headData.RETURNDATE == "") headData.RETURNDATE = null; if (headData.RECEIVECTNDATE == "") headData.RECEIVECTNDATE = null; if (headData.CUSTOMDATE == "") headData.CUSTOMDATE = null; if (headData.CUSTOMCLEARDATE == "") headData.CUSTOMCLEARDATE = null; if (headData.ARRIVEPLACETRANSIT == "") headData.ARRIVEPLACETRANSIT = null; if (headData.SETOUTPLACETRANSIT == "") headData.SETOUTPLACETRANSIT = null; if (headData.ARRIVEFRPLACETRANSIT == "") headData.ARRIVEFRPLACETRANSIT = null; if (headData.ARRIVALDATE == "") headData.ARRIVALDATE = null; if (headData.RETRAINDATE == "") headData.RETRAINDATE = null; if (headData.STLDATE == "") headData.STLDATE = null; var modb = new ModelObjectDB(); DBResult result = modb.Save(headData); if (result.Success) InsertCount++; } isSucess = true; } catch (Exception exception) { isSucess = false; msg = exception.Message; } return isSucess; } public static DBResult DeleteFrPort(string GID) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (GID != "") { var cmdDelete = db.GetSqlStringCommand("delete from code_railway_frport where GID='" + GID + "'"); db.ExecuteNonQuery(cmdDelete, tran); } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "删除成功"; return result; } #endregion static public List GetCustomRefList(string bsno) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("Gid,CODENAME AS CustCode, SHORTNAME AS CustName,CODENAME + '-' + SHORTNAME AS CodeAndName,BillRises1"); strSql.Append(",UNITPRICE,description,SHORTNAME+'_'+description KFCodename,SALE"); strSql.Append(" from info_Client where SHORTNAME IN (SELECT CUSTOMERNAME from ch_Fee where BSNO='"+bsno+"' AND FEETYPE=2)"); return SetCustomRefData(strSql); } private static List SetCustomRefData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { CustomRefModel data = new CustomRefModel(); #region Set DB data to Object data.GId = Convert.ToString(reader["GId"]); data.CustCode = Convert.ToString(reader["CustCode"]); data.CustName = Convert.ToString(reader["CustName"]); data.CodeAndName = Convert.ToString(reader["CodeAndName"]); data.BillRises1 = Convert.ToString(reader["BillRises1"]); data.UNITPRICE = Convert.ToString(reader["UNITPRICE"]); data.DESCRIPTION = Convert.ToString(reader["description"]); data.KFCodename = Convert.ToString(reader["KFCodename"]); data.SALE = Convert.ToString(reader["SALE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #region 运踪代码 static public List GetCodeTrackingList(string strCondition, string sort = null) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" GID,TRACKINGCODE,REMARK"); strSql.Append(" from code_railwaytracking "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by TRACKINGCODE"); } return SetCodeTrackingData(strSql); } private static List SetCodeTrackingData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { CodeRailWayTracking data = new CodeRailWayTracking(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.TRACKINGCODE = Convert.ToString(reader["TRACKINGCODE"]); data.REMARK = Convert.ToString(reader["REMARK"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static DBResult SaveCodeTracking(List bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (bodyList != null) { var cmddelete = db.GetSqlStringCommand(@"delete from code_railwaytracking "); db.ExecuteNonQuery(cmddelete, tran); foreach (var enumValue in bodyList) { var cmdInsert = db.GetSqlStringCommand(@"insert into code_railwaytracking (GID,TRACKINGCODE,REMARK) values (newid(),'" + enumValue.TRACKINGCODE + "','" + enumValue.REMARK + "')"); 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 DeleteCodeTracking(List headData) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { if (headData != null) { foreach (var enumValue in headData) { var cmdDelete = db.GetSqlStringCommand("delete from code_railwaytracking where GID='" + enumValue.GID + "'"); db.ExecuteNonQuery(cmdDelete, tran); } } tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "删除成功"; return result; } #endregion #region 修改 public static DBResult Modify(List bodyList, MsOpRailway 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 op_railway set bsno=bsno "); if (modifydata.SETOUTDATE != "" && modifydata.SETOUTDATE != null) strSql.Append(",SETOUTDATE=@SETOUTDATE"); if (modifydata.RETRAINDATE != "" && modifydata.RETRAINDATE != null) strSql.Append(",RETRAINDATE=@RETRAINDATE"); if (modifydata.ARRIVEPLACETRANSIT != "" && modifydata.ARRIVEPLACETRANSIT != null) strSql.Append(",ARRIVEPLACETRANSIT=@ARRIVEPLACETRANSIT"); if (modifydata.SETOUTPLACETRANSIT != "" && modifydata.SETOUTPLACETRANSIT != null) strSql.Append(",SETOUTPLACETRANSIT=@SETOUTPLACETRANSIT"); strSql.Append(" where BSNO=@BSNO "); var cmdupdate = db.GetSqlStringCommand(strSql.ToString()); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdupdate.Parameters.Clear(); if (modifydata.SETOUTDATE != "" && modifydata.SETOUTDATE != null) db.AddInParameter(cmdupdate, "@SETOUTDATE", DbType.String, modifydata.SETOUTDATE); if (modifydata.RETRAINDATE != "" && modifydata.RETRAINDATE != null) db.AddInParameter(cmdupdate, "@RETRAINDATE", DbType.String, modifydata.RETRAINDATE); if (modifydata.ARRIVEPLACETRANSIT != "" && modifydata.ARRIVEPLACETRANSIT != null) db.AddInParameter(cmdupdate, "@ARRIVEPLACETRANSIT", DbType.String, modifydata.ARRIVEPLACETRANSIT); if (modifydata.SETOUTPLACETRANSIT != "" && modifydata.SETOUTPLACETRANSIT != null) db.AddInParameter(cmdupdate, "@SETOUTPLACETRANSIT", DbType.String, modifydata.SETOUTPLACETRANSIT); db.AddInParameter(cmdupdate, "@BSNO", DbType.String, enumValue.BSNO); 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; } #endregion #region 添加日志 public static DBResult setLog(MsOpRailway modeldata, string strUserID) { DBResult result = new DBResult(); if (modeldata == null) { result.Success = false; result.Message = "没有输入任何需要修改的数据"; return result; } string OPLBNAME ="铁路运输"; string MBLNO = modeldata.MBLNO.ToString(); // Database db = DatabaseFactory.CreateDatabase(); using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { #region 把【仓储结算关联表wms_settlement_date】中数据【ISDELETE】设为【1】 string strSql = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除信息','删除操作','" + OPLBNAME + "主提单号：" + MBLNO + "','" + strUserID + "')"; int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null); #endregion sqlTran.Commit(); } catch (Exception) { sqlTran.Rollback(); result.Success = false; result.Message = "操作出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作数据成功"; return result; } #endregion } }