using System; using System.Data; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.CRM_QUOTATION; 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; using HcUtility.Core; using DSWeb.Areas.CommMng.DAL; using System.Web; using DSWeb.TruckMng.Comm.Cookie; using DSWeb.MvcShipping.Controllers; namespace DSWeb.MvcShipping.DAL.CRM_QUOTATIONDAL { public class CRM_QUOTATIONDAL { #region 查询 static public List GetDataList ( string strCondition, string userid, string usercode, string companyid, string sort = null, string load = 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 cq.GID,cq.BLTYPE,cq.BLSTATUS,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=39 and EnumValueID=cq.BLSTATUS) BLSTATUS_REF "); strSql.Append(" ,cq.CLIENTGID,cq.SHORTNAME,cq.DESCRIPTION,cq.SALE,dbo.trimdate(cq.QUOTATIONDATE) QUOTATIONDATE,cq.CORPID,cq.PORTLOAD,cq.PORTDISCHARGE,cq.LANE "); strSql.Append(" ,cq.CARRIER,dbo.trimdate(cq.ETD) ETD,dbo.trimdate(cq.ETA) ETA,cq.ENTREPORT,cq.TT,cq.GOODSNAME,cq.REMARK "); strSql.Append(" ,cq.CREATEUSER,dbo.trimtime(cq.CREATETIME) CREATETIME,cq.MODIFIEDUSER,dbo.trimtime(cq.MODIFIEDTIME) MODIFIEDTIME,cq.CONTACTNAME "); strSql.Append(" ,dbo.trimdate(cq.ASKDATE) ASKDATE,dbo.trimdate(cq.VALIDDATE) VALIDDATE,dbo.trimdate(cq.VALIDDATE_END) VALIDDATE_END,datename(WEEKDAY,ETD) WEEK,convert(bigint ,cq.TimeMark) as TimeMark "); strSql.Append(" ,icc.TEL,icc.EMAIL,cl.PORT+'-'+cl.CNAME PORTLOAD_REF,cd.PORT+'-'+cd.EDICODE PORTDISCHARGE_REF,CQ.CTN,CQ.CTNNUM,CQ.CTN2,CQ.CTNNUM2 "); strSql.Append(" ,isnull(CQ._20GP,0) _20GP,isnull(CQ._40GP,0) _40GP,isnull(CQ._40HC,0) _40HC,isnull(CQ._45,0) _45"); strSql.Append(" ,(select count(*) from CRM_QUOTATION_LINK L where L.CUSTCQGID=CQ.GID and EXISTS(select 1 from CRM_QUOTATION where GID=L.CARRIERCQGID and BLSTATUS in(2,3))) GETBJ"); strSql.Append(" from crm_quotation cq "); strSql.Append(" left join info_client_contact icc on icc.LINKID=CLIENTGID and CONTACTNAME=icc.SHOWNAME "); strSql.Append(" left join code_loadport cl on cl.EDICODE=cq.PORTLOAD "); strSql.Append(" left join code_disport cd on cd.EDICODE=cq.PORTDISCHARGE "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } else { strSql.Append(" "); } // var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by QUOTATIONDATE desc"); } return SetData(strSql); } static public CRM_QUOTATIONmb GetData ( string strCondition ) { var list = CRM_QUOTATIONDAL.GetDataList(strCondition, "", "", "", "", ""); if (list.Count > 0) return list[0]; return new CRM_QUOTATIONmb(); } 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()) { CRM_QUOTATIONmb data = new CRM_QUOTATIONmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.BLTYPE = Convert.ToString(reader["BLTYPE"]); data.BLSTATUS = Convert.ToString(reader["BLSTATUS"]); data.BLSTATUS_REF = Convert.ToString(reader["BLSTATUS_REF"]); data.CLIENTGID = Convert.ToString(reader["CLIENTGID"]); data.SHORTNAME = Convert.ToString(reader["SHORTNAME"]); data.DESCRIPTION = Convert.ToString(reader["DESCRIPTION"]); data.SALE = Convert.ToString(reader["SALE"]); data.ASKDATE = Convert.ToString(reader["ASKDATE"]); data.QUOTATIONDATE = Convert.ToString(reader["QUOTATIONDATE"]); data.CORPID = Convert.ToString(reader["CORPID"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.LANE = Convert.ToString(reader["LANE"]); data.CARRIER = Convert.ToString(reader["CARRIER"]); data.ETD = Convert.ToString(reader["ETD"]); data.ETA = Convert.ToString(reader["ETA"]); data.ENTREPORT = Convert.ToString(reader["ENTREPORT"]); data.TT = Convert.ToString(reader["TT"]); data.GOODSNAME = Convert.ToString(reader["GOODSNAME"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATETIME = Convert.ToString(reader["CREATETIME"]); data.MODIFIEDUSER = Convert.ToString(reader["MODIFIEDUSER"]); data.MODIFIEDTIME = Convert.ToString(reader["MODIFIEDTIME"]); data.CONTACTNAME = Convert.ToString(reader["CONTACTNAME"]); data.VALIDDATE = Convert.ToString(reader["VALIDDATE"]); data.WEEK = Convert.ToString(reader["WEEK"]); data.TimeMark = Convert.ToDecimal(reader["TimeMark"]); data.PORTLOAD_REF = Convert.ToString(reader["PORTLOAD_REF"]); data.PORTDISCHARGE_REF = Convert.ToString(reader["PORTDISCHARGE_REF"]); data.TEL = Convert.ToString(reader["TEL"]); data.EMAIL = Convert.ToString(reader["EMAIL"]); data.CTN = Convert.ToString(reader["CTN"]); data.CTNNUM = Convert.ToString(reader["CTNNUM"]); data.CTN2 = Convert.ToString(reader["CTN2"]); data.CTNNUM2 = Convert.ToString(reader["CTNNUM2"]); data._20GP = Convert.ToString(reader["_20GP"]); data._40GP = Convert.ToString(reader["_40GP"]); data._40HC = Convert.ToString(reader["_40HC"]); data._45 = Convert.ToString(reader["_45"]); data.VALIDDATE_END = Convert.ToString(reader["VALIDDATE_END"]); data.GETBJ = Convert.ToString(reader["GETBJ"]); #endregion headList.Add(data); } reader.Close(); } return headList; } public static string getfeestatus(string feestatusint) { var result = ""; if (feestatusint == "0:") { return "审核通过"; } else if (feestatusint == "1:") { return "录入状态"; } else if (feestatusint == "2:") { return "提交审核"; } else if (feestatusint == "8:") { return "部分结算"; } else if (feestatusint == "9:") { return "结算完毕"; } else if (feestatusint == "") { return "未录入"; } else if (feestatusint == "3:") { return "申请修改"; } else if (feestatusint == "4:") { return "申请删除"; } else if (feestatusint == "6:") { return "驳回提交"; } else if (feestatusint == "7:") { return "驳回申请"; } else if (feestatusint.IndexOf("9:") > -1) { return "部分结算"; } else if (feestatusint.IndexOf("0:") > -1) { return "部分审核"; } else if (feestatusint.IndexOf("2:") > -1) { return "部分提交"; } return result; } #endregion #region 船公司明细 static public List GetCARRIERList ( string strCondition ) { var strSql = new StringBuilder(); strSql.Append(" select GID,LINKGID,LINKGID2,CARRIER,FREEDAYS,REMARK,dbo.trimdate(VALIDDATE) VALIDDATE,dbo.trimdate(VALIDDATE_END) VALIDDATE_END "); strSql.Append(" from crm_quotation_CARRIER "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } else { strSql.Append(" order by LINKGID2,CARRIER "); } return SetCARRIERData(strSql); } private static List SetCARRIERData ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { CRM_QUOTATION_CARRIERmb data = new CRM_QUOTATION_CARRIERmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.LINKGID = Convert.ToString(reader["LINKGID"]); data.LINKGID2 = Convert.ToString(reader["LINKGID2"]); data.CARRIER = Convert.ToString(reader["CARRIER"]); data.FREEDAYS = Convert.ToString(reader["FREEDAYS"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.VALIDDATE = Convert.ToString(reader["VALIDDATE"]); data.VALIDDATE_END = Convert.ToString(reader["VALIDDATE_END"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 报价明细 static public List GetDetailList ( string strCondition ) { var strSql = new StringBuilder(); strSql.Append(" select GID,LINKGID,LINKGID2,FEETYPE,FEENAME,UNIT,UNITPRICE,CURRENCY,CARRIER,REMARK"); strSql.Append(" ,CREATEUSER,dbo.trimtime(CREATETIME) CREATETIME,MODIFIEDUSER,dbo.trimtime(MODIFIEDTIME) MODIFIEDTIME "); strSql.Append(" ,dbo.trimDate(ETD) ETD ,CTN,CTNNUM,CTN2,CTNPRICE,CTNPRICE2,(select SHORTNAME from crm_quotation where gid=LINKGID) SHORTNAME "); strSql.Append(" ,ISUSE,_20GP,_40GP,_40HC,_45"); strSql.Append(",(select SHORTNAME from crm_quotation where gid=LINKGID)+'_'+CARRIER CARRIER_REF "); strSql.Append(" from crm_quotation_detail "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } else { strSql.Append(" order by CARRIER,FEENAME "); } return SetDetailData(strSql); } private static List SetDetailData ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { CRM_QUOTATION_DETAILmb data = new CRM_QUOTATION_DETAILmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.LINKGID = Convert.ToString(reader["LINKGID"]); data.LINKGID2 = Convert.ToString(reader["LINKGID2"]); data.FEETYPE = Convert.ToString(reader["FEETYPE"]); data.FEENAME = Convert.ToString(reader["FEENAME"]); data.UNIT = Convert.ToString(reader["UNIT"]); data.UNITPRICE = Convert.ToString(reader["UNITPRICE"]); data.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.CARRIER = Convert.ToString(reader["CARRIER"]); data.CARRIER_REF = Convert.ToString(reader["CARRIER_REF"]); data.REMARK = Convert.ToString(reader["REMARK"]); data.CREATEUSER = Convert.ToString(reader["CREATEUSER"]); data.CREATETIME = Convert.ToString(reader["CREATETIME"]); data.MODIFIEDUSER = Convert.ToString(reader["MODIFIEDUSER"]); data.MODIFIEDTIME = Convert.ToString(reader["MODIFIEDTIME"]); data.ETD = Convert.ToString(reader["ETD"]); data.CTN = Convert.ToString(reader["CTN"]); data.CTNNUM = Convert.ToString(reader["CTNNUM"]); data.CTN2 = Convert.ToString(reader["CTN2"]); data.CTNPRICE = Convert.ToString(reader["CTNPRICE"]); data.CTNPRICE2 = Convert.ToString(reader["CTNPRICE2"]); data.SHORTNAME = Convert.ToString(reader["SHORTNAME"]); data.ISUSE = Convert.ToString(reader["ISUSE"]); data._20GP = Convert.ToString(reader["_20GP"]); data._40GP = Convert.ToString(reader["_40GP"]); data._40HC = Convert.ToString(reader["_40HC"]); data._45 = Convert.ToString(reader["_45"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 删除 public static DBResult DeleteDetail(string GID) { var result = new DBResult(); Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDeletebill = db.GetSqlStringCommand("delete from crm_quotation_detail where GID='" + GID + "'"); db.ExecuteNonQuery(cmdDeletebill, tran); tran.Commit(); } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "删除出现错误，请重试或联系系统管理员"; return result; } } result.Success = true; result.Message = "操作成功"; return result; } #endregion #region 编码生成 public static String getCodeRule(string strRULENAME, 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=1"); 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_Seae] where " + strfield + " like '" + strCONTENT.Trim() + "%' order by " + strfield + " DESC"); if (strRULENOLENGTH == "") { strRULENOLENGTH = "1"; } else { strRULENOLENGTH = strRULENOLENGTH.Replace(strCONTENT.Trim(), ""); inum = int.Parse(strRULENOLENGTH.Trim()); inum = inum + 1; strRULENOLENGTH = inum.ToString(); } int j = iRULENOLENGTH - strRULENOLENGTH.Length; for (int i = 1; i <= j; i++) { strRULENOLENGTH = "0" + strRULENOLENGTH; } // strCONTENT = strCONTENT + strRULENOLENGTH; return strCONTENT; } } } return ""; } #endregion #region 判断是否有费用 static public int GetRdCount(string strCondition) { var strSql = new StringBuilder(); strSql.Append("SELECT Count(BSNO) AS CT from op_airn (NOLOCK) "); 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; } public static bool GetFeeCount(string GID) { var isfee = false; var strSql = new StringBuilder(); strSql.Append("Select count(*) as count "); strSql.Append(" from crm_QUOTATION_DETAIL "); strSql.Append(" where LINKGID='" + GID + "'"); 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; } #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]='modOpAirnList' 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=2"; } else if (visiblerange == "3") { str = " (B.OP='" + username + "' OR B.SALE='" + username + "' OR B.CUSTSERVICE='" + username + "')"; } else if (visiblerange == "2") { if (tb == "index") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); var userstr = new StringBuilder(); userstr.Append(" 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 + "')"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' "; } else { str = str + " or B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' "; }; } str = str + ")"; reader.Close(); } } else { str = " UPPER(B.Corpid)='" + companyid + "'"; } } else if (visiblerange == "1") { str = " UPPER(B.Corpid)='" + companyid + "'"; } return str; } #endregion #region 直接执行sql命令 static public int ExecSql(string StrSql) { Database db = DatabaseFactory.CreateDatabase(); var _count = db.ExecuteNonQuery(CommandType.Text, StrSql); return _count; } #endregion } }