using System; using System.Data; using System.Data.Common; using System.Collections; using System.Collections.Generic; using System.Text; using DSWeb.MvcShipping.Models.MsOpBillProfit; using DSWeb.MvcShipping.Models.InfoSaleProfitSharePlan; 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 DSWeb.MvcShipping.DAL.MsInfoSaleProfitShareSet; using DSWeb.MvcShipping.DAL.MsSysParamSet; using DSWeb.Common.DB; using System.Linq; namespace DSWeb.MvcShipping.DAL.MsRptZjTotalDAL { public class MsRptZjTotalDAL { public static string GetRangDAStr(string tb, string userid, string usercode, 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]='modZjTotalreportRange' 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='" + usercode + "' OR B.SALE='" + usercode + "')"; } 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"]) + "'"; } else { str = str + " or B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + 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; } #region 泰邦 资金占用 static public List GetTBDataList(string strCondition, int start, int limit, string userid, string usercode, string companyid, string sort = null) { var rangstr = GetRangDAStr("index", userid, usercode, companyid); var strConditionAll = strCondition; if (!string.IsNullOrEmpty(rangstr)) { if (!string.IsNullOrEmpty(strCondition)) { strConditionAll = strCondition + " and " + rangstr; } else { strConditionAll = rangstr; } } //限制显示的业务 目前只计算 海运进口 海运出口 if (!string.IsNullOrEmpty(strCondition)) { strConditionAll = strCondition + " and B.OPLBNAME in('海运进口','海运出口' ) "; } else { strConditionAll = " B.OPLBNAME in('海运进口','海运出口' ) "; } var strSql = new StringBuilder(); strSql.Append(" if object_id('tempdb..##SALEPROFITSHARE') is not null Begin DROP TABLE ##SALEPROFITSHARE End "); strSql.Append("SELECT * into ##SALEPROFITSHARE FROM (SELECT B.BSNO, B.OPLB,B.OPTYPE,B.OPLBNAME, B.BSTYPE,B.CUSTOMERNAME, B.CUSTNO, B.MBLNO, B.HBLNO,B.VESSEL, B.VOYNO, B.INPUTBY, B.CREATETIME, B.BSSTATUS, B.SALE, B.OP, B.DOC,"); strSql.Append("B.ETD,B.OPDATE,B.CUSTOMDATE,B.FEESTATUSREF,B.BLFRT,B.BSSOURCEDETAIL,B.STLNAME"); strSql.Append(",B.STLDATE "); strSql.Append(",B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER, B.BSSOURCE, B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.TEU,B.CNTRTOTAL,B.CORPID"); strSql.Append(",B.CNTR1,B.CNTR2,B.CNTR3,B.CNTR4,B.CNTR5,B.CNTR6,B.CNTR7,B.CNTR8,B.CNTR9,B.CNTR10,B.OTCNTR,B.TRADETYPE,B.GOODSNAME,B.FEESTATUS"); strSql.Append(",B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR"); strSql.Append(",B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.TAXPROFIT,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR "); strSql.Append(",T.SETTLETIME,T.STLTTLDR MONTHSTLTTLDR");//,f.ISFEE //该客户的 Info_Client_ACCDATE.BGNDATE (生效日期) 减 每票货的开船日期得出是否超过一年以内 //一年以内则提成30% 一年之外为15% //此处ACTUALDAY为ETD-帐期生效日期。大于1年则ACTUALDAY小于-365 strSql.Append(",DATEDIFF([day],b.ETD,(select min(bgndate) from Info_Client_ACCDATE where LINKGID=(select gid from Info_Client where SHORTNAME=b.CUSTOMERNAME)))+1 ACTUALDAY"); strSql.Append(",DATEDIFF([day],ISNULL(T.SETTLETIME,GETDATE()),(select min(bgndate) from Info_Client_ACCDATE where LINKGID=(select gid from Info_Client where SHORTNAME=b.CUSTOMERNAME))) NODAY"); strSql.Append(",B.ACCDATE ETDMONTH,'0' ZYTTL "); strSql.Append(" FROM v_op_bill_gain_zh B "); strSql.Append("LEFT JOIN V_MAX_SETTLEMENTTIME_BSDR T ON (T.BSNO=B.BSNO) "); //strSql.Append("LEFT JOIN op_sale_proftfee f ON (f.BSNO=B.BSNO) where B.TTLDR<>0 "); strSql.Append(" where 1=1 "); //strSql.Append(" AND B.TTLDR=B.STLTTLDR "); if (!string.IsNullOrEmpty(strConditionAll)) { strSql.Append(" and " + strConditionAll); } strSql.Append(" ) AS V "); // var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by ETD,SALE,SETTLETIME,CREATETIME DESC"); // strSql.Append(" order by SALE,SETTLETIME,ETD,CREATETIME DESC"); } strSql.Append(" SELECT * FROM ##SALEPROFITSHARE "); return SetTBData(strCondition, strSql, start, limit, 12, 0.12M); } private static List SetTBData(string strCondition, StringBuilder strSql, int start, int limit, decimal days, decimal rate) { //DateTime CurDateBegin = Convert.ToDateTime(Month + "-01"); //DateTime CurDateEnd = CurDateBegin.AddMonths(1).AddDays(-1); var saleprofitlist = MsInfoSaleProfitShareSetDAL.GetSaleProfitDetailDataList(""); var saleprofitcqlist = MsInfoSaleProfitShareSetDAL.GetSaleProfitCqDataList(""); var tmpsaleprofitlist = saleprofitlist; var POORDAY = 0; var MONTHPOORDAY = 0; SqlCommand cmd = new SqlCommand(); cmd.CommandText = strSql.ToString(); cmd.CommandTimeout = 1200000; //要加这一句 var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { MsOpBillProfit data = new MsOpBillProfit(); #region Set DB data to Object data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号 data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]); data.ACCDATE = Convert.ToString(reader["ACCDATE"]); data.ETDMONTH = Convert.ToString(reader["ETDMONTH"]); data.OPTYPE = Convert.ToString(reader["OPTYPE"]); data.OPLB = Convert.ToString(reader["OPLB"]); 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.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); data.VESSEL = Convert.ToString(reader["VESSEL"]); data.VOYNO = Convert.ToString(reader["VOYNO"]); data.CARRIER = Convert.ToString(reader["CARRIER"]); data.FORWARDER = Convert.ToString(reader["FORWARDER"]); data.ETD = Convert.ToString(reader["ETD"]); data.OPDATE = Convert.ToString(reader["OPDATE"]); data.CUSTOMDATE = Convert.ToString(reader["CUSTOMDATE"]); data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]); data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]); data.LANE = Convert.ToString(reader["LANE"]); data.BLFRT = Convert.ToString(reader["BLFRT"]); data.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]); 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.CORPID = Convert.ToString(reader["CORPID"]); data.STLNAME = Convert.ToString(reader["STLNAME"]); data.STLDATE = Convert.ToString(reader["STLDATE"]); if (reader["RMBDR"] != DBNull.Value) data.RMBDR = Convert.ToDecimal(reader["RMBDR"]); if (reader["RMBCR"] != DBNull.Value) data.RMBCR = Convert.ToDecimal(reader["RMBCR"]); if (reader["STLRMBDR"] != DBNull.Value) data.STLRMBDR = Convert.ToDecimal(reader["STLRMBDR"]); if (reader["STLRMBCR"] != DBNull.Value) data.STLRMBCR = Convert.ToDecimal(reader["STLRMBCR"]); if (reader["RMBNODR"] != DBNull.Value) data.RMBNODR = Convert.ToDecimal(reader["RMBNODR"]); if (reader["RMBNOCR"] != DBNull.Value) data.RMBNOCR = Convert.ToDecimal(reader["RMBNOCR"]); if (reader["RMBPROFIT"] != DBNull.Value) data.RMBPROFIT = Convert.ToDecimal(reader["RMBPROFIT"]); if (reader["USDDR"] != DBNull.Value) data.USDDR = Convert.ToDecimal(reader["USDDR"]); if (reader["USDCR"] != DBNull.Value) data.USDCR = Convert.ToDecimal(reader["USDCR"]); if (reader["STLUSDDR"] != DBNull.Value) data.STLUSDDR = Convert.ToDecimal(reader["STLUSDDR"]); if (reader["STLUSDCR"] != DBNull.Value) data.STLUSDCR = Convert.ToDecimal(reader["STLUSDCR"]); if (reader["USDNODR"] != DBNull.Value) data.USDNODR = Convert.ToDecimal(reader["USDNODR"]); if (reader["USDNOCR"] != DBNull.Value) data.USDNOCR = Convert.ToDecimal(reader["USDNOCR"]); if (reader["USDPROFIT"] != DBNull.Value) data.USDPROFIT = Convert.ToDecimal(reader["USDPROFIT"]); if (reader["OTDR"] != DBNull.Value) data.OTDR = Convert.ToDecimal(reader["OTDR"]); if (reader["OTCR"] != DBNull.Value) data.OTCR = Convert.ToDecimal(reader["OTCR"]); if (reader["STLOTDR"] != DBNull.Value) data.STLOTDR = Convert.ToDecimal(reader["STLOTDR"]); if (reader["STLOTCR"] != DBNull.Value) data.STLOTCR = Convert.ToDecimal(reader["STLOTCR"]); if (reader["OTNODR"] != DBNull.Value) data.OTNODR = Convert.ToDecimal(reader["OTNODR"]); if (reader["OTNOCR"] != DBNull.Value) data.OTNOCR = Convert.ToDecimal(reader["OTNOCR"]); if (reader["OTPROFIT"] != DBNull.Value) data.OTPROFIT = Convert.ToDecimal(reader["OTPROFIT"]); if (reader["TTLDR"] != DBNull.Value) data.TTLDR = Convert.ToDecimal(reader["TTLDR"]); if (reader["TTLCR"] != DBNull.Value) data.TTLCR = Convert.ToDecimal(reader["TTLCR"]); if (reader["STLTTLDR"] != DBNull.Value) data.STLTTLDR = Convert.ToDecimal(reader["STLTTLDR"]); if (reader["MONTHSTLTTLDR"] != DBNull.Value) data.MONTHSTLTTLDR = Convert.ToDecimal(reader["MONTHSTLTTLDR"]); if (reader["STLTTLCR"] != DBNull.Value) data.STLTTLCR = Convert.ToDecimal(reader["STLTTLCR"]); if (reader["TTLNODR"] != DBNull.Value) data.TTLNODR = Convert.ToDecimal(reader["TTLNODR"]); if (reader["TTLNOCR"] != DBNull.Value) data.TTLNOCR = Convert.ToDecimal(reader["TTLNOCR"]); //利润 if (reader["TTLPROFIT"] != DBNull.Value) data.TTLPROFIT = Convert.ToDecimal(reader["TTLPROFIT"]); if (reader["NODAY"] != DBNull.Value) data.NODAY = Convert.ToInt32(reader["NODAY"]); data.TiChengBiLi = 0M; data.TiChengBiLiStr = ""; data.TiCheng = 0; //if (reader["ACTUALDAY"] != DBNull.Value) //{ // data.ACTUALDAY = -Convert.ToInt32(reader["ACTUALDAY"]); // if (data.ACTUALDAY <= 365) // { // data.TiChengBiLi = 0.3M; // data.TiChengBiLiStr = "30%"; // data.TiCheng = data.TTLPROFIT * data.TiChengBiLi; // } // if (data.ACTUALDAY > 365) // { // data.TiChengBiLi = 0.15M; // data.TiChengBiLiStr = "15%"; // data.TiCheng = data.TTLPROFIT * data.TiChengBiLi; // } // // //} if (reader["ZYTTL"] != DBNull.Value) data.ZYTTL = Convert.ToDecimal(reader["ZYTTL"]); if (reader["TAXPROFIT"] != DBNull.Value) data.TAXPROFIT = Convert.ToDecimal(reader["TAXPROFIT"]); if (data.TTLPROFIT < 0) { data.ACTUALDAY = 0; } decimal beginplan = 0; decimal endplan = 0; decimal SHAREPR = 0; data.COMPR = data.TiCheng - data.TAXPROFIT - data.ZYTTL; data.PROFITRATE = Convert.ToString(reader["PROFITRATE"]); if (reader["SETTLETIME"] != DBNull.Value) data.SETTLETIME = Convert.ToDateTime(reader["SETTLETIME"]).ToString("yyyy-MM-dd"); //data.ISSALEFEE = Convert.ToString(reader["ISFEEREF"]); #endregion headList.Add(data); } reader.Close(); } headList = headList.Skip(start).Take(limit).ToList(); GetZJFee(ref headList); return headList; } /// /// 向数据中加入资金占用的费用 /// /// 用于参数和返回的数据集 /// 资金开始计算利息的天数，默认60 /// 资金利率 默认12% public static void GetZJFee(ref List headList,int days=60,decimal feerate=0.12M) { var cdc = new CommonDataContext(); var bsnolist = headList.Select(s => s.BSNO).Distinct().ToList(); //对于每个业务而言 寻找它的应收费用 // var feeList = cdc.v_ch_fee_settlementinfo.Where(x => bsnolist.Contains(x.BSNO) && x.FEETYPE == 1).ToList(); foreach (var head in headList) { var amountfee = 0M; if (string.IsNullOrWhiteSpace(head.ETD)) { continue; } var 需计算资金成本的费用List = feeList.Where(x => head.BSNO == x.BSNO && (x.SETTLETIME == null || x.SETTLETIME > Convert.ToDateTime(head.ETD).AddDays(days-1))).ToList(); if (需计算资金成本的费用List != null && 需计算资金成本的费用List.Count > 0) { foreach (var item in 需计算资金成本的费用List) { var feedays = 0; if (item.SETTLETIME == null) { feedays = (DateTime.Now - Convert.ToDateTime(head.ETD)).Days - days; amountfee += Convert.ToDecimal(item.EXCHANGERATE * item.AMOUNT * 0.12M / 365); } else { if (item.SETTLEMENT == item.AMOUNT) { feedays = ((DateTime)item.SETTLETIME - Convert.ToDateTime(head.ETD)).Days - days; amountfee += Convert.ToDecimal((item.SETTLEAMOUNT) * 0.12M / 365); } else { feedays = ((DateTime)item.SETTLETIME - Convert.ToDateTime(head.ETD)).Days - days; amountfee += Convert.ToDecimal((item.SETTLEAMOUNT) * 0.12M / 365); feedays = (DateTime.Now - Convert.ToDateTime(head.ETD)).Days - days; amountfee += Convert.ToDecimal((item.EXCHANGERATE * item.AMOUNT - item.SETTLEAMOUNT) * 0.12M / 365); } } } } if (amountfee != 0M) head.ZJFEE = amountfee; } } #endregion } }