using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.MsOpBulk; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.EntityDA; using DSWeb.Areas.CommMng.Models; using HcUtility.Comm; using System.Data.SqlClient; using System.Web; using System.Data.Common; using DSWeb.TruckMng.Comm.Cookie; using DSWeb.DataAccess; namespace DSWeb.MvcShipping.DAL.MsOpBulkDAL { public class MsOpBulkDAL { #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 "); strSql.Append("BsNo,BsStatus,FeeStatus,OpStatus,BsDate,AccDate,OpDate,CustNo,ORDERNO"); strSql.Append(",CustomerName,TransType,ETD,LASTETD,ETA,PORTLOAD,PORTDISCHARGE"); strSql.Append(",INPUTBY,OP,SALE,BSSOURCE,BSSOURCEDETAIL,GOODCODE,GOODSNAME"); strSql.Append(",CORPID,SALEDEPT,PKGS,KGS,OVERKGS,OVERPKGS,CBM,REMARK,STLNAME,STLDATE"); strSql.Append(",(CASE BsStatus WHEN 1 THEN '已锁定' else '未锁定' end) as bsstatusref "); strSql.Append(",(CASE FeeStatus WHEN 1 THEN '已锁定' else '未锁定' end) as feestatusref "); strSql.Append(" from OP_BULK "); 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 BsNo"); } 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 "); strSql.Append("BsNo,BsStatus,FeeStatus,OpStatus,BsDate,AccDate,OpDate,CustNo,ORDERNO"); strSql.Append(",CustomerName,TransType,ETD,LASTETD,ETA,PORTLOAD,PORTDISCHARGE"); strSql.Append(",INPUTBY,OP,SALE,BSSOURCE,BSSOURCEDETAIL,GOODCODE,GOODSNAME"); strSql.Append(",CORPID,SALEDEPT,PKGS,KGS,OVERKGS,OVERPKGS,CBM,REMARK,STLNAME,STLDATE"); strSql.Append(",(CASE BsStatus WHEN 1 THEN '已锁定' else '未锁定' end) as bsstatusref "); strSql.Append(",(CASE FeeStatus WHEN 1 THEN '已锁定' else '未锁定' end) as feestatusref "); strSql.Append(" from OP_BULK "); 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 BsNo"); } return strSql.ToString(); } static public MsOpBulk GetData(string condition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("BsNo,BsStatus,FeeStatus,OpStatus,BsDate,AccDate,OpDate,CustNo,ORDERNO"); strSql.Append(",CustomerName,TransType,ETD,LASTETD,ETA,PORTLOAD,PORTDISCHARGE"); strSql.Append(",INPUTBY,OP,SALE,BSSOURCE,BSSOURCEDETAIL,GOODCODE,GOODSNAME"); strSql.Append(",CORPID,SALEDEPT,PKGS,KGS,OVERKGS,OVERPKGS,CBM,REMARK,STLNAME,STLDATE"); strSql.Append(",(CASE BsStatus WHEN 1 THEN '已锁定' else '未锁定' end) as bsstatusref "); strSql.Append(",(CASE FeeStatus WHEN 1 THEN '已锁定' else '未锁定' end) as feestatusref "); strSql.Append(" from OP_BULK "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition); } var list=SetData(strSql); if (list.Count > 0) return list[0]; return new MsOpBulk(); } 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()) { MsOpBulk data = new MsOpBulk(); #region Set DB data to Object 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"]); if (reader["BSDATE"] != DBNull.Value) data.BSDATE = Convert.ToDateTime(reader["BSDATE"]); data.ETD = Convert.ToString(reader["ETD"]); data.LASTETD = Convert.ToString(reader["LASTETD"]); data.ETA = Convert.ToString(reader["ETA"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.OPSTATUS = Convert.ToString(reader["OPSTATUS"]); data.GOODCODE = Convert.ToString(reader["GOODCODE"]); data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]); if (reader["OpDate"] != DBNull.Value) data.OPDATE = Convert.ToString(reader["OpDate"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.ORDERNO = Convert.ToString(reader["ORDERNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.TRANSTYPE = Convert.ToString(reader["TRANSTYPE"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.INPUTBY = Convert.ToString(reader["INPUTBY"]); data.OP = Convert.ToString(reader["OP"]); data.SALE = Convert.ToString(reader["SALE"]); data.BSSOURCE = Convert.ToString(reader["BSSOURCE"]); data.BSSOURCEDETAIL = Convert.ToString(reader["BSSOURCEDETAIL"]); data.CORPID = Convert.ToString(reader["CORPID"]); data.SALEDEPT = Convert.ToString(reader["SALEDEPT"]); data.PKGS = Convert.ToInt32(reader["PKGS"]); data.KGS = Convert.ToDecimal(reader["KGS"]); if (reader["OVERKGS"] != DBNull.Value) data.OVERKGS = Convert.ToDecimal(reader["OVERKGS"]); if (reader["OVERPKGS"] != DBNull.Value) data.OVERPKGS = Convert.ToDecimal(reader["OVERPKGS"]); data.CBM = Convert.ToDecimal(reader["CBM"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.STLNAME = Convert.ToString(reader["STLNAME"]); data.STLDATE = Convert.ToString(reader["STLDATE"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion static public List GetBodyList(string strCondition,string sort) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("BSNO,SerialNo,ACCSTATUS,ISTRAIN,TruckNo,DrvName,DrvCust,STATIONNO"); strSql.Append(",ExpDate,PKGS,KGS,NETKGS,TAREKGS,PKGKGS,POUNDNO,TRANSTOTAL,TRANSOT,Remark"); strSql.Append(" from op_Bulk_detail "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by SerialNo"); } 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()) { MsOpBulkDetail data = new MsOpBulkDetail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); if (reader["SERIALNO"] != DBNull.Value) data.SerialNo = Convert.ToDecimal(reader["SERIALNO"]); if (reader["ACCSTATUS"] != DBNull.Value) data.ACCSTATUS = Convert.ToBoolean(reader["ACCSTATUS"]); data.ISTRAIN = Convert.ToString(reader["ISTRAIN"]); data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]); data.DRVNAME = Convert.ToString(reader["DRVNAME"]); data.DRVCUST = Convert.ToString(reader["DRVCUST"]); data.EXPDATE = Convert.ToString(reader["EXPDATE"]); data.STATIONNO = Convert.ToString(reader["STATIONNO"]); if (reader["PKGS"] != DBNull.Value) data.PKGS = Convert.ToDecimal(reader["PKGS"]); if (reader["KGS"] != DBNull.Value) data.KGS = Convert.ToDecimal(reader["KGS"]); if (reader["NETKGS"] != DBNull.Value) data.NETKGS = Convert.ToDecimal(reader["NETKGS"]); if (reader["TAREKGS"] != DBNull.Value) data.TAREKGS = Convert.ToDecimal(reader["TAREKGS"]); if (reader["PKGKGS"] != DBNull.Value) data.PKGKGS = Convert.ToDecimal(reader["PKGKGS"]); data.POUNDNO = Convert.ToString(reader["POUNDNO"]); if (reader["TRANSTOTAL"] != DBNull.Value) data.TRANSTOTAL = Convert.ToDecimal(reader["TRANSTOTAL"]); if (reader["TRANSOT"] != DBNull.Value) data.TRANSOT = Convert.ToDecimal(reader["TRANSOT"]); data.REMARK = Convert.ToString(reader["Remark"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } static public List GetBodyListTrain(string strCondition,string sort) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("BSNO,SerialNo,ACCSTATUS,TRAINNO,TRAINTYPE,TRAINCUST,INVNO,STATIONNO,ARRIVALSTATION"); strSql.Append(",ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark"); strSql.Append(" from op_Bulk_railway_detail "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by SerialNo"); } 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()) { MsOpBulkTrainDetail data = new MsOpBulkTrainDetail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); if (reader["SERIALNO"] != DBNull.Value) data.SerialNo = Convert.ToDecimal(reader["SERIALNO"]); if (reader["ACCSTATUS"] != DBNull.Value) data.ACCSTATUS = Convert.ToBoolean(reader["ACCSTATUS"]); data.TRAINNO = Convert.ToString(reader["TRAINNO"]); data.TRAINTYPE = Convert.ToString(reader["TRAINTYPE"]); data.TRAINCUST = Convert.ToString(reader["TRAINCUST"]); data.INVNO = Convert.ToString(reader["INVNO"]); data.STATIONNO = Convert.ToString(reader["STATIONNO"]); data.ARRIVALSTATION = Convert.ToString(reader["ARRIVALSTATION"]); data.EXPDATE = Convert.ToString(reader["EXPDATE"]); if (reader["PKGS"] != DBNull.Value) data.PKGS = Convert.ToDecimal(reader["PKGS"]); if (reader["KGS"] != DBNull.Value) data.KGS = Convert.ToDecimal(reader["KGS"]); if (reader["TRANSTOTAL"] != DBNull.Value) data.TRANSTOTAL = Convert.ToDecimal(reader["TRANSTOTAL"]); if (reader["TRANSOT"] != DBNull.Value) data.TRANSOT = Convert.ToDecimal(reader["TRANSOT"]); data.REMARK = Convert.ToString(reader["Remark"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } static public MsOpBulkStationKc GetStationKc(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("STATIONNO,SUM(PKGS) PKGS,SUM(KGS) KGS "); strSql.Append(" from V_OP_BULK_KC "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" Group by STATIONNO "); var list = SetStationKcData(strSql); if (list.Count > 0) return list[0]; return new MsOpBulkStationKc(); } static public List GetStationKcList(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("STATIONNO,SUM(PKGS) PKGS,SUM(KGS) KGS "); strSql.Append(" from V_OP_BULK_KC "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" Group by STATIONNO "); return SetStationKcData(strSql); } private static List SetStationKcData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpBulkStationKc data = new MsOpBulkStationKc(); #region Set DB data to Object data.STATION = Convert.ToString(reader["STATIONNO"]); // data.GOODCODE = Convert.ToString(reader["GOODCODE"]); data.PKGS = Convert.ToDecimal(reader["PKGS"]); data.KGS = Convert.ToDecimal(reader["KGS"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } static public List GetStationKcDetail(string strCondition,string sort) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("BSNO,BSTYPE,STATIONNO,CUSTNO,EXPDATE,PKGS,KGS "); strSql.Append(" from V_OP_BULK_KC "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by EXPDATE"); } return SetStationKcDetailData(strSql); } private static List SetStationKcDetailData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpBulkStationKcDetail data = new MsOpBulkStationKcDetail(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]); data.BSTYPE = Convert.ToString(reader["BSTYPE"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.EXPDATE = Convert.ToString(reader["EXPDATE"]); data.STATIONNO = Convert.ToString(reader["STATIONNO"]); data.PKGS = Convert.ToDecimal(reader["PKGS"]); data.KGS = Convert.ToDecimal(reader["KGS"]); #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } static public MsOpBulkKcModify GetKcModifyData(string condition) { var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("GID,OP,STATIONNO,CUSTNO,BSDATE,PKGS,KGS,REMARK "); strSql.Append(" from op_Bulk_station_kc_modify "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" where " + condition); } var list = SetKcModifyData(strSql); if (list.Count > 0) return list[0]; return new MsOpBulkKcModify(); } private static List SetKcModifyData(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpBulkKcModify data = new MsOpBulkKcModify(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); if (reader["BSDATE"] != DBNull.Value) data.BSDATE = Convert.ToString(reader["BSDATE"]); data.OP = Convert.ToString(reader["OP"]); data.STATIONNO = Convert.ToString(reader["STATIONNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.PKGS = Convert.ToInt32(reader["PKGS"]); data.KGS = Convert.ToDecimal(reader["KGS"]); data.REMARK = Convert.ToString(reader["REMARK"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static DBResult SaveDetail(string BsNo, List bodyList,bool acc) { 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 op_Bulk_detail (BSNO, SerialNo,IsTrain,TruckNo,DrvName,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo) values (@BSNO, @SerialNo,@IsTrain, @TruckNo,@DrvName,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) "); var cmdUpdate = db.GetSqlStringCommand( @"update op_Bulk_detail set TruckNo=@TruckNo,DrvName=@DrvName,ExpDate=@ExpDate,PKGS=@PKGS,KGS=@KGS,TRANSTOTAL=@TRANSTOTAL ,TRANSOT=@TRANSOT,Remark=@Remark,ACCSTATUS=@ACCSTATUS,StationNo=@StationNo where BSNO=@BSNO AND SerialNo=@SerialNo and IsTrain=@IsTrain "); if (bodyList != null) { foreach (var enumValue in bodyList) { if (enumValue.BSNO == "*" || enumValue.BSNO == "") { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, BsNo); db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo); db.AddInParameter(cmdInsert, "@ISTRAIN", DbType.String, enumValue.ISTRAIN); db.AddInParameter(cmdInsert, "@TruckNo", DbType.String, enumValue.TRUCKNO); db.AddInParameter(cmdInsert, "@DrvName", DbType.String, enumValue.DRVNAME); if (enumValue.EXPDATE != "" && enumValue.EXPDATE !=null) { db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1,10)); } else { db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE); } db.AddInParameter(cmdInsert, "@PKGS", DbType.Decimal, enumValue.PKGS); db.AddInParameter(cmdInsert, "@KGS", DbType.Decimal, enumValue.KGS); db.AddInParameter(cmdInsert, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL); db.AddInParameter(cmdInsert, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT); if (acc) { db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, "True"); } else { db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS); } db.AddInParameter(cmdInsert, "@StationNo", DbType.String, enumValue.STATIONNO); db.AddInParameter(cmdInsert, "@Remark", DbType.String, enumValue.REMARK); db.ExecuteNonQuery(cmdInsert, tran); } else { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo); db.AddInParameter(cmdUpdate, "@SerialNo", DbType.Int32, enumValue.SerialNo); db.AddInParameter(cmdUpdate, "@ISTRAIN", DbType.String, enumValue.ISTRAIN); db.AddInParameter(cmdUpdate, "@TruckNo", DbType.String, enumValue.TRUCKNO); db.AddInParameter(cmdUpdate, "@DrvName", DbType.String, enumValue.DRVNAME); if (enumValue.EXPDATE != "" && enumValue.EXPDATE != null) { db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1, 10)); } else { db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE); } db.AddInParameter(cmdUpdate, "@PKGS", DbType.Decimal, enumValue.PKGS); db.AddInParameter(cmdUpdate, "@KGS", DbType.Decimal, enumValue.KGS); db.AddInParameter(cmdUpdate, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL); db.AddInParameter(cmdUpdate, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT); if (acc) { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True"); } else { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS); } db.AddInParameter(cmdUpdate, "@StationNo", DbType.String, enumValue.STATIONNO); 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 AuditDetail(string BsNo, bool acc) { 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 op_Bulk_detail set ACCSTATUS=@ACCSTATUS where BSNO=@BSNO "); cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo); if (acc) { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True"); } else { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "False"); } 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 DeleteDetail(List bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"delete from op_Bulk_detail where BSNO=@BSNO AND SERIALNO=@SerialNo AND IsTrain=@IsTrain "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@BSNO", DbType.String,enumValue.BSNO); db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo); db.AddInParameter(cmdInsert, "@ISTRAIN", DbType.String, enumValue.ISTRAIN); 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 SaveTrainDetail(string BsNo, List bodyList, bool acc) { 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 op_Bulk_railway_detail (BSNO, SerialNo,TrainNo,TrainType,InvNo,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo) values (@BSNO, @SerialNo, @TrainNo,@TrainType,@InvNo,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) "); var cmdUpdate = db.GetSqlStringCommand( @"update op_Bulk_railway_detail set TrainNo=@TrainNo,TrainType=@TrainType,InvNo=@InvNo,ExpDate=@ExpDate,PKGS=@PKGS,KGS=@KGS,TRANSTOTAL=@TRANSTOTAL ,TRANSOT=@TRANSOT,Remark=@Remark,ACCSTATUS=@ACCSTATUS,StationNo=@StationNo where BSNO=@BSNO AND SerialNo=@SerialNo "); if (bodyList != null) { foreach (var enumValue in bodyList) { if (enumValue.BSNO == "*" || enumValue.BSNO == "") { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, BsNo); db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo); db.AddInParameter(cmdInsert, "@TrainNo", DbType.String, enumValue.TRAINNO); db.AddInParameter(cmdInsert, "@TrainType", DbType.String, enumValue.TRAINTYPE); db.AddInParameter(cmdInsert, "@InvNo", DbType.String, enumValue.INVNO); if (enumValue.EXPDATE != "" && enumValue.EXPDATE != null) { db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1, 10)); } else { db.AddInParameter(cmdInsert, "@ExpDate", DbType.String, enumValue.EXPDATE); } db.AddInParameter(cmdInsert, "@PKGS", DbType.Decimal, enumValue.PKGS); db.AddInParameter(cmdInsert, "@KGS", DbType.Decimal, enumValue.KGS); db.AddInParameter(cmdInsert, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL); db.AddInParameter(cmdInsert, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT); if (acc) { db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, "True"); } else { db.AddInParameter(cmdInsert, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS); } db.AddInParameter(cmdInsert, "@StationNo", DbType.String, enumValue.STATIONNO); db.AddInParameter(cmdInsert, "@Remark", DbType.String, enumValue.REMARK); db.ExecuteNonQuery(cmdInsert, tran); } else { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo); db.AddInParameter(cmdUpdate, "@SerialNo", DbType.Int32, enumValue.SerialNo); db.AddInParameter(cmdUpdate, "@TrainNo", DbType.String, enumValue.TRAINNO); db.AddInParameter(cmdUpdate, "@TrainType", DbType.String, enumValue.TRAINTYPE); db.AddInParameter(cmdUpdate, "@InvNo", DbType.String, enumValue.INVNO); if (enumValue.EXPDATE != "" && enumValue.EXPDATE != null) { db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE.Substring(1, 10)); } else { db.AddInParameter(cmdUpdate, "@ExpDate", DbType.String, enumValue.EXPDATE); } db.AddInParameter(cmdUpdate, "@PKGS", DbType.Decimal, enumValue.PKGS); db.AddInParameter(cmdUpdate, "@KGS", DbType.Decimal, enumValue.KGS); db.AddInParameter(cmdUpdate, "@TRANSTOTAL", DbType.Decimal, enumValue.TRANSTOTAL); db.AddInParameter(cmdUpdate, "@TRANSOT", DbType.Decimal, enumValue.TRANSOT); if (acc) { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True"); } else { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, enumValue.ACCSTATUS); } db.AddInParameter(cmdUpdate, "@StationNo", DbType.String, enumValue.STATIONNO); 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 AuditTrainDetail(string BsNo, bool acc) { 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 op_Bulk_railway_detail set ACCSTATUS=@ACCSTATUS where BSNO=@BSNO "); cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@BSNO", DbType.String, BsNo); if (acc) { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String, "True"); } else { db.AddInParameter(cmdUpdate, "@ACCSTATUS", DbType.String,"False"); } 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 DeleteTrainDetail(List bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"delete from op_Bulk_railway_detail where BSNO=@BSNO AND SERIALNO=@SerialNo "); if (bodyList != null) { foreach (var enumValue in bodyList) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.BSNO); db.AddInParameter(cmdInsert, "@SerialNo", DbType.Int32, enumValue.SerialNo); 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 DeleteKcModifyDetail(MsOpBulkStationKcDetail bodyList) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"delete from op_Bulk_station_kc_modify where GID=@BSNO "); if (bodyList != null) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, bodyList.BSNO); 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 DeleteDetail(MsOpBulk headData) { 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_Bulk_detail where BSNO='" + headData.BSNO + "'"); db.ExecuteNonQuery(cmdDelete, tran); cmdDelete = db.GetSqlStringCommand("delete from op_Bulk_railway_detail where BSNO='" + headData.BSNO + "'"); db.ExecuteNonQuery(cmdDelete, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "保存成功"; return result; } public static bool ImportTruckDetailData(string bsno, HttpRequestBase request, DataTable table, out string msg, out List headList) { var isSucess = false; msg = ""; headList = new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (DbConnection connection = db.CreateConnection()) { connection.Open(); DbTransaction idbTran = connection.BeginTransaction(); try { var orgCode = CookieConfig.GetCookie_OrgCode(request); var userCode = CookieConfig.GetCookie_UserCode(request); var userName = CookieConfig.GetCookie_UserName(request); int SerialNo = 0; var objSerialNo = db.ExecuteScalar(CommandType.Text, "select MAX(SerialNo) AS SerialNo from op_Bulk_detail where ISTRAIN='1' AND BSNO='" + bsno + "'"); var StrSerialNo = Convert.ToString(objSerialNo); if (string.IsNullOrEmpty(StrSerialNo)) SerialNo = 0; else SerialNo = Convert.ToInt16(StrSerialNo); const string deleteSql = @"delete from op_Bulk_detail where bsno=@bsno and ISTRAIN=@ISTRAIN"; DbCommand cmddelete = db.GetSqlStringCommand(deleteSql); db.AddInParameter(cmddelete, "BSNO", DbType.String, bsno); db.AddInParameter(cmddelete, "ISTRAIN", DbType.String, '0'); db.ExecuteNonQuery(cmddelete, idbTran); foreach (DataRow row in table.Rows) { var custName = Convert.ToString(row["日期"]); if (custName != "") { #region 托单数据生成 SerialNo = SerialNo + 1; const string insertSql =@"insert into op_Bulk_detail (BSNO, SerialNo,IsTrain,TruckNo,DrvName,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo) values (@BSNO, @SerialNo,@IsTrain, @TruckNo,@DrvName,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) "; DbCommand cmd = db.GetSqlStringCommand(insertSql); db.AddInParameter(cmd, "BSNO", DbType.String, bsno); db.AddInParameter(cmd, "SerialNo", DbType.Int16, SerialNo); db.AddInParameter(cmd, "IsTrain", DbType.String, '0'); db.AddInParameter(cmd, "TruckNo", DbType.String, ""); db.AddInParameter(cmd, "DrvName", DbType.String, ""); var custDate = Convert.ToString(row["日期"]).Trim(); if (!string.IsNullOrEmpty(custDate)) { custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim(); } db.AddInParameter(cmd, "ExpDate", DbType.String, custDate); db.AddInParameter(cmd, "PKGS", DbType.Decimal, Convert.ToDecimal(row["包数"])); db.AddInParameter(cmd, "KGS", DbType.Decimal, Convert.ToDecimal(row["吨数"])); db.AddInParameter(cmd, "TRANSTOTAL", DbType.Decimal, 0); db.AddInParameter(cmd, "TRANSOT", DbType.Decimal, 0); db.AddInParameter(cmd, "Remark", DbType.String, ""); db.AddInParameter(cmd, "ACCSTATUS", DbType.Boolean, false); db.AddInParameter(cmd, "StationNo", 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 ImportDetailData(string bsno, HttpRequestBase request, DataTable table, out string msg, out List headList) { var isSucess = false; msg = ""; headList = new List(); if (table == null) throw new ArgumentNullException("table"); var billNoList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (DbConnection connection = db.CreateConnection()) { connection.Open(); DbTransaction idbTran = connection.BeginTransaction(); try { var orgCode = CookieConfig.GetCookie_OrgCode(request); var userCode = CookieConfig.GetCookie_UserCode(request); var userName = CookieConfig.GetCookie_UserName(request); int SerialNo = 0; var objSerialNo = db.ExecuteScalar(CommandType.Text, "select MAX(SerialNo) AS SerialNo from op_Bulk_detail where ISTRAIN='0' AND BSNO='" + bsno + "'"); var StrSerialNo = Convert.ToString(objSerialNo); if (string.IsNullOrEmpty(StrSerialNo)) SerialNo = 0; else SerialNo = Convert.ToInt16(StrSerialNo); const string deleteSql = @"delete from op_Bulk_detail where bsno=@bsno and ISTRAIN=@ISTRAIN"; DbCommand cmddelete = db.GetSqlStringCommand(deleteSql); db.AddInParameter(cmddelete, "BSNO", DbType.String, bsno); db.AddInParameter(cmddelete, "ISTRAIN", DbType.String, '1'); db.ExecuteNonQuery(cmddelete, idbTran); foreach (DataRow row in table.Rows) { var custName = Convert.ToString(row["日期"]); if (custName != "") { #region 托单数据生成 SerialNo = SerialNo + 1; const string insertSql = @"insert into op_Bulk_detail (BSNO, SerialNo,IsTrain,TruckNo,DrvName,ExpDate,PKGS,KGS,NETKGS,TAREKGS,PKGKGS,POUNDNO,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo) values (@BSNO, @SerialNo,@IsTrain, @TruckNo,@DrvName,@ExpDate,@PKGS,@KGS,@NETKGS,@TAREKGS,@PKGKGS,@POUNDNO,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo) "; DbCommand cmd = db.GetSqlStringCommand(insertSql); db.AddInParameter(cmd, "BSNO", DbType.String, bsno); db.AddInParameter(cmd, "SerialNo", DbType.Int16, Convert.ToInt16(row["序号"])); db.AddInParameter(cmd, "IsTrain", DbType.String, '1'); db.AddInParameter(cmd, "TruckNo", DbType.String,Convert.ToString(row["车号"])); db.AddInParameter(cmd, "DrvName", DbType.String, ""); var custDate = Convert.ToString(row["日期"]).Trim(); if (!string.IsNullOrEmpty(custDate)) { custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim(); } db.AddInParameter(cmd, "ExpDate", DbType.String, custDate); db.AddInParameter(cmd, "PKGS", DbType.Decimal, Convert.ToDecimal(row["袋数"])); db.AddInParameter(cmd, "KGS", DbType.Decimal, Convert.ToDecimal(row["毛重"])); db.AddInParameter(cmd, "NETKGS", DbType.Decimal, Convert.ToDecimal(row["净重"])); db.AddInParameter(cmd, "TAREKGS", DbType.Decimal, Convert.ToDecimal(row["皮重"])); db.AddInParameter(cmd, "PKGKGS", DbType.Decimal, Convert.ToDecimal(row["包装物重"])); db.AddInParameter(cmd, "TRANSTOTAL", DbType.Decimal, 0); db.AddInParameter(cmd, "POUNDNO", DbType.String, Convert.ToString(row["磅单编号"])); db.AddInParameter(cmd, "TRANSOT", DbType.Decimal, 0); db.AddInParameter(cmd, "Remark", DbType.String, ""); db.AddInParameter(cmd, "ACCSTATUS", DbType.Boolean, false); db.AddInParameter(cmd, "StationNo", 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 ImportTrainDetailData(string bsno, 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 { int SerialNo = 0; var objSerialNo = db.ExecuteScalar(CommandType.Text, "select MAX(SerialNo) AS SerialNo from op_Bulk_railway_detail where BSNO='" + bsno + "'"); var StrSerialNo = Convert.ToString(objSerialNo); if (string.IsNullOrEmpty(StrSerialNo)) SerialNo = 0; else SerialNo = Convert.ToInt16(StrSerialNo); const string deleteSql = @"delete from op_Bulk_railway_detail where bsno=@bsno"; DbCommand cmddelete = db.GetSqlStringCommand(deleteSql); db.AddInParameter(cmddelete, "BSNO", DbType.String, bsno); db.ExecuteNonQuery(cmddelete, idbTran); foreach (DataRow row in table.Rows) { var custName = Convert.ToString(row["日期"]); if (custName != "") { #region 托单数据生成 SerialNo = SerialNo + 1; const string insertSql = @"insert into op_Bulk_railway_detail (BSNO, SerialNo,TrainNo,TrainType,InvNo,ExpDate,PKGS,KGS,TRANSTOTAL,TRANSOT,Remark,ACCSTATUS,StationNo,ARRIVALSTATION) values (@BSNO, @SerialNo, @TrainNo,@TrainType,@InvNo,@ExpDate,@PKGS,@KGS,@TRANSTOTAL,@TRANSOT,@Remark,@ACCSTATUS,@StationNo,@ARRIVALSTATION) "; DbCommand cmd = db.GetSqlStringCommand(insertSql); db.AddInParameter(cmd, "BSNO", DbType.String, bsno); db.AddInParameter(cmd, "SerialNo", DbType.Int16, Convert.ToInt16(row["序号"])); db.AddInParameter(cmd, "ARRIVALSTATION", DbType.String, Convert.ToString(row["到站"]).Trim()); db.AddInParameter(cmd, "TrainNo", DbType.String, Convert.ToString(row["车号"]).Trim()); db.AddInParameter(cmd, "TrainType", DbType.String, Convert.ToString(row["车型"]).Trim()); db.AddInParameter(cmd, "InvNo", DbType.String, Convert.ToString(row["票号"]).Trim()); var custDate = Convert.ToString(row["日期"]).Trim(); if (!string.IsNullOrEmpty(custDate)) { custDate = Convert.ToDateTime(custDate).ToString("yyyy-MM-dd").Trim(); } db.AddInParameter(cmd, "ExpDate", DbType.String, custDate); db.AddInParameter(cmd, "PKGS", DbType.Decimal, Convert.ToDecimal(row["包数"])); db.AddInParameter(cmd, "KGS", DbType.Decimal, Convert.ToDecimal(row["吨数"])); db.AddInParameter(cmd, "TRANSTOTAL", DbType.Decimal, Convert.ToDecimal(row["金额"])); db.AddInParameter(cmd, "TRANSOT", DbType.Decimal, 0); db.AddInParameter(cmd, "Remark", DbType.String, ""); db.AddInParameter(cmd, "ACCSTATUS", DbType.Boolean, false); db.AddInParameter(cmd, "StationNo", 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 int p_update_OVERKGS(string bsno) { Database db = DatabaseFactory.CreateDatabase(); var cmd = db.GetStoredProcCommand("p_update_OVERKGS"); db.AddInParameter(cmd, "@con_no", DbType.String, bsno); db.ExecuteNonQuery(cmd); return 0; } public static String getCodeRule(string strRULENAME, 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=6"); 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_bulk] 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 ""; } public static bool GetFeeCount(string bsno) { var isfee = false; var strSql = new StringBuilder(); strSql.Append("Select count(*) as count "); strSql.Append(" from ch_fee "); strSql.Append(" where bsno='" + bsno+"'"); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var evData = Convert.ToInt32(reader["count"]); if (evData > 0) { isfee = true; }; } reader.Close(); } return isfee; } //判断单号是否重复 static public int GetRdCount(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT Count(BSNO) AS CT from op_bulk"); 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; } #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_Bulk 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_bulk 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 参照部分 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]='modOpBulkOpwt' 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 INPUTBY='"+username+"')"; } else if (visiblerange == "3") { str = " (OP='" + username + "' OR INPUTBY='" + 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 + "'))" + " OR INPUTBY 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 + "'))" + " OR INPUTBY 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 } }