using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.MsExchangesUnit; 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.IO; namespace DSWeb.MvcShipping.DAL.MsExchangesUnitDAL { public class MsExchangesUnitDAL { #region 查询 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; } } // String strSql = "SELECT *,MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=[info_client].MODIFIEDUSER) FROM [info_client] where 1=1"; if (!string.IsNullOrEmpty(strCondition)) { strSql += " and " + strCondition; } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql += " order by " + sortstring; } else { strSql += " order by [CODENAME],[NAME]"; } 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; } } // String strSql = "SELECT *,MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=[info_client].MODIFIEDUSER) FROM [info_client] where 1=1"; if (!string.IsNullOrEmpty(strCondition)) { strSql += " and " + strCondition; } var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql += " order by " + sortstring; } else { strSql += " order by [CODENAME],[NAME]"; } return strSql.ToString(); } static public MsExchangesUnitEntity GetData(string strCondition) { String strSql = "SELECT *,MODIFIEDUSERNAME=(select top 1 SHOWNAME from [user] where gid=[info_client].MODIFIEDUSER) FROM [info_client] where 1=1"; if (!string.IsNullOrEmpty(strCondition)) { strSql += " and " + strCondition; } var list = SetData(strSql); if (list.Count > 0) { return list[0]; } return new MsExchangesUnitEntity(); } private static List SetData(String strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { MsExchangesUnitEntity data = new MsExchangesUnitEntity(); #region Set DB data to Object data.GID = (reader["GID"] == null ? "" : Convert.ToString(reader["GID"])); data.CODENAME = (reader["CODENAME"] == null ? "" : Convert.ToString(reader["CODENAME"]));//客户代码 data.SHORTNAME = (reader["SHORTNAME"] == null ? "" : Convert.ToString(reader["SHORTNAME"]));//客户简称 data.DESCRIPTION = (reader["DESCRIPTION"] == null ? "" : Convert.ToString(reader["DESCRIPTION"]));//客户中文名称或描述信息 data.NAME = (reader["NAME"] == null ? "" : Convert.ToString(reader["NAME"]));//客户英文简称 data.EnFullName = (reader["EnFullName"] == null ? "" : Convert.ToString(reader["EnFullName"]));//客户英文全称 data.STATUS = (reader["STATUS"] == null ? 0 : Convert.ToInt32(reader["STATUS"]));//客户状态 data.ADDR = (reader["ADDR"] == null ? "" : Convert.ToString(reader["ADDR"]));//通讯地址 data.EMAIL = (reader["EMAIL"] == null ? "" : Convert.ToString(reader["EMAIL"]));//邮箱 data.WEB = (reader["WEB"] == null ? "" : Convert.ToString(reader["WEB"]));//网页 data.TEL = (reader["TEL"] == null ? "" : Convert.ToString(reader["TEL"]));//电话 data.FAX = (reader["FAX"] == null ? "" : Convert.ToString(reader["FAX"]));//传真 data.CHIEF = (reader["CHIEF"] == null ? "" : Convert.ToString(reader["CHIEF"]));//负责人 data.SALE = (reader["SALE"] == null ? "" : Convert.ToString(reader["SALE"]));//所属揽货人 data.OP = (reader["OP"] == null ? "" : Convert.ToString(reader["OP"]));//所属揽货人 data.DOC = (reader["DOC"] == null ? "" : Convert.ToString(reader["DOC"]));//所属单证 data.ISCARRIER = (reader["ISCARRIER"] == null ? false : Convert.ToBoolean(reader["ISCARRIER"]));//是否船公司 data.ISBOOKING = (reader["ISBOOKING"] == null ? false : Convert.ToBoolean(reader["ISBOOKING"]));//是否订舱公司 data.ISYARD = (reader["ISYARD"] == null ? false : Convert.ToBoolean(reader["ISYARD"]));//是否场站 data.ISTRUCK = (reader["ISTRUCK"] == null ? false : Convert.ToBoolean(reader["ISTRUCK"]));//是否车队 data.ISCONTROLLER = (reader["ISCONTROLLER"] == null ? false : Convert.ToBoolean(reader["ISCONTROLLER"]));//是否委托单位 data.ISCUSTOM = (reader["ISCUSTOM"] == null ? false : Convert.ToBoolean(reader["ISCUSTOM"]));//是否报关行 data.ISAGENT = (reader["ISAGENT"] == null ? false : Convert.ToBoolean(reader["ISAGENT"]));//是否代理(国外) data.ISAGENTCN = (reader["ISAGENTCN"] == null ? false : Convert.ToBoolean(reader["ISAGENTCN"]));//是否代理(国内) data.ISEXPRESS = (reader["ISEXPRESS"] == null ? false : Convert.ToBoolean(reader["ISEXPRESS"]));//是否快递公司 data.ISAIRLINES = (reader["ISAIRLINES"] == null ? false : Convert.ToBoolean(reader["ISAIRLINES"]));//是否航空公司 data.ISSHIPPER = (reader["ISSHIPPER"] == null ? false : Convert.ToBoolean(reader["ISSHIPPER"]));//是否发货人 data.ISCONSIGNEE = (reader["ISCONSIGNEE"] == null ? false : Convert.ToBoolean(reader["ISCONSIGNEE"]));//是否收货人 data.ISNOTIFYPARTY = (reader["ISNOTIFYPARTY"] == null ? false : Convert.ToBoolean(reader["ISNOTIFYPARTY"]));//是否通知人 data.ISWAREHOUSE = (reader["ISWAREHOUSE"] == null ? false : Convert.ToBoolean(reader["ISWAREHOUSE"]));//是否仓库 data.COUNTRY = (reader["COUNTRY"] == null ? "" : Convert.ToString(reader["COUNTRY"]));//国家 data.PROVINCE = (reader["PROVINCE"] == null ? "" : Convert.ToString(reader["PROVINCE"]));//省或州 data.CITY = (reader["CITY"] == null ? "" : Convert.ToString(reader["CITY"]));//城市 data.CORPID = (reader["CORPID"] == null ? "" : Convert.ToString(reader["CORPID"]));//CORPID data.BLCONTENT = (reader["BLCONTENT"] == null ? "" : Convert.ToString(reader["BLCONTENT"]));//BLCONTENT data.ISSTOP = (reader["ISSTOP"] == null ? false : Convert.ToBoolean(reader["ISSTOP"]));//是否停用该客户信息 data.BillRises1 = (reader["BillRises1"] == null ? "" : Convert.ToString(reader["BillRises1"]));//发票抬头1 data.BillRises2 = (reader["BillRises2"] == null ? "" : Convert.ToString(reader["BillRises2"]));//发票抬头2 data.USDBillRises = (reader["USDBillRises"] == null ? "" : Convert.ToString(reader["USDBillRises"]));//美元支票抬头 data.RMBBillRises = (reader["RMBBillRises"] == null ? "" : Convert.ToString(reader["RMBBillRises"]));//人民币支票抬头 data.RMBBank = (reader["RMBBank"] == null ? "" : Convert.ToString(reader["RMBBank"]));//人民币开户银行 data.RMBAccount = (reader["RMBAccount"] == null ? "" : Convert.ToString(reader["RMBAccount"]));//人民币账号 data.RMBOnlineNO = (reader["RMBOnlineNO"] == null ? "" : Convert.ToString(reader["RMBOnlineNO"]));//人民币联机行号 data.USDBank = (reader["USDBank"] == null ? "" : Convert.ToString(reader["USDBank"]));//美元开户银行 data.USDAccount = (reader["USDAccount"] == null ? "" : Convert.ToString(reader["USDAccount"]));//美元账号 data.USDOnlineNO = (reader["USDOnlineNO"] == null ? "" : Convert.ToString(reader["USDOnlineNO"]));//美元联机行号 data.CustomAttributes1 = (reader["CustomAttributes1"] == null ? "" : Convert.ToString(reader["CustomAttributes1"]));//自定义属性1 data.CustomAttributes2 = (reader["CustomAttributes2"] == null ? "" : Convert.ToString(reader["CustomAttributes2"]));//自定义属性2 data.USDMaxAmountCredit = (reader["USDMaxAmountCredit"] == null ? 0 : Convert.ToDecimal(reader["USDMaxAmountCredit"]));//美元信用最大金额 data.RMBMaxAmountCredit = (reader["RMBMaxAmountCredit"] == null ? 0 : Convert.ToDecimal(reader["RMBMaxAmountCredit"]));//人民币信用最大金额 data.MaxAmountCredit = (reader["MaxAmountCredit"] == null ? 0 : Convert.ToDecimal(reader["MaxAmountCredit"]));//信用最大金额（RMB+USD） data.STLNAME = (reader["STLNAME"] == null ? "" : Convert.ToString(reader["STLNAME"]));//结算方式编码 data.REMARK = (reader["REMARK"] == null ? "" : Convert.ToString(reader["REMARK"]));//备注 data.USDExchangeRate = (reader["USDExchangeRate"] == null ? 0 : Convert.ToDecimal(reader["USDExchangeRate"]));//美金汇率 data.STLDATE = (reader["STLDATE"] == null ? "" : Convert.ToString(reader["STLDATE"]));//月结算时间 data.STLFIRSTHALFDATE = (reader["STLFIRSTHALFDATE"] == null ? "" : Convert.ToString(reader["STLFIRSTHALFDATE"]));//上半月结算时间 data.STLMIDDLEDATE = (reader["STLMIDDLEDATE"] == null ? "" : Convert.ToString(reader["STLMIDDLEDATE"]));//下半月结算时间 data.STLDATEPJ = (reader["STLDATEPJ"] == null ? "" : Convert.ToString(reader["STLDATEPJ"]));//STLDATEPJ data.LEVEL = (reader["LEVEL"] == null ? "" : Convert.ToString(reader["LEVEL"]));//LEVEL data.LOGINNAME = (reader["LOGINNAME"] == null ? "" : Convert.ToString(reader["LOGINNAME"]));//LOGINNAME data.LOGINPASSWORD = (reader["LOGINPASSWORD"] == null ? "" : Convert.ToString(reader["LOGINPASSWORD"]));//LOGINPASSWORD data.QQ = (reader["QQ"] == null ? "" : Convert.ToString(reader["QQ"]));//QQ data.MSN = (reader["MSN"] == null ? "" : Convert.ToString(reader["MSN"]));//MSN data.ISWHARF = (reader["ISWHARF"] == null ? false : Convert.ToBoolean(reader["ISWHARF"]));//ISWHARF data.ISSHIPPINGAGENT = (reader["ISSHIPPINGAGENT"] == null ? false : Convert.ToBoolean(reader["ISSHIPPINGAGENT"]));//ISSHIPPINGAGENT data.FARCODE = (reader["FARCODE"] == null ? "" : Convert.ToString(reader["FARCODE"]));//FARCODE data.FAPCODE = (reader["FAPCODE"] == null ? "" : Convert.ToString(reader["FAPCODE"]));//FAPCODE data.ISINSURE = (reader["ISINSURE"] == null ? false : Convert.ToBoolean(reader["ISINSURE"]));//ISINSURE data.ISLEASING = (reader["ISLEASING"] == null ? false : Convert.ToBoolean(reader["ISLEASING"]));//ISLEASING data.ISTRADINGAGENCY = (reader["ISTRADINGAGENCY"] == null ? false : Convert.ToBoolean(reader["ISTRADINGAGENCY"]));//ISTRADINGAGENCY data.ISOTHER = (reader["ISOTHER"] == null ? false : Convert.ToBoolean(reader["ISOTHER"]));//ISOTHER data.OTHERS = (reader["OTHERS"] == null ? "" : Convert.ToString(reader["OTHERS"]));//OTHERS data.UNITPRICE = (reader["UNITPRICE"] == null ? "" : Convert.ToString(reader["UNITPRICE"]));//UNITPRICE data.REGISTRATIONNO = (reader["REGISTRATIONNO"] == null ? "" : Convert.ToString(reader["REGISTRATIONNO"]));//REGISTRATIONNO data.ORDERNO = (reader["ORDERNO"] == null ? "" : Convert.ToString(reader["ORDERNO"]));//ORDERNO data.ISENTERP = (reader["ISENTERP"] == null ? false : Convert.ToBoolean(reader["ISENTERP"]));//ISENTERP data.TAXNO = (reader["TAXNO"] == null ? "" : Convert.ToString(reader["TAXNO"]));//TAXNO data.EDICODE = (reader["EDICODE"] == null ? "" : Convert.ToString(reader["EDICODE"]));//EDICODE data.EDICODE2 = (reader["EDICODE2"] == null ? "" : Convert.ToString(reader["EDICODE2"]));//EDICODE2 data.EDICODE3 = (reader["EDICODE3"] == null ? "" : Convert.ToString(reader["EDICODE3"]));//EDICODE3 data.OPNAME = (reader["OPNAME"] == null ? "" : Convert.ToString(reader["OPNAME"]));//处理人 if (reader["OPTIME"] != DBNull.Value) data.OPTIME = Convert.ToDateTime(reader["OPTIME"]);//处理时间 data.MODIFIEDUSER = (reader["MODIFIEDUSER"] == null ? "" : Convert.ToString(reader["MODIFIEDUSER"]));//更新人gid if (reader["MODIFIEDTIME"] != DBNull.Value) data.MODIFIEDTIME = Convert.ToDateTime(reader["MODIFIEDTIME"]); data.MODIFIEDUSERNAME = (reader["MODIFIEDUSERNAME"] == null ? "" : Convert.ToString(reader["MODIFIEDUSERNAME"]));//更改人 #endregion headList.Add(data); } reader.Close(); } return headList; } #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]='modExchangesUnit' 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 = " 1<0"; } else if (visiblerange == "3")//个人 { str = " CORPID='" + companyid + "' and (SALE like '%" + username + "%' or OP like '%" + username + "%' or DOC like '%" + username + "%' or [OPNAME] like '%" + username + "%' or MODIFIEDUSER in(select gid from [user] where SHOWNAME like '%" + username + "%'))"; } else if (visiblerange == "2")//部门 { str = " 1<0"; } else if (visiblerange == "1")//公司 { str = " CORPID='" + companyid + "'"; } else if (visiblerange == "0")//全部 { str = " 1=1 "; } return str; } #endregion #region 添加日志 public static DBResult setLog(MsExchangesUnitEntity modeldata, string strUserID) { DBResult result = new DBResult(); if (modeldata == null) { result.Success = false; result.Message = "没有输入任何需要修改的数据"; return result; } // Database db = DatabaseFactory.CreateDatabase(); using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction)) { try { string strSql = "insert into sys_log(NAME,LOGTYPE,LOGCONTENT,CREATEUSER) values('删除信息','更新操作','" + modeldata.NAME + " 添加删除标志','" + strUserID + "')"; int existVal = SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString(), null); sqlTran.Commit(); } catch (Exception) { sqlTran.Rollback(); result.Success = false; result.Message = "操作出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作数据成功"; return result; } #endregion #region 删除 public static DBResult Delete(MsExchangesUnitEntity headData) { DBResult result = new DBResult(); T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); string strSql = "SELECT top 1 CUSTOMERNAME from VW_Settlement where CUSTOMERNAME in ('" + headData.SHORTNAME + "') group by CUSTOMERNAME"; string strCUSTOMERNAME = T_ALL_DA.GetStrSQL("CUSTOMERNAME", strSql); if (strCUSTOMERNAME.Trim() != "") { result.Success = false; result.Message = "业务信息中已启用往来单位，不允许删除！"; return result; } // Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand("delete from [info_client] where GID='" + headData.GID + "'"); //var cmdDelete = db.GetSqlStringCommand("update [company] set ISDELETED=1 where GID='" + headData.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 } }