using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Text; using DSWeb.MvcShipping.Models.MsOpReceipt; using HcUtility.Comm; using System.Data.SqlClient; using WebSqlHelper; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.EntityDA; using System.Web; using System.IO; using DSWeb.Areas.CommMng.Models; namespace DSWeb.MvcShipping.DAL.MsOpReceiptDAL { public class MsOpReceiptDAL { #region 插入、更新、删除 等 op_receipt 表的操作，并返回操作结果 static public int SaveUpdateReceipt(IList tempReceiptEntities, string tempGID, string tempUserID, string tempCOMPANYID) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { foreach (MsOpReceiptEntity model in tempReceiptEntities) { if (model.BSNO == "*" || model.BSNO == "") { #region 插入op_receipt，并返回操作结果 SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) , new SqlParameter("@TRANCNO", SqlDbType.VarChar,100) , new SqlParameter("@ISREPEAT", SqlDbType.Bit,1) , new SqlParameter("@REPEAT_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@REPEAT_DATE", SqlDbType.DateTime) , new SqlParameter("@ISRECEIVE", SqlDbType.Bit,1) , new SqlParameter("@RETURN_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@RETURN_DATE", SqlDbType.DateTime) , new SqlParameter("@RETURN_TRANCER", SqlDbType.VarChar,100) , new SqlParameter("@RETURN_TRANCNO", SqlDbType.VarChar,100) , new SqlParameter("@REMARK", SqlDbType.VarChar,1024) , new SqlParameter("@BSNO", SqlDbType.VarChar,100) , new SqlParameter("@CORPID", SqlDbType.VarChar,36) , new SqlParameter("@CREATEUSER", SqlDbType.VarChar,36) , //new SqlParameter("@CREATETIME", SqlDbType.DateTime) , new SqlParameter("@MODIFIEDUSER", SqlDbType.VarChar,36) , //new SqlParameter("@MODIFIEDTIME", SqlDbType.DateTime) , new SqlParameter("@RECEIPTNO", SqlDbType.VarChar,100) , new SqlParameter("@RECEIPTTYPE", SqlDbType.VarChar,100) , new SqlParameter("@RECEIVE_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@RECEIVE_DATE", SqlDbType.DateTime) , new SqlParameter("@SEND_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@SEND_DATE", SqlDbType.DateTime) , new SqlParameter("@TRANCER", SqlDbType.VarChar,100), new SqlParameter("@DOCNUM", SqlDbType.Int) }; parameters[0].Value = Guid.NewGuid().ToString().Trim(); parameters[1].Value = model.TRANCNO; parameters[2].Value = model.ISREPEAT; parameters[3].Value = model.REPEAT_MAN; //parameters[4].Value = model.REPEAT_DATE; parameters[4].Value = model.ISRECEIVE; parameters[5].Value = model.RETURN_MAN; //parameters[7].Value = model.RETURN_DATE; parameters[6].Value = model.RETURN_TRANCER; parameters[7].Value = model.RETURN_TRANCNO; parameters[8].Value = model.REMARK; parameters[9].Value = tempGID; parameters[10].Value = tempCOMPANYID; parameters[11].Value = model.CREATEUSER; //parameters[14].Value = model.CREATETIME; parameters[12].Value = model.MODIFIEDUSER; //parameters[16].Value = model.MODIFIEDTIME; parameters[13].Value = model.RECEIPTNO; parameters[14].Value = model.RECEIPTTYPE; parameters[15].Value = model.RECEIVE_MAN; //parameters[20].Value = model.RECEIVE_DATE; parameters[16].Value = model.SEND_MAN; //parameters[22].Value = model.SEND_DATE; parameters[17].Value = model.TRANCER; parameters[18].Value = model.DOCNUM; string REPEAT_DATE = ""; string REPEAT_DATE_name = ""; if (model.REPEAT_DATE != null) { if (model.REPEAT_DATE.ToString().IndexOf("0001") < 0 && model.REPEAT_DATE.ToString().Trim() != "") { REPEAT_DATE = "'" + model.REPEAT_DATE.ToString() + "',"; REPEAT_DATE_name = "REPEAT_DATE,"; } } string RETURN_DATE = ""; string RETURN_DATE_name = ""; if (model.RETURN_DATE != null) { if (model.RETURN_DATE.ToString().IndexOf("0001") < 0 && model.RETURN_DATE.ToString().Trim() != "") { RETURN_DATE = "'" + model.RETURN_DATE.ToString() + "',"; RETURN_DATE_name = "RETURN_DATE,"; } } string RECEIVE_DATE = ""; string RECEIVE_DATE_name = ""; if (model.RECEIVE_DATE != null) { if (model.RECEIVE_DATE.ToString().IndexOf("0001") < 0 && model.RECEIVE_DATE.ToString().Trim() != "") { RECEIVE_DATE = "'" + model.RECEIVE_DATE.ToString() + "',"; RECEIVE_DATE_name = "RECEIVE_DATE,"; } } string SEND_DATE = ""; string SEND_DATE_name = ""; if (model.SEND_DATE != null) { if (model.SEND_DATE.ToString().IndexOf("0001") < 0 && model.SEND_DATE.ToString().Trim() != "") { SEND_DATE = "'" + model.SEND_DATE.ToString() + "',"; SEND_DATE_name = "SEND_DATE,"; } } String strSql = "insert into op_receipt(GID,TRANCNO,ISREPEAT,REPEAT_MAN,{0}ISRECEIVE,RETURN_MAN,{2}RETURN_TRANCER,RETURN_TRANCNO,REMARK,BSNO,CORPID,CREATEUSER,CREATETIME,MODIFIEDUSER,MODIFIEDTIME,RECEIPTNO,RECEIPTTYPE,RECEIVE_MAN,{4}SEND_MAN,{6}TRANCER,DOCNUM)" + " values (@GID,@TRANCNO,@ISREPEAT,@REPEAT_MAN,{1}@ISRECEIVE,@RETURN_MAN,{3}@RETURN_TRANCER,@RETURN_TRANCNO,@REMARK,@BSNO,@CORPID,@CREATEUSER,getdate(),@MODIFIEDUSER,getdate(),@RECEIPTNO,@RECEIPTTYPE,@RECEIVE_MAN,{5}@SEND_MAN,{7}@TRANCER,@DOCNUM)"; strSql = String.Format(strSql, REPEAT_DATE_name, REPEAT_DATE, RETURN_DATE_name, RETURN_DATE, RECEIVE_DATE_name, RECEIVE_DATE, SEND_DATE_name, SEND_DATE); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql, parameters); #endregion } else { #region 更新op_receipt，并返回操作结果 SqlParameter[] parameters = { new SqlParameter("@GID", SqlDbType.VarChar,36) , new SqlParameter("@TRANCNO", SqlDbType.VarChar,100) , new SqlParameter("@ISREPEAT", SqlDbType.Bit,1) , new SqlParameter("@REPEAT_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@REPEAT_DATE", SqlDbType.DateTime) , new SqlParameter("@ISRECEIVE", SqlDbType.Bit,1) , new SqlParameter("@RETURN_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@RETURN_DATE", SqlDbType.DateTime) , new SqlParameter("@RETURN_TRANCER", SqlDbType.VarChar,100) , new SqlParameter("@RETURN_TRANCNO", SqlDbType.VarChar,100) , new SqlParameter("@REMARK", SqlDbType.VarChar,1024) , //new SqlParameter("@BSNO", SqlDbType.VarChar,100) , //new SqlParameter("@CORPID", SqlDbType.VarChar,36) , //new SqlParameter("@CREATEUSER", SqlDbType.VarChar,36) , //new SqlParameter("@CREATETIME", SqlDbType.DateTime) , new SqlParameter("@MODIFIEDUSER", SqlDbType.VarChar,36) , //new SqlParameter("@MODIFIEDTIME", SqlDbType.DateTime) , new SqlParameter("@RECEIPTNO", SqlDbType.VarChar,100) , new SqlParameter("@RECEIPTTYPE", SqlDbType.VarChar,100) , new SqlParameter("@RECEIVE_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@RECEIVE_DATE", SqlDbType.DateTime) , new SqlParameter("@SEND_MAN", SqlDbType.VarChar,100) , //new SqlParameter("@SEND_DATE", SqlDbType.DateTime) , new SqlParameter("@TRANCER", SqlDbType.VarChar,100), new SqlParameter("@DOCNUM", SqlDbType.Int) }; parameters[0].Value = model.GID; parameters[1].Value = model.TRANCNO; parameters[2].Value = model.ISREPEAT; parameters[3].Value = model.REPEAT_MAN; //parameters[4].Value = model.REPEAT_DATE; parameters[4].Value = model.ISRECEIVE; parameters[5].Value = model.RETURN_MAN; //parameters[7].Value = model.RETURN_DATE; parameters[6].Value = model.RETURN_TRANCER; parameters[7].Value = model.RETURN_TRANCNO; parameters[8].Value = model.REMARK; //parameters[9].Value = model.BSNO; //parameters[12].Value = model.CORPID; //parameters[13].Value = model.CREATEUSER; //parameters[14].Value = model.CREATETIME; parameters[9].Value = model.MODIFIEDUSER; //parameters[16].Value = model.MODIFIEDTIME; parameters[10].Value = model.RECEIPTNO; parameters[11].Value = model.RECEIPTTYPE; parameters[12].Value = model.RECEIVE_MAN; //parameters[20].Value = model.RECEIVE_DATE; parameters[13].Value = model.SEND_MAN; //parameters[22].Value = model.SEND_DATE; parameters[14].Value = model.TRANCER; parameters[15].Value = model.DOCNUM; // string REPEAT_DATE = ""; if (model.REPEAT_DATE != null) { if (model.REPEAT_DATE.ToString().IndexOf("0001") < 0 && model.REPEAT_DATE.ToString().Trim() != "") { REPEAT_DATE = "REPEAT_DATE='" + model.REPEAT_DATE.ToString() + "',"; } } string RETURN_DATE = ""; if (model.RETURN_DATE != null) { if (model.RETURN_DATE.ToString().IndexOf("0001") < 0 && model.RETURN_DATE.ToString().Trim() != "") { RETURN_DATE = "RETURN_DATE='" + model.RETURN_DATE.ToString() + "',"; } } string RECEIVE_DATE = ""; if (model.RECEIVE_DATE != null) { if (model.RECEIVE_DATE.ToString().IndexOf("0001") < 0 && model.RECEIVE_DATE.ToString().Trim() != "") { RECEIVE_DATE = "RECEIVE_DATE='" + model.RECEIVE_DATE.ToString() + "',"; } } string SEND_DATE = ""; if (model.SEND_DATE != null) { if (model.SEND_DATE.ToString().IndexOf("0001") < 0 && model.SEND_DATE.ToString().Trim() != "") { SEND_DATE = "SEND_DATE='" + model.SEND_DATE.ToString() + "',"; } } // StringBuilder strSql=new StringBuilder(); strSql.Append("update op_receipt set "); strSql.Append(" TRANCNO = @TRANCNO , "); strSql.Append(" ISREPEAT = @ISREPEAT , "); strSql.Append(" REPEAT_MAN = @REPEAT_MAN , "); strSql.Append(REPEAT_DATE); strSql.Append(" ISRECEIVE = @ISRECEIVE , "); strSql.Append(" RETURN_MAN = @RETURN_MAN , "); strSql.Append(RETURN_DATE); strSql.Append(" RETURN_TRANCER = @RETURN_TRANCER , "); strSql.Append(" RETURN_TRANCNO = @RETURN_TRANCNO , "); strSql.Append(" REMARK = @REMARK , "); //strSql.Append(" BSNO = @BSNO , "); //strSql.Append(" CORPID = @CORPID , "); //strSql.Append(" CREATEUSER = @CREATEUSER , "); //strSql.Append(" CREATETIME = @CREATETIME , "); strSql.Append(" MODIFIEDUSER = @MODIFIEDUSER , "); strSql.Append(" MODIFIEDTIME = getdate() , "); strSql.Append(" RECEIPTNO = @RECEIPTNO , "); strSql.Append(" RECEIPTTYPE = @RECEIPTTYPE , "); strSql.Append(" RECEIVE_MAN = @RECEIVE_MAN , "); strSql.Append(RECEIVE_DATE); strSql.Append(" SEND_MAN = @SEND_MAN , "); strSql.Append(SEND_DATE); strSql.Append(" TRANCER = @TRANCER, "); strSql.Append(" DOCNUM = @DOCNUM "); strSql.Append(" where GID=@GID "); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), parameters); #endregion } } sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } static public int DeleteReceipt(string tempGID) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append("Delete from op_receipt where GID in (" + tempGID + ")"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion #region 查询费用数据 static public List GetDataList(string strCondition, string optype, string userid, string username, string companyid) { var rangstr = GetRangDAStr(userid, username, companyid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } StringBuilder strSql = new StringBuilder(); strSql.Append("select GID, TRANCNO,DOCNUM,ISREPEAT, REPEAT_MAN, dbo.trimdate(REPEAT_DATE) as REPEAT_DATE, ISRECEIVE, RETURN_MAN, dbo.trimdate(RETURN_DATE) as RETURN_DATE, RETURN_TRANCER, RETURN_TRANCNO, REMARK, BSNO, CORPID, CREATEUSER,dbo.trimtime( CREATETIME) CREATETIME, MODIFIEDUSER, dbo.trimtime(MODIFIEDTIME) MODIFIEDTIME, RECEIPTNO, RECEIPTTYPE, RECEIVE_MAN, dbo.trimdate(RECEIVE_DATE) as RECEIVE_DATE, SEND_MAN, dbo.trimdate(SEND_DATE) as SEND_DATE, TRANCER from op_receipt "); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(" order by CREATETIME desc"); return SetData(strSql); } private static List SetData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpReceiptEntity data = new MsOpReceiptEntity(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]);//惟一编号 data.BSNO = Convert.ToString(reader["BSNO"]);//关联编号/业务编号BSNO data.RECEIPTNO = Convert.ToString(reader["RECEIPTNO"]);//单据编号 data.RECEIPTTYPE = Convert.ToString(reader["RECEIPTTYPE"]);//单据类型 data.RECEIVE_MAN = Convert.ToString(reader["RECEIVE_MAN"]);//接单人 data.RECEIVE_DATE = Convert.ToString(reader["RECEIVE_DATE"]);//接单日期 if (reader["DOCNUM"] != DBNull.Value) data.DOCNUM = Convert.ToInt32(reader["DOCNUM"]);//是否有更改单 //if (reader["RECEIVE_DATE"] != DBNull.Value) // data.RECEIVE_DATE = Convert.ToDateTime(reader["RECEIVE_DATE"]);//接单日期 data.SEND_MAN = Convert.ToString(reader["SEND_MAN"]);//送单人 data.SEND_DATE = Convert.ToString(reader["SEND_DATE"]);//接单日期 //if (reader["SEND_DATE"] != DBNull.Value) // data.SEND_DATE = Convert.ToDateTime(reader["SEND_DATE"]);//送单日期 data.TRANCER = Convert.ToString(reader["TRANCER"]);//送单的快递公司 data.TRANCNO = Convert.ToString(reader["TRANCNO"]);//送单的快递单号 data.ISREPEAT = Convert.ToBoolean(reader["ISREPEAT"]);//是否回单 data.REPEAT_MAN = Convert.ToString(reader["REPEAT_MAN"]);//回单人 data.REPEAT_DATE = Convert.ToString(reader["REPEAT_DATE"]);//回单日期 //if (reader["REPEAT_DATE"] != DBNull.Value) // data.REPEAT_DATE = Convert.ToDateTime(reader["REPEAT_DATE"]);//回单日期 data.ISRECEIVE = Convert.ToBoolean(reader["ISRECEIVE"]);//是否还单 data.RETURN_MAN = Convert.ToString(reader["RETURN_MAN"]);//还单人 data.RETURN_DATE = Convert.ToString(reader["RETURN_DATE"]);//还单日期 //if (reader["RETURN_DATE"] != DBNull.Value) // data.RETURN_DATE = Convert.ToDateTime(reader["RETURN_DATE"]);//还单日期 data.RETURN_TRANCER = Convert.ToString(reader["RETURN_TRANCER"]);//还单的快递公司 data.RETURN_TRANCNO = Convert.ToString(reader["RETURN_TRANCNO"]);//还单的快递单号 data.REMARK = Convert.ToString(reader["REMARK"]);//备注 data.CORPID = Convert.ToString(reader["CORPID"]);//分公司代码 data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);//创建人GID data.CREATETIME = Convert.ToString(reader["CREATETIME"]);//创建时间 //if (reader["CREATETIME"] != DBNull.Value) // data.CREATETIME = Convert.ToDateTime(reader["CREATETIME"]);//创建时间 data.MODIFIEDUSER = Convert.ToString(reader["MODIFIEDUSER"]);//更改操作人GID data.MODIFIEDTIME = Convert.ToString(reader["MODIFIEDTIME"]);//更改操作时间 //if (reader["MODIFIEDTIME"] != DBNull.Value) // data.MODIFIEDTIME = Convert.ToDateTime(reader["MODIFIEDTIME"]);//更改操作时间 #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } #endregion static public List GetBLDataList(string strCondition, int start, int limit, out int totel, string userid, string username, string companyid, string sort = null) { var rangstr = GetRangDAStr(userid, username, companyid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT * from (SELECT row_number() over ("); var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by r.CREATETIME "); } strSql.Append(@") as num , "); strSql.Append("r.GID, r.TRANCNO,r.DOCNUM,r.ISREPEAT, r.REPEAT_MAN, dbo.trimdate(r.REPEAT_DATE) as REPEAT_DATE, r.ISRECEIVE,"); strSql.Append("r.RETURN_MAN, dbo.trimdate(r.RETURN_DATE) as RETURN_DATE, r.RETURN_TRANCER, r.RETURN_TRANCNO, r.REMARK, r.BSNO, r.CORPID,"); strSql.Append("r.CREATEUSER,dbo.trimtime( r.CREATETIME) CREATETIME, r.MODIFIEDUSER, dbo.trimtime(r.MODIFIEDTIME) MODIFIEDTIME, r.RECEIPTNO,"); strSql.Append("r.RECEIPTTYPE, r.RECEIVE_MAN, dbo.trimdate(r.RECEIVE_DATE) as RECEIVE_DATE, r.SEND_MAN, dbo.trimdate(r.SEND_DATE) as SEND_DATE, r.TRANCER "); strSql.Append(",b.PARENTID,b.OPTYPE,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.FEESTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME,b.BSSOURCE,b.BSSOURCEDETAIL,b.CUSTOMNO"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD,b.DESTINATION"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.APPLYNO"); strSql.Append(",b.DOC,b.SALE,b.FORWARDER "); strSql.Append(" from op_receipt r left join v_op_bill b on (b.BSNO=R.BSNO)"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } strSql.Append(@")as t "); strSql.Append(string.Format("where t.num>{0} and t.num<={1} order by t.num ", start, start + limit)); // totel = getTotalCount(strCondition); return SetBLData(strSql); } static public string GetBLDataListStr(string strCondition,string userid, string username, string companyid, string sort = null) { var rangstr = GetRangDAStr(userid, username, companyid); if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strCondition = strCondition + " and " + rangstr; } else { strCondition = rangstr; } } StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append("r.GID, r.TRANCNO,r.DOCNUM,r.ISREPEAT, r.REPEAT_MAN, dbo.trimdate(r.REPEAT_DATE) as REPEAT_DATE, r.ISRECEIVE,"); strSql.Append("r.RETURN_MAN, dbo.trimdate(r.RETURN_DATE) as RETURN_DATE, r.RETURN_TRANCER, r.RETURN_TRANCNO, r.REMARK, r.BSNO, r.CORPID,"); strSql.Append("r.CREATEUSER,dbo.trimtime( r.CREATETIME) CREATETIME, r.MODIFIEDUSER, dbo.trimtime(r.MODIFIEDTIME) MODIFIEDTIME, r.RECEIPTNO,"); strSql.Append("r.RECEIPTTYPE, r.RECEIVE_MAN, dbo.trimdate(r.RECEIVE_DATE) as RECEIVE_DATE, r.SEND_MAN, dbo.trimdate(r.SEND_DATE) as SEND_DATE, r.TRANCER "); strSql.Append(",b.PARENTID,b.OPTYPE,b.OPLB,b.OPLBNAME,b.BSSTATUS,b.FEESTATUS,b.ACCDATE,b.MBLNO,b.HBLNO,b.CUSTNO"); strSql.Append(",b.CUSTOMERNAME,b.BSSOURCE,b.BSSOURCEDETAIL,b.CUSTOMNO"); strSql.Append(",b.VESSEL,b.VOYNO,b.ETD,b.PORTLOAD,b.DESTINATION"); strSql.Append(",b.PORTDISCHARGE,b.INPUTBY,b.OP,b.CUSTSERVICE,b.APPLYNO"); strSql.Append(",b.DOC,b.SALE,b.FORWARDER "); strSql.Append(" from op_receipt r left join v_op_bill b on (b.BSNO=R.BSNO)"); if (strCondition.Trim() != String.Empty) { strSql.Append(" where " + strCondition); } return strSql.ToString(); } public static int getTotalCount(string strCondition) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(r.GID) "); strSql.Append(" from op_receipt r left join v_op_bill b on (b.BSNO=R.BSNO) "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } int cnt = 0; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { cnt = Convert.ToInt32(reader[0]); } } return cnt; } private static List SetBLData(StringBuilder strSql) { var bodyList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsOpReceipt data = new MsOpReceipt(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]);//惟一编号 data.BSNO = Convert.ToString(reader["BSNO"]);//关联编号/业务编号BSNO data.RECEIPTNO = Convert.ToString(reader["RECEIPTNO"]);//单据编号 data.RECEIPTTYPE = Convert.ToString(reader["RECEIPTTYPE"]);//单据类型 data.RECEIVE_MAN = Convert.ToString(reader["RECEIVE_MAN"]);//接单人 data.RECEIVE_DATE = Convert.ToString(reader["RECEIVE_DATE"]);//接单日期 if (reader["DOCNUM"] != DBNull.Value) data.DOCNUM = Convert.ToInt32(reader["DOCNUM"]);//是否有更改单 //if (reader["RECEIVE_DATE"] != DBNull.Value) // data.RECEIVE_DATE = Convert.ToDateTime(reader["RECEIVE_DATE"]);//接单日期 data.SEND_MAN = Convert.ToString(reader["SEND_MAN"]);//送单人 data.SEND_DATE = Convert.ToString(reader["SEND_DATE"]);//接单日期 //if (reader["SEND_DATE"] != DBNull.Value) // data.SEND_DATE = Convert.ToDateTime(reader["SEND_DATE"]);//送单日期 data.TRANCER = Convert.ToString(reader["TRANCER"]);//送单的快递公司 data.TRANCNO = Convert.ToString(reader["TRANCNO"]);//送单的快递单号 data.ISREPEAT = Convert.ToBoolean(reader["ISREPEAT"]);//是否回单 data.REPEAT_MAN = Convert.ToString(reader["REPEAT_MAN"]);//回单人 data.REPEAT_DATE = Convert.ToString(reader["REPEAT_DATE"]);//回单日期 //if (reader["REPEAT_DATE"] != DBNull.Value) // data.REPEAT_DATE = Convert.ToDateTime(reader["REPEAT_DATE"]);//回单日期 data.ISRECEIVE = Convert.ToBoolean(reader["ISRECEIVE"]);//是否还单 data.RETURN_MAN = Convert.ToString(reader["RETURN_MAN"]);//还单人 data.RETURN_DATE = Convert.ToString(reader["RETURN_DATE"]);//还单日期 //if (reader["RETURN_DATE"] != DBNull.Value) // data.RETURN_DATE = Convert.ToDateTime(reader["RETURN_DATE"]);//还单日期 data.RETURN_TRANCER = Convert.ToString(reader["RETURN_TRANCER"]);//还单的快递公司 data.RETURN_TRANCNO = Convert.ToString(reader["RETURN_TRANCNO"]);//还单的快递单号 data.REMARK = Convert.ToString(reader["REMARK"]);//备注 data.CORPID = Convert.ToString(reader["CORPID"]);//分公司代码 data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]);//创建人GID data.CREATETIME = Convert.ToString(reader["CREATETIME"]);//创建时间 //if (reader["CREATETIME"] != DBNull.Value) // data.CREATETIME = Convert.ToDateTime(reader["CREATETIME"]);//创建时间 data.MODIFIEDUSER = Convert.ToString(reader["MODIFIEDUSER"]);//更改操作人GID data.MODIFIEDTIME = Convert.ToString(reader["MODIFIEDTIME"]);//更改操作时间 if (reader["ETD"] != DBNull.Value) data.ETD = Convert.ToDateTime(reader["ETD"]).ToString("yyyy-MM-dd"); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.OPLB = Convert.ToString(reader["OPLB"]); data.OPTYPE = Convert.ToString(reader["OPTYPE"]); data.OPLBNAME = Convert.ToString(reader["OPLBNAME"]); data.MBLNO = Convert.ToString(reader["MBLNO"]); data.HBLNO = Convert.ToString(reader["HBLNO"]); data.CUSTNO = Convert.ToString(reader["CUSTNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.VESSEL = Convert.ToString(reader["VESSEL"]); data.VOYNO = Convert.ToString(reader["VOYNO"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.DESTINATION = Convert.ToString(reader["DESTINATION"]); data.INPUTBY = Convert.ToString(reader["INPUTBY"]); data.OP = Convert.ToString(reader["OP"]); data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]); data.DOC = Convert.ToString(reader["DOC"]); data.SALE = Convert.ToString(reader["SALE"]); data.BSSOURCE = Convert.ToString(reader["BSSOURCE"]); data.BSSOURCEDETAIL = Convert.ToString(reader["BSSOURCEDETAIL"]); data.APPLYNO = Convert.ToString(reader["APPLYNO"]); data.FORWARDER= Convert.ToString(reader["FORWARDER"]); //if (reader["MODIFIEDTIME"] != DBNull.Value) // data.MODIFIEDTIME = Convert.ToDateTime(reader["MODIFIEDTIME"]);//更改操作时间 #endregion bodyList.Add(data); } reader.Close(); } return bodyList; } public static DBResult SongDan(MsOpReceiptEntity headData,List bodyList, string userid, string username) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); var cmdUpdate = db.GetSqlStringCommand( @"update op_receipt set SEND_MAN=@SEND_MAN,SEND_DATE=@SEND_DATE,TRANCER=@TRANCER,TRANCNO=@TRANCNO WHERE GID=@GID "); try { if (bodyList != null) { foreach (var enumValue in bodyList) { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdUpdate, "@SEND_MAN", DbType.String, headData.SEND_MAN); db.AddInParameter(cmdUpdate, "@SEND_DATE", DbType.String, headData.SEND_DATE); db.AddInParameter(cmdUpdate, "@TRANCER", DbType.String, headData.TRANCER); db.AddInParameter(cmdUpdate, "@TRANCNO", DbType.String, headData.TRANCNO); db.ExecuteNonQuery(cmdUpdate, tran); } } tran.Commit(); } catch (Exception e) { tran.Rollback(); result.Success = false; result.Message = "更新出现错误，请重试或联系系统管理员" + e.Message; return result; } } result.Success = true; result.Message = "更新成功"; return result; } public static DBResult HuiDan(MsOpReceiptEntity headData, List bodyList, string userid, string username) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); var cmdUpdate = db.GetSqlStringCommand( @"update op_receipt set ISREPEAT=@ISREPEAT,REPEAT_MAN=@REPEAT_MAN,REPEAT_DATE=@REPEAT_DATE WHERE GID=@GID "); try { if (bodyList != null) { foreach (var enumValue in bodyList) { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdUpdate, "@ISREPEAT", DbType.Boolean, headData.ISREPEAT); db.AddInParameter(cmdUpdate, "@REPEAT_MAN", DbType.String, headData.REPEAT_MAN); db.AddInParameter(cmdUpdate, "@REPEAT_DATE", DbType.String, headData.REPEAT_DATE); db.ExecuteNonQuery(cmdUpdate, tran); } } tran.Commit(); } catch (Exception e) { tran.Rollback(); result.Success = false; result.Message = "更新出现错误，请重试或联系系统管理员" + e.Message; return result; } } result.Success = true; result.Message = "更新成功"; return result; } public static DBResult HuanDan(MsOpReceiptEntity headData, List bodyList, string userid, string username) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); var cmdUpdate = db.GetSqlStringCommand( @"update op_receipt set ISRECEIVE=@ISRECEIVE,RETURN_MAN=@RETURN_MAN,RETURN_DATE=@RETURN_DATE WHERE GID=@GID "); try { if (bodyList != null) { foreach (var enumValue in bodyList) { cmdUpdate.Parameters.Clear(); db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdUpdate, "@ISRECEIVE", DbType.Boolean, headData.ISRECEIVE); db.AddInParameter(cmdUpdate, "@RETURN_MAN", DbType.String, headData.RETURN_MAN); db.AddInParameter(cmdUpdate, "@RETURN_DATE", DbType.String, headData.RETURN_DATE); db.ExecuteNonQuery(cmdUpdate, tran); } } tran.Commit(); } catch (Exception e) { tran.Rollback(); result.Success = false; result.Message = "更新出现错误，请重试或联系系统管理员" + e.Message; return result; } } result.Success = true; result.Message = "更新成功"; return result; } #region 权限设置 public static List GetReceiptOpRang(string userid) { var strSql = new StringBuilder(); strSql.Append(GetRangDASQLStr(userid)); Database db = DatabaseFactory.CreateDatabase(); var evList = new List(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var evData = new OPCODE(); evData.OPID = Convert.ToString(reader["OPCODE"]); evList.Add(evData); } reader.Close(); } return evList; } public static string GetRangDASQLStr(string userid) { string str = ""; var strSql = new StringBuilder(); strSql.Append("SELECT VISIBLERANGE,OPERATERANGE from VW_User_Authority where [NAME]='modOpReceiptManagement' 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 (operaterange == "4") { str = "select GID AS OPCODE from [user] where GID='" + userid + "'"; } else if (operaterange == "3") { str = "select GID AS OPCODE from [user] where GID='" + userid + "'"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var companyid = rangeDa.GetCORPID(userid); var deptname = rangeDa.GetDEPTNAME(userid); str = " select USERID AS OPCODE from user_company where COMPANYID='" + companyid + "' and USERID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')"; } else if (visiblerange == "1") { var rangeDa = new RangeDA(); var companyid = rangeDa.GetCORPID(userid); str = " select USERID AS OPCODE from user_company where COMPANYID='" + companyid + "'"; } else { str = "select GID AS OPCODE from [user] "; } return str; } public static string GetRangDAStr(string userid, string username, string companyid) { string str = ""; string visiblerange = "4"; string operaterange = "4"; var strSql = new StringBuilder(); strSql.Append("SELECT VISIBLERANGE,OPERATERANGE from VW_User_Authority where [NAME]='modOpReceiptManagement' and USERID='" + userid + "' and ISDELETE=0"); 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 = " MODIFIEDUSER='" + userid + "'"; } else if (visiblerange == "3") { str = " MODIFIEDUSER='" + userid + "'"; } else if (visiblerange == "2") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); str = " MODIFIEDUSER in (select USERID from user_company where COMPANYID='" + companyid + "') and MODIFIEDUSER in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')"; } else if (visiblerange == "1") { str = " MODIFIEDUSER in (select USERID from user_company where COMPANYID='" + companyid + "') "; } else if (visiblerange == "0") { str = " 1=1 "; } return str; } #endregion #region 单据确认，并返回操作结果 static public int onSetRECEIPT(string strBSNO, string tempGID, string onSetType, string tempUserID, string tempUserName) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); if (onSetType.Trim() == "SEND")//送单 { strSql.Append("update op_receipt set SEND_MAN='" + tempUserName + "',SEND_DATE=getdate(),MODIFIEDUSER='" + tempUserID + "',MODIFIEDTIME=getdate() where GID in (" + tempGID + ")"); } else if (onSetType.Trim() == "RECEIVE")//接单 { strSql.Append("update op_receipt set RECEIVE_MAN='" + tempUserName + "',RECEIVE_DATE=getdate(),MODIFIEDUSER='" + tempUserID + "',MODIFIEDTIME=getdate() where GID in (" + tempGID + ")"); } else if (onSetType.Trim() == "REPEAT")//回单 { strSql.Append("update op_receipt set ISREPEAT=1,REPEAT_MAN='" + tempUserName + "',REPEAT_DATE=getdate(),MODIFIEDUSER='" + tempUserID + "',MODIFIEDTIME=getdate() where GID in (" + tempGID + ")"); } else if (onSetType.Trim() == "RETURN")//还单 { strSql.Append("update op_receipt set ISRECEIVE=1,RETURN_MAN='" + tempUserName + "',RETURN_DATE=getdate(),MODIFIEDUSER='" + tempUserID + "',MODIFIEDTIME=getdate() where GID in (" + tempGID + ")"); } SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } #endregion public static DBResult UpdateFileData(string filename,string name,string BSNO, string userid,string type,string DOCUMENTATTACHEDCODE,string DOCUMENTATTACHEDNO,string CUSTNO) { 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 Receipt_Doc (GID,BSNO,URL,Driect_URL,MODIFIEDUSER,MODIFIEDTIME,RECEIPTTYPE,DOCUMENTATTACHEDCODE,DOCUMENTATTACHEDNO,R_GID) values (@GID,@BSNO,@URL,@Driect_URL,@MODIFIEDUSER,@MODIFIEDTIME,@RECEIPTTYPE,@DOCUMENTATTACHEDCODE,@DOCUMENTATTACHEDNO,@R_GID) "); cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString()); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, BSNO); db.AddInParameter(cmdInsert, "@URL", DbType.String, name); db.AddInParameter(cmdInsert, "@Driect_URL", DbType.String, filename); db.AddInParameter(cmdInsert, "@MODIFIEDUSER", DbType.String, userid); db.AddInParameter(cmdInsert, "@MODIFIEDTIME", DbType.String, DateTime.Now.ToString()); db.AddInParameter(cmdInsert, "@RECEIPTTYPE", DbType.String,type); db.AddInParameter(cmdInsert, "@DOCUMENTATTACHEDCODE", DbType.String, DOCUMENTATTACHEDCODE); db.AddInParameter(cmdInsert, "@DOCUMENTATTACHEDNO", DbType.String, DOCUMENTATTACHEDNO); db.AddInParameter(cmdInsert, "@R_GID", DbType.String, CUSTNO); 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 UpdateFileData(List revdoc, string userid) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdInsert = db.GetSqlStringCommand( @"update Receipt_Doc set URL=@URL,Driect_URL=@Driect_URL,MODIFIEDUSER=@MODIFIEDUSER,MODIFIEDTIME=@MODIFIEDTIME,RECEIPTTYPE=@RECEIPTTYPE,DOCUMENTATTACHEDCODE=@DOCUMENTATTACHEDCODE,DOCUMENTATTACHEDNO=@DOCUMENTATTACHEDNO ,ISPUBLIC=@ISPUBLIC,BSNO=@BSNO WHERE GID=@GID "); if (revdoc != null) { foreach (var enumValue in revdoc) { cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, enumValue.GID); db.AddInParameter(cmdInsert, "@URL", DbType.String, enumValue.URL); db.AddInParameter(cmdInsert, "@Driect_URL", DbType.String,enumValue.Driect_URL); db.AddInParameter(cmdInsert, "@MODIFIEDUSER", DbType.String, userid); db.AddInParameter(cmdInsert, "@MODIFIEDTIME", DbType.String, DateTime.Now.ToString()); db.AddInParameter(cmdInsert, "@RECEIPTTYPE", DbType.String, enumValue.RECEIPTTYPE); db.AddInParameter(cmdInsert, "@DOCUMENTATTACHEDCODE", DbType.String, enumValue.DOCUMENTATTACHEDCODE); db.AddInParameter(cmdInsert, "@DOCUMENTATTACHEDNO", DbType.String, enumValue.DOCUMENTATTACHEDNO); db.AddInParameter(cmdInsert, "@ISPUBLIC", DbType.Boolean, enumValue.ISPUBLIC); db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.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; } static public int DeleteUploadFile(string tempGId) { int iResult = 0; using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { var strSql = new StringBuilder(); strSql.Append("Delete from Receipt_Doc where "); strSql.Append(" GId in (" + tempGId + ")"); SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString()); sqlTran.Commit(); iResult = 1;//状态为1表示插入成功 } catch (Exception execError) { iResult = -1;//有异常,插入失败 sqlTran.Rollback(); iResult = -2;//插入异常,事务已回滚成功 throw execError; } finally { SqlHelper.CloseConnection(); } } return iResult; } } internal class MsOpReceiptDealException : Exception { internal MsOpReceiptDealException(string message) : base(message) { } } }