Ejemplo n.º 1
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int status, string key, string exchangeCode)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "E.ExchangeId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " ExchangeId,ExchangeName,ExchangeCode,bd.StatusName ";
            select.TableName = " dbo.Exchange e inner join dbo.BDStatusDetail bd on e.ExchangeStatus = bd.DetailId ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" bd.StatusId = 1 ");

            if (status > 0)
                sb.AppendFormat(" and ExchangeStatus = {0}", status);
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and ExchangeName like '%{0}%'", key);
            if (!string.IsNullOrEmpty(exchangeCode))
                sb.AppendFormat(" and ExchangeCode like '%{0}%'", exchangeCode);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="assetName"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int status, string assetName)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "a.AssetId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " a.AssetName,a.MUId,a.AssetStatus,a.AssetId,bd.StatusName,mu.MUName as MUName,a.AmountPerHand ";
            select.TableName = " dbo.Asset a left join BDStatusDetail bd on a.AssetStatus=bd.DetailId left join dbo.MeasureUnit mu on a.MUId=mu.MUId and bd.StatusId = 1";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" bd.StatusId = 1 ");

            if (status > 0)
                sb.AppendFormat(" and  a.AssetStatus = {0}", status);
            if (!string.IsNullOrEmpty(assetName))
                sb.AppendFormat(" and a.AssetName like '%{0}%'", assetName);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, string key, int status)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "r.RoleId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " r.RoleId,r.RoleName,r.RoleStatus,bd.StatusName  ";
            select.TableName = string.Format(" dbo.Role r left join NFMT_Basic.dbo.BDStatusDetail bd on bd.DetailId = r.RoleStatus and bd.StatusId = {0} ", (int)Common.StatusTypeEnum.通用状态);

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" 1=1 ");
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and r.RoleName like '%{0}%'", key);
            if (status>0)
                sb.AppendFormat(" and r.RoleStatus = {0}", status);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 4
0
        /// <summary>
        /// 分页获取查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="rateDate"></param>
        /// <param name="currency1"></param>
        /// <param name="currency2"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, DateTime rateDate, int currency1, int currency2)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "r.RateId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = "r.RateId,r.CreateTime,c1.CurrencyName as CurrencyName_1,r.RateValue,c2.CurrencyName as CurrencyName_2,bd.StatusName ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" dbo.Rate r ");
            sb.Append(" left join dbo.Currency c1 on r.FromCurrencyId = c1.CurrencyId ");
            sb.Append(" left join dbo.Currency c2 on r.ToCurrencyId = c2.CurrencyId ");
            sb.AppendFormat(" left join dbo.BDStatusDetail bd on bd.DetailId = r.RateStatus and bd.StatusId = {0} ", (int)Common.StatusTypeEnum.通用状态);
            select.TableName = sb.ToString();

            sb.Clear();

            sb.Append(" 1=1 ");

            if (currency1 > 0)
                sb.AppendFormat(" r.FromCurrencyId = {0} ", currency1);
            if (currency2 > 0)
                sb.AppendFormat(" r.ToCurrencyId = {0} ", currency2);
            if (rateDate > Common.DefaultValue.DefaultTime)
                sb.AppendFormat(" and r.CreateTime between '{0}' and '{1}' ", rateDate.ToString(), rateDate.AddDays(1).ToString());

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 5
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <param name="corpId"></param>
        /// <param name="empIdFrom"></param>
        /// <param name="empIdTo"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, string key, int corpId, int empIdFrom)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();
            int dataStatus = (int)Common.StatusEnum.已生效;

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "C.ContactId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " C.ContactId,C.ContactName,C.ContactCode,C.ContactTel,C.ContactFax,C.ContactAddress,Co.CorpName,BD.StatusName,e.ECId ";
            select.TableName = string.Format(" dbo.Contact C left join dbo.Corporation Co on C.CompanyId = Co.CorpId left join NFMT_Basic.dbo.BDStatusDetail BD on BD.DetailId = C.ContactStatus left join dbo.EmployeeContact e on C.ContactId = e.ContactId and e.EmpId = {0} and e.RefStatus = {1} ", empIdFrom, (int)Common.StatusEnum.已生效);

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" 1=1 ");

            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and C.ContactName like '%{0}%'", key);
            if (corpId > 0)
                sb.AppendFormat(" and C.CompanyId = {0}", corpId);
            if (empIdFrom > 0)
                sb.AppendFormat(" and C.ContactId in (select ContactId from dbo.EmployeeContact where EmpId = {0} and RefStatus = {1}) ", empIdFrom, dataStatus);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 6
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int status, string key)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "C.CurrencyId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " C.CurrencyId,C.CurrencyName,C.CurencyShort,BD.StatusName,C.CurrencyFullName,C.CurrencyStatus  ";
            select.TableName = " dbo.Currency C left join dbo.BDStatusDetail BD on C.CurrencyStatus = BD.DetailId ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" BD.StatusId = 1");

            if (status > 0)
                sb.AppendFormat(" and C.CurrencyStatus = {0}", status);
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and C.CurrencyName like '%{0}%'", key);
            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 7
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="exchange"></param>
        /// <param name="firstTradeDate"></param>
        /// <param name="lastTradeDate"></param>
        /// <param name="asset"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, DateTime firstTradeDate, DateTime lastTradeDate, int exchageId, int selFuturesCodeStatus)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "fc.FuturesCodeId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " FuturesCodeId,ec.ExchangeName as ExchangeName,FirstTradeDate,LastTradeDate,CONVERT(varchar(20),Convert(int,CodeSize))+mu.MUName as CodeSize,c.CurrencyName as CurrencyName,FuturesCodeStatus,TradeCode,bd.StatusName as StatusName,fc.AssetId,ass.AssetName  ";
            select.TableName = "  dbo.FuturesCode  fc " +
                                                 " inner join dbo.Exchange ec on fc.ExchageId=ec.ExchangeId" +
                                                 " inner join dbo.MeasureUnit mu on fc.MUId=mu.MUId" +
                                                 " inner join dbo.Asset ass on ass.AssetId=fc.AssetId" +
                                                 " inner join dbo.Currency c on fc.CurrencyId =c.CurrencyId" +
                                                 " inner join dbo.BDStatusDetail bd on fc.FuturesCodeStatus=bd.DetailId ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" bd.StatusId = 1 ");

            if (selFuturesCodeStatus > 0)
                sb.AppendFormat(" and FuturesCodeStatus = {0}", selFuturesCodeStatus);
            if (exchageId > 0)
                sb.AppendFormat(" and ExchageId = {0}", exchageId);
            if (firstTradeDate > NFMT.Common.DefaultValue.DefaultTime)
                sb.AppendFormat(" and FirstTradeDate >= '{0}'", firstTradeDate);
            if (lastTradeDate > NFMT.Common.DefaultValue.DefaultTime)
                sb.AppendFormat(" and LastTradeDate <= '{0}'", lastTradeDate);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 8
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int status, string key, int bankeStatus, int capitalType, string bankEname)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "BankId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " B.BankId,B.BankName,B.BankEname,B.BankFullName,B.BankShort,B.CapitalType,B.BankStatus,bd.StatusName,sd.DetailName,bt.BankName as ParentBankName,B.ParentId,case ISNULL(B.SwitchBack,0) when 0 then '否' when 1 then '是' end as SwitchBack ";
            select.TableName = "  dbo.Bank B left join dbo.BDStatusDetail bd on B.BankStatus = bd.DetailId left join dbo.BDStyleDetail sd on sd.StyleDetailId=B.CapitalType left join Bank bt on bt.BankId=B.ParentId ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" bd.StatusId = 1 ");

            if (status > 0)
                sb.AppendFormat(" and  B.BankStatus = {0}", status);
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and B.BankFullName like '%{0}%'", key);
            if (capitalType > 0)
                sb.AppendFormat(" and  B.CapitalType = {0}", capitalType);
            if (!string.IsNullOrEmpty(bankEname))
                sb.AppendFormat(" and B.BankEname like '%{0}%'", bankEname);
            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 9
0
        public SelectModel GetCashSelectModel(int pageIndex, int pageSize, string orderStr, int pledgeApplyId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "psd.ContractNo desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" psd.ContractNo as StockContractNo,psd.Deadline,SUM(Hands) as Hands,bank.BankName as AccountName ");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.Fin_PledgeApply pa ");
            sb.AppendFormat(" inner join dbo.Fin_PledgeApplyStockDetail psd on pa.PledgeApplyId = psd.PledgeApplyId and psd.DetailStatus ={0} ", (int)Common.StatusEnum.已生效);
            sb.Append(" left join NFMT_Basic..Bank bank on pa.FinancingBankId = bank.BankId ");
            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" pa.PledgeApplyId ={0} group by psd.ContractNo,psd.Deadline,bank.BankName ", pledgeApplyId);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 10
0
        public Common.SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr,int priceConfirmId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "detail.DetailId desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("detail.DetailId,detail.PriceConfirmId,detail.InterestStartDate,detail.InterestEndDate,CONVERT(varchar,detail.InterestDay) + '天' as InterestDayName,detail.InterestDay,detail.InterestUnit,CONVERT(varchar,detail.InterestUnit)+mu.MUName as InterestUnitName");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.Pri_PriceConfirmDetail detail ");
            sb.Append(" left join dbo.Pri_PriceConfirm pc on detail.PriceConfirmId = pc.PriceConfirmId ");
            sb.Append(" left join dbo.Con_Contract con on pc.ContractId = con.ContractId ");
            sb.Append(" left join NFMT_Basic..MeasureUnit mu on con.UnitId = mu.MUId ");
            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" detail.PriceConfirmId = {0} and detail.DetailStatus = {1} ", priceConfirmId, (int)Common.StatusEnum.已生效);
            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 11
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, string typeName, int status)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "SmsTypeId asc";
            else
                select.OrderStr = orderStr;

            int statusId = (int)Common.StatusTypeEnum.通用状态;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" SmsTypeId,TypeName,ListUrl,ViewUrl,SmsTypeStatus,bd.StatusName ");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.Sm_SmsType st  ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStatusDetail bd on st.SmsTypeStatus = bd.DetailId and bd.StatusId = {0} ", statusId);
            select.TableName = sb.ToString();

            sb.Clear();
            sb.Append(" 1=1 ");
            if (!string.IsNullOrEmpty(typeName))
                sb.AppendFormat(" and TypeName like '%{0}%' ", typeName);
            if (status > 0)
                sb.AppendFormat(" and SmsTypeStatus = {0} ", status);
            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 12
0
        /// <summary>
        /// 可配货库存列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <returns></returns>
        public SelectModel GetCanSalesSelect(int pageIndex, int pageSize, string orderStr, string sids = "", int contractId = 0, int StockApplyId = 0, string dids = "", string refNo = "", int ownCorpId = 0)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "sto.StockId asc";
            else
                select.OrderStr = orderStr;

            if (string.IsNullOrEmpty(dids))
                dids = "0";

            int readyStatus = (int)Common.StatusEnum.已生效;

            select.ColumnName = "sto.StockId,sn.RefNo,sto.UintId,sto.CurNetAmount,mu.MUName,sto.StockStatus,sd.StatusName,sto.CorpId,cor.CorpName,sto.AssetId,ass.AssetName,sto.BrandId,bra.BrandName,soad.ApplyAmount,ISNULL(sto.CurNetAmount,0) - ISNULL(soad.ApplyAmount,0) as LastAmount,ISNULL(sto.CurNetAmount,0) - ISNULL(soad.ApplyAmount,0) as NetAmount,sto.Bundles - isnull(soad.ApplyBundles,0) as LaveBundles,sto.Bundles - isnull(soad.ApplyBundles,0) as Bundles,dp.DPName,sto.CardNo ";

            int statusId = (int)Common.StatusTypeEnum.库存状态;
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" dbo.St_Stock sto ");
            sb.AppendFormat(" inner join NFMT.dbo.Con_Contract con on con.AssetId = sto.AssetId and con.UnitId = sto.UintId and con.ContractId = {0} ", contractId);
            sb.Append(" inner join dbo.St_StockName sn on sto.StockNameId = sn.StockNameId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStatusDetail sd on sd.DetailId = sto.StockStatus and sd.StatusId ={0} ", statusId);
            sb.Append(" left join NFMT_User.dbo.Corporation cor on cor.CorpId = sto.CorpId ");
            sb.Append(" left join NFMT_Basic.dbo.Asset ass on ass.AssetId = sto.AssetId ");
            sb.Append(" left join NFMT_Basic.dbo.Brand bra on bra.BrandId = sto.BrandId ");
            sb.Append(" left join NFMT_Basic.dbo.DeliverPlace dp on dp.DPId = sto.DeliverPlaceId ");
            sb.Append(" left join NFMT_Basic.dbo.MeasureUnit mu on mu.MUId = sto.UintId ");

            sb.AppendFormat("left join (select sum(NetAmount) as ApplyAmount,sum(Bundles) as ApplyBundles,StockId from NFMT.dbo.St_StockOutApplyDetail where DetailStatus ={0} and DetailId not in ({1}) group by StockId) as soad on soad.StockId = sto.StockId", readyStatus, dids);
            select.TableName = sb.ToString();

            sb.Clear();

            int planStockInStatus = (int)StockStatusEnum.预入库存;
            int planCustomsStatus = (int)StockStatusEnum.新拆库存;

            sb.AppendFormat(" sto.StockStatus between {0} and {1} ", planStockInStatus, planCustomsStatus);
            sb.AppendFormat(" and ISNULL(sto.CurNetAmount,0) - ISNULL(soad.ApplyAmount,0) > 0 ");
            if (StockApplyId > 0)
            {
                sb.AppendFormat(" and sto.StockId not in (select StockId from dbo.St_StockOutApplyDetail where StockOutApplyId ={0} and DetailStatus ={1} ) ", StockApplyId, readyStatus);
            }
            if (!string.IsNullOrEmpty(sids))
            {
                sids = sids.Trim();
                sb.AppendFormat(" and sto.StockId not in ({0})", sids);
            }

            if (!string.IsNullOrEmpty(refNo))
                sb.AppendFormat(" and sn.RefNo like '%{0}%'", refNo);

            if (ownCorpId > 0)
                sb.AppendFormat(" and sto.CorpId={0} ", ownCorpId);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 13
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int masterId, bool isHas = false, string clauseText = "")
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();
            int status = (int)NFMT.Common.StatusEnum.已生效;

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "cc.ClauseId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " cc.ClauseText,cc.ClauseEnText,ccr.RefId,cc.ClauseId,ccr.Sort,ccr.IsChose ";
            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            if (isHas)
            {
                select.TableName = " dbo.ClauseContract_Ref ccr left join dbo.ContractClause cc on cc.ClauseId = ccr.ClauseId left join dbo.ContractMaster cm on cm.MasterId = ccr.MasterId ";
                sb.AppendFormat(" ccr.RefStatus ={0} and cc.ClauseStatus = {0} ", status);
                sb.AppendFormat(" and ccr.MasterId = {0} ", masterId);
            }
            else
            {
                select.TableName = string.Format(" dbo.ContractClause cc left join dbo.ClauseContract_Ref ccr on ccr.ClauseId = cc.ClauseId and MasterId = {0} and RefStatus = {1}  left join dbo.ContractMaster cm on cm.MasterId = ccr.MasterId ", masterId, status);
                sb.AppendFormat(" ccr.ClauseId is null and cc.ClauseStatus = {0}", status);
                if (!string.IsNullOrEmpty(clauseText))
                    sb.AppendFormat(" and cc.ClauseText like '%{0}%' ", clauseText);
            }

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 14
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <param name="producerName"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int brandStatus, string key, int producerName)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "BrandId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = "  BrandId,b.ProducerId,pd.ProducerName as ProducerName,BrandName,BrandFullName,BrandInfo,BrandStatus,bd.StatusName as BrandStatusName ";
            select.TableName = " Brand b  inner join dbo.BDStatusDetail bd on b.BrandStatus=bd.DetailId inner join dbo.Producer pd on b.ProducerId=pd.ProducerId ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" bd.StatusId = 1 ");

            if (producerName > 0)
                sb.AppendFormat(" and  b.ProducerId= {0}", producerName);
            if (brandStatus > 0)
                sb.AppendFormat(" and BrandStatus = {0}", brandStatus);
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and BrandName like '%{0}%'", key);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 15
0
        public SelectModel GetSelectModelForHand(int pageIndex, int pageSize, string orderStr, int pledgeApplyId, int repoApplyId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "rad.StockDetailId desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" rad.DetailId,rad.StockDetailId,rad.RepoTime,rad.ContractNo,rad.StockId,rad.RefNo,pasd.NetAmount as PledgeNetAmount,pasd.Hands as PledgeHands,pasd.Memo,ISNULL(alStock.AlreadyNetAmount,0) as AlreadyNetAmount,ISNULL(alStock.AlreadyHands,0) as AlreadyHands,rad.AccountName,rad.NetAmount,ROUND(rad.NetAmount/ass.AmountPerHand,0) as Hands,rad.Price,rad.ExpiringDate");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.Fin_RepoApplyDetail rad ");
            sb.Append(" left join dbo.Fin_PledgeApplyStockDetail pasd on rad.StockDetailId = pasd.StockDetailId ");
            sb.Append(" left join dbo.Fin_PledgeApply pa on pasd.PledgeApplyId = pa.PledgeApplyId ");
            sb.Append(" left join ( ");
            sb.Append(" select StockId,SUM(ISNULL(NetAmount,0)) as AlreadyNetAmount,SUM(ISNULL(Hands,0)) as AlreadyHands ");
            sb.Append(" from dbo.Fin_RepoApplyDetail ");
            sb.AppendFormat(" where DetailStatus >= {0} and RepoApplyId <> {1} ", (int)Common.StatusEnum.已录入, repoApplyId);
            sb.Append(" group by StockId ");
            sb.Append(" ) alStock on rad.StockId = alStock.StockId ");
            sb.Append(" left join NFMT_Basic..Asset ass on ass.AssetId = pa.AssetId ");
            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" rad.RepoApplyId = {0} and rad.DetailStatus = {1} ", repoApplyId, (int)Common.StatusEnum.已生效);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 16
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int status, string key, int producerArea)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "P.ProducerId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " P.ProducerId,P.ProducerFullName,P.ProducerName,P.ProducerShort,A.AreaName,BD.StatusName,P.ProducerArea ";
            select.TableName = " dbo.Producer P left join dbo.Area A on P.ProducerArea = A.AreaId left join dbo.BDStatusDetail BD on P.ProducerStatus = BD.DetailId ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" BD.StatusId = 1 ");

            if (status > 0)
                sb.AppendFormat(" and P.ProducerStatus = {0}", status);
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and P.ProducerName like '%{0}%'", key);
            if (producerArea > 0)
                sb.AppendFormat(" and P.ProducerArea = {0}", producerArea);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 17
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int status, string key)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "mu.MUId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = "  mu.MUId,mu.MUName,mu.BaseId,bmu.MUName as BaseName,mu.TransformRate,mu.MUStatus,BD.StatusName as MUStatusName ";
            select.TableName = " dbo.MeasureUnit mu left join dbo.MeasureUnit bmu on mu.BaseId = bmu.MUId left join dbo.BDStatusDetail BD on BD.DetailId = mu.MUStatus ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("  BD.StatusId = 1  ");

            if (status > 0)
                sb.AppendFormat(" and mu.MUStatus = {0}", status);
            if (!string.IsNullOrEmpty(key))
                sb.AppendFormat(" and mu.MUName like '%{0}%'", key);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 18
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int splitDocId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "sd.DetailId asc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" sd.DetailId,sd.NewRefNo,sd.GrossAmount,sd.NetAmount,sd.UnitId,mu.MUName,sd.AssetId,ass.AssetName,sd.Bundles,sd.BrandId,br.BrandName,sd.PaperNo,sd.PaperHolder,e.Name,sd.CardNo,sd.Memo");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.St_SplitDocDetail sd ");
            //sb.Append(" left join dbo.St_StockName sn on sd.NewRefNoId = sn.StockNameId ");
            sb.Append(" left join NFMT_Basic.dbo.MeasureUnit mu on sd.UnitId = mu.MUId ");
            sb.Append(" left join NFMT_Basic.dbo.Asset ass on sd.AssetId = ass.AssetId ");
            sb.Append(" left join NFMT_Basic.dbo.Brand br on sd.BrandId = br.BrandId ");
            sb.Append(" left join NFMT_User.dbo.Employee e on sd.PaperHolder = e.EmpId ");
            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" sd.SplitDocId = {0} and sd.DetailStatus >= {1} ", splitDocId, (int)Common.StatusEnum.已生效);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 19
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="status"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, DateTime tradeDate, DateTime deliverDate, string tradeCode)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "FPId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = "   FPId,TradeDate,DeliverDate,TradeCode,SettlePrice ";
            select.TableName = " dbo.FuturesPrice ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" 1=1 ");

            //if (tradeDate!=null)
            //    sb.AppendFormat(" and TradeDate = '{0}'", tradeDate);
            //if (deliverDate!=null)
            //    sb.AppendFormat(" and DeliverDate = '{0}'", deliverDate);
            //if (!string.IsNullOrEmpty(tradeCode))
            //    sb.AppendFormat(" and TradeCode = '{0}'", tradeCode);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 20
0
        /// <summary>
        ///  
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="contractNo"></param>
        /// <param name="outCorpId"></param>
        /// <param name="applyTimeBegin"></param>
        /// <param name="applyTimeEnd"></param>
        /// <returns></returns>
        public SelectModel GetCanAllotListSelect(int pageIndex, int pageSize, string orderStr, string contractNo, int outCorpId, DateTime applyTimeBegin, DateTime applyTimeEnd)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "cs.SubId desc";
            else
                select.OrderStr = orderStr;

            int tradeDirectionStyleId = (int)NFMT.Data.StyleEnum.贸易方向;
            int readyStatus = (int)NFMT.Common.StatusEnum.已生效;
            int statusType = (int)NFMT.Common.StatusTypeEnum.通用状态;
            int priceModeStyleId = (int)NFMT.Data.StyleEnum.PriceMode;
            int tradeDirection = NFMT.Data.DetailProvider.Details(NFMT.Data.StyleEnum.贸易方向)["Sell"].StyleDetailId;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" cs.SubId,cs.ContractId,cs.ContractDate,c.ContractNo,c.OutContractNo,c.TradeDirection,tradeDirection.DetailName as TradeDirectionName,inCorp.CorpId as InCorpId , inCorp.CorpName as InCorpName,outCorp.CorpId as OutCorpId , outCorp.CorpName as OutCorpName,c.AssetId,ass.AssetName,cs.SignAmount,cs.UnitId,cast(cs.SignAmount as varchar(20))+mu.MUName as ContractWeight,cs.PriceMode,priceMode.DetailName as PriceModeName,cs.SubStatus,subStatus.StatusName");
            sb.Append(",ref.SumBala,CAST(isnull(ref.SumBala,0) as varchar) + cur.CurrencyName as AllotBala,cur.CurrencyId");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.Con_ContractSub cs ");
            sb.Append(" left join dbo.Con_SubDetail sd on cs.SubId = sd.SubId ");
            sb.Append(" left join dbo.Con_SubPrice sp on cs.SubId = sp.SubId ");
            sb.Append(" left join dbo.Con_Contract c on cs.ContractId = c.ContractId ");
            sb.AppendFormat(" left join dbo.Con_ContractCorporationDetail outCorp on c.ContractId = outCorp.ContractId and outCorp.IsInnerCorp= 0 and outCorp.IsDefaultCorp =1 and outCorp.DetailStatus={0} ", readyStatus);
            sb.AppendFormat(" left join dbo.Con_ContractCorporationDetail inCorp on c.ContractId = inCorp.ContractId and inCorp.IsInnerCorp=1 and inCorp.IsDefaultCorp=1 and inCorp.DetailStatus = {0} ", readyStatus);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail tradeDirection on c.TradeDirection = tradeDirection.StyleDetailId and tradeDirection.BDStyleId={0}  ", tradeDirectionStyleId);
            sb.Append(" left join NFMT_Basic.dbo.Asset ass on c.AssetId = ass.AssetId ");
            sb.Append(" left join NFMT_Basic.dbo.MeasureUnit mu on cs.UnitId = mu.MUId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail priceMode on cs.PriceMode = priceMode.StyleDetailId and priceMode.BDStyleId ={0} ", priceModeStyleId);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStatusDetail subStatus on cs.SubStatus = subStatus.DetailId and SubStatus.StatusId={0} ", statusType);
            sb.Append(" left join NFMT_Basic.dbo.Currency cur on cs.SettleCurrency = cur.CurrencyId");

            sb.Append(" left join (select crr.SubContractId,SUM(rad.AllotBala) as SumBala from dbo.Fun_ContractReceivable_Ref crr  ");
            sb.Append(" inner join dbo.Fun_RecAllotDetail rad on crr.DetailId = rad.DetailId and rad.DetailStatus>=20 group by crr.SubContractId) ");
            sb.Append(" as ref on ref.SubContractId = cs.SubId ");

            select.TableName = sb.ToString();

            sb.Clear();

            sb.AppendFormat(" c.TradeDirection = {0} ", tradeDirection);

            if (!string.IsNullOrEmpty(contractNo))
                sb.AppendFormat(" and cs.SubNo like '%{0}%' ", contractNo);
            if (outCorpId > 0)
                sb.AppendFormat(" and outCorp.CorpId = {0} ", outCorpId);
            if (applyTimeBegin > Common.DefaultValue.DefaultTime && applyTimeEnd > applyTimeBegin)
                sb.AppendFormat(" and cs.ContractDate between '{0}' and '{1}' ", applyTimeBegin.ToString(), applyTimeEnd.AddDays(1).ToString());

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 21
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, DateTime beginDate, DateTime endDate, int bankId, int assetId, int status, string pledgeApplyNo, string refNo)
        {
            SelectModel select = new SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            select.OrderStr = string.IsNullOrEmpty(orderStr) ? "pa.PledgeApplyId desc" : orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" pa.PledgeApplyId,pa.PledgeApplyNo,pa.ApplyTime,bank.BankName,bacc.AccountNo,ass.AssetName,case pa.SwitchBack when 0 then '否' when 1 then '是' end as SwitchBack,pa.SumNetAmount,pa.SumHands,mu.MUName,pa.PledgeApplyStatus,bd.StatusName,task.NodeName ");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.Fin_PledgeApply pa ");
            sb.Append(" left join NFMT_Basic..Bank bank on pa.FinancingBankId = bank.BankId ");
            sb.Append(" left join NFMT_Basic..BankAccount bacc on pa.FinancingAccountId = bacc.BankAccId ");
            sb.Append(" left join NFMT_Basic..Asset ass on pa.AssetId = ass.AssetId ");
            sb.Append(" left join NFMT_Basic..MeasureUnit mu on ass.MUId = mu.MUId ");
            sb.Append(" left join NFMT_Basic..Exchange ex on pa.ExchangeId = ex.ExchangeId ");
            sb.AppendFormat(" left join NFMT_Basic..BDStatusDetail bd on pa.PledgeApplyStatus = bd.DetailId and bd.StatusId = {0} ", (int)Common.StatusTypeEnum.通用状态);
            sb.Append(" left join ( ");
            sb.Append(" select a.NodeName,a.RowId from ( ");
            sb.Append(" select n.NodeName,ds.RowId,row_number() OVER (partition BY ds.RowId ORDER BY t.TaskId desc,ISNULL(tol.LogTime,dateadd(d,1,getdate())) desc,tn.NodeLevel desc) as num ");
            sb.Append(" from NFMT_WorkFlow.dbo.Wf_DataSource ds ");
            sb.Append(" left join NFMT_WorkFlow.dbo.Wf_Task t on ds.SourceId = t.DataSourceId  ");
            sb.Append(" left join NFMT_WorkFlow.dbo.Wf_TaskNode tn on t.TaskId = tn.TaskId ");
            sb.Append(" left join NFMT_WorkFlow.dbo.Wf_TaskOperateLog tol on tn.TaskNodeId = tol.TaskNodeId ");
            sb.Append(" left join NFMT_WorkFlow.dbo.Wf_Node n on tn.NodeId = n.NodeId ");
            sb.Append(" where ds.BaseName = 'Financing' ");
            sb.Append(" and ds.TableCode = 'dbo.Fin_PledgeApply' ");
            sb.AppendFormat(" and (ISNULL(tol.LogId,0)<>0 or tn.NodeStatus>{0}) ", (int)Common.StatusEnum.审核拒绝);
            sb.Append("  ) a ");
            sb.Append(" where a.num = 1 ");
            sb.Append(" ) task on pa.PledgeApplyId = task.RowId ");

            select.TableName = sb.ToString();

            sb.Clear();
            sb.Append(" 1=1 ");

            if (beginDate > NFMT.Common.DefaultValue.DefaultTime && endDate > beginDate)
                sb.AppendFormat(" and pa.ApplyTime between '{0}' and '{1}' ", beginDate.ToShortDateString(), endDate.ToShortDateString());
            if (bankId > 0)
                sb.AppendFormat(" and pa.FinancingBankId = {0} ", bankId);
            if (assetId > 0)
                sb.AppendFormat(" and pa.AssetId = {0} ", assetId);
            if (status > 0)
                sb.AppendFormat(" and pa.PledgeApplyStatus = {0} ", status);
            if (!string.IsNullOrEmpty(pledgeApplyNo))
                sb.AppendFormat(" and pa.PledgeApplyNo like '%{0}%' ", pledgeApplyNo);
            if (!string.IsNullOrEmpty(refNo))
                sb.AppendFormat(" and exists (select 1 from dbo.Fin_PledgeApplyStockDetail where RefNo like '%{0}%' and PledgeApplyId = pa.PledgeApplyId and DetailStatus >={1})", refNo, (int)NFMT.Common.StatusEnum.已生效);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 22
0
        public SelectModel GetBankPledgeReportSelect(int pageIndex, int pageSize, string orderStr, string refNo, int bankId, DateTime startDate, DateTime endDate, int repoInfo)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "bank.BankName desc";
            else
                select.OrderStr = orderStr;

            int readyStatus = (int)Common.StatusEnum.已生效;
            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            sb.Append(" bank.BankName,pasd.RefNo,pasd.NetAmount,pasd.ContractNo,pa.ApplyTime,pasd.Hands,price.Price,price.ExpiringDate,pasd.NetAmount-ISNULL(repo.NetAmount,0) nowPledgeAmount ");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" Financing.dbo.Fin_PledgeApply pa ");
            sb.Append(" left join NFMT_Basic.dbo.Bank bank on pa.FinancingBankId = bank.BankId ");
            sb.Append(" inner join Financing.dbo.Fin_PledgeApplyStockDetail pasd on pa.PledgeApplyId = pasd.PledgeApplyId and pasd.DetailStatus >=50 ");
            sb.Append(" left join ( ");
            sb.Append(" select ROW_NUMBER() OVER(PARTITION BY PledgeApplyId,StockContractNo,Deadline ORDER BY ExpiringDate desc) rowid, PledgeApplyId,Price,ExpiringDate,StockContractNo,Deadline ");
            sb.Append(" from Financing.dbo.Fin_PledgeApplyCashDetail ");
            sb.Append(" where DetailStatus >=50  ");
            sb.Append(" ) price on pasd.ContractNo = price.StockContractNo and pasd.Deadline = price.Deadline and pasd.PledgeApplyId = price.PledgeApplyId and price.rowid = 1 ");
            sb.Append(" left join ( ");
            sb.Append(" select rad.PledgeApplyId,rad.StockId,SUM(rad.NetAmount) NetAmount ");
            sb.Append(" from Financing.dbo.Fin_RepoApplyDetail rad ");
            sb.Append(" left join Financing.dbo.Fin_RepoApply ra on rad.RepoApplyId = ra.RepoApplyId ");
            sb.Append(" where rad.DetailStatus >=50 and ra.RepoApplyStatus >=50 ");
            sb.Append(" group by rad.PledgeApplyId,rad.StockId ");
            sb.Append(" ) repo on pasd.PledgeApplyId = repo.PledgeApplyId and pasd.StockId = repo.StockId ");

            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" pa.PledgeApplyStatus >= {0} ", readyStatus);
            if (bankId > 0)
                sb.AppendFormat(" and pa.FinancingBankId = {0} ", bankId);
            if (!string.IsNullOrEmpty(refNo))
                sb.AppendFormat(" and pasd.RefNo like '%{0}%' ", refNo);
            if (startDate > Common.DefaultValue.DefaultTime && endDate > startDate)
                sb.AppendFormat(" and pa.ApplyTime between '{0}' and '{1}' ", startDate.ToString(), endDate.AddDays(1).ToString());
            if (repoInfo > 0)
            {
                if (repoInfo == 1)
                    sb.AppendFormat(" and (pasd.NetAmount-ISNULL(repo.NetAmount,0)) = 0 ");
                if(repoInfo == 2)
                    sb.AppendFormat(" and (pasd.NetAmount-ISNULL(repo.NetAmount,0)) > 0 ");
            }

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 23
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, string name, int assetId, int tradeDirection, int tradeBorder, int contractInOut, int contractLimit, int corpId, int status)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "ag.AuthGroupId desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("ag.AuthGroupId,ag.AuthGroupName,");
            sb.Append("case when ISNULL(ag.AssetId,0)=0 then '全部' else a.AssetName end as AssetName,");
            sb.Append("case when ISNULL(ag.TradeDirection,0)=0 then '全部' else bd1.DetailName end as TradeDirection,");
            sb.Append("case when ISNULL(ag.TradeBorder,0)=0 then '全部' else bd2.DetailName end as TradeBorder,");
            sb.Append("case when ISNULL(ag.ContractInOut,0)=0 then '全部' else bd3.DetailName end as ContractInOut,");
            sb.Append("case when ISNULL(ag.ContractLimit,0)=0 then '全部' else bd4.DetailName end as ContractLimit,");
            sb.Append("case when ISNULL(ag.CorpId,0)=0 then '全部' else c.CorpName end as CorpName,bdd.StatusName");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.AuthGroup ag ");
            sb.Append(" left join NFMT_Basic.dbo.Asset a on ag.AssetId = a.AssetId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd1 on ag.TradeDirection = bd1.StyleDetailId and bd1.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.TradeDirection);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd2 on ag.TradeBorder = bd2.StyleDetailId and bd2.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.TradeBorder);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd3 on ag.ContractInOut = bd3.StyleDetailId and bd3.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.ContractSide);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd4 on ag.ContractLimit = bd4.StyleDetailId and bd4.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.ContractLimit);
            sb.Append(" left join dbo.Corporation c on ag.CorpId = c.CorpId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStatusDetail bdd on ag.AuthGroupStatus = bdd.DetailId and bdd.StatusId = {0} ", (int)NFMT.Common.StatusTypeEnum.通用状态);
            select.TableName = sb.ToString();

            sb.Clear();
            sb.Append(" 1=1 ");

            if (!string.IsNullOrEmpty(name))
                sb.AppendFormat(" and ag.AuthGroupName like '%{0}%' ", name);
            if (assetId > 0)
                sb.AppendFormat(" and (ag.AssetId = {0} or ag.AssetId = 0) ", assetId);
            if (tradeDirection > 0)
                sb.AppendFormat(" and (ag.TradeDirection = {0} or ag.TradeDirection = 0) ", tradeDirection);
            if (tradeBorder > 0)
                sb.AppendFormat(" and (ag.TradeBorder = {0} or ag.TradeBorder = 0) ", tradeBorder);
            if (contractInOut > 0)
                sb.AppendFormat(" and (ag.ContractInOut = {0} or ag.ContractInOut = 0) ", contractInOut);
            if (contractLimit > 0)
                sb.AppendFormat(" and (ag.ContractLimit = {0} or ag.ContractLimit = 0) ", contractLimit);
            if (corpId > 0)
                sb.AppendFormat(" and (ag.CorpId = {0} or ag.CorpId = 0) ", corpId);
            if (status > 0)
                sb.AppendFormat(" and ag.AuthGroupStatus = {0} ", status);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 24
0
        public SelectModel GetCurDetailsSelect(int pageIndex, int pageSize, string orderStr,int allotId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "ref.RefId desc";
            else
                select.OrderStr = orderStr;

            int readyStatus = (int)NFMT.Common.StatusEnum.已生效;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            //sb.Append(" ciad.DetailId,ciad.AllotId,ci.CashInId,ci.CashInDate,ci.CashInCorpId,inCorp.CorpName as InCorp ");
            //sb.Append(" ,ci.PayCorpName as OutCorp,ci.CashInBank,ban.BankName as CashInBankName,ci.CurrencyId,cur.CurrencyName ");
            //sb.Append(" ,ci.CashInBala,isnull(ref.SumBala,0) as SumBala,ci.CashInBala - ISNULL(ref.SumBala,0) as LastBala ");
            //sb.Append(" ,isnull(ref.SumBala,0) as AllotBala");
            sb.Append(" ref.RefId as DetailId,ref.AllotId,ci.CashInId,ci.CashInDate,ci.CashInCorpId,inCorp.CorpName as InCorp ");
            sb.Append(" ,ci.PayCorpName as OutCorp,ci.CashInBank,ban.BankName as CashInBankName,ci.CurrencyId,cur.CurrencyName ");
            sb.Append(" ,ci.CashInBala,ref.AllotBala as SumBala,ci.CashInBala - ISNULL(ref.AllotBala,0) as LastBala ");
            sb.Append(" ,isnull(ref.AllotBala,0) as AllotBala ");

            select.ColumnName = sb.ToString();

            sb.Clear();

            //sb.Append(" dbo.Fun_CashInAllotDetail ciad ");
            //sb.Append(" inner join dbo.Fun_CashIn ci on ciad.CashInId = ci.CashInId ");
            //sb.Append(" left join NFMT_User.dbo.Corporation inCorp on inCorp.CorpId = ci.CashInCorpId ");
            //sb.Append(" left join NFMT_Basic.dbo.Bank ban on ban.BankId = ci.CashInBank ");
            //sb.Append(" left join NFMT_Basic.dbo.Currency cur on cur.CurrencyId = ci.CurrencyId ");
            //sb.Append(" left join(select CashInId,SUM(AllotBala) as SumBala ");
            //sb.AppendFormat(" from dbo.Fun_CashInAllotDetail where AllotId = {0} and DetailStatus>={1} ", allotId, readyStatus);
            //sb.Append(" group by CashInId) as ref on ref.CashInId = ciad.CashInId ");

            sb.Append(" dbo.Fun_CashInCorp_Ref ref ");
            sb.Append(" inner join dbo.Fun_CashIn ci on ref.CashInId = ci.CashInId ");
            sb.Append(" left join NFMT_User.dbo.Corporation inCorp on inCorp.CorpId = ci.CashInCorpId ");
            sb.Append(" left join NFMT_Basic.dbo.Bank ban on ban.BankId = ci.CashInBank ");
            sb.Append(" left join NFMT_Basic.dbo.Currency cur on cur.CurrencyId = ci.CurrencyId ");

            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" ref.DetailStatus>={0} ", readyStatus);
            sb.AppendFormat(" and ref.AllotId = {0} ", allotId);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 25
0
        public SelectModel GetContractOutStockSelect(int pageIndex, int pageSize, string orderStr, string stockName, DateTime beginDate, DateTime endDate, string stockIds = "")
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "sto.StockId desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("sto.StockId,sn.RefNo,sto.AssetId,ass.AssetName,sto.BrandId,bra.BrandName,sto.Format,sto.OriginPlace,sto.CurNetAmount,sto.DeliverPlaceId");
            sb.Append(",dp.DPName,sto.StockDate,sto.UintId,mu.MUName,sto.CustomsType,ct.DetailName as CustomsTypeName,sto.CardNo,sto.StockStatus,ss.StatusName");
            sb.Append(",se.ExclusiveAmount, ISNULL(sto.CurNetAmount,0) - ISNULL(se.ExclusiveAmount,0) as NetAmount, ISNULL(sto.CurNetAmount,0) - ISNULL(se.ExclusiveAmount,0) as OutAmount,sto.Bundles,sto.Bundles as OutBundles");
            select.ColumnName = sb.ToString();

            int readyStatus = (int)Common.StatusEnum.已生效;

            sb.Clear();
            sb.Append(" dbo.St_Stock sto ");
            sb.Append("  inner join dbo.St_StockName sn on sto.StockNameId = sn.StockNameId ");

            sb.Append(" left join NFMT_Basic.dbo.Asset ass on sto.AssetId = ass.AssetId ");
            sb.Append(" left join NFMT_Basic.dbo.Brand bra on sto.BrandId = bra.BrandId ");
            sb.Append(" left join NFMT_Basic.dbo.DeliverPlace dp on sto.DeliverPlaceId = dp.DPId ");
            sb.Append(" left join NFMT_Basic.dbo.MeasureUnit mu on sto.UintId= mu.MUId ");
            sb.Append(" left join NFMT_Basic.dbo.BDStyleDetail ct on ct.StyleDetailId = sto.CustomsType ");
            sb.Append("  left join NFMT_Basic.dbo.BDStatusDetail ss on ss.DetailId = sto.StockStatus ");

            sb.AppendFormat("  left join (select Sum(ExclusiveAmount) as ExclusiveAmount,StockId from dbo.St_StockExclusive where ExclusiveStatus = {0} group by StockId ) as se on se.StockId = sto.StockId ", readyStatus);
            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" sto.StockStatus in ({0},{1},{2}) ", (int)StockStatusEnum.预入库存, (int)StockStatusEnum.在库正常, (int)StockStatusEnum.新拆库存);
            sb.Append(" and ISNULL(sto.CurNetAmount,0) - ISNULL(se.ExclusiveAmount,0) >0 ");

            if (!string.IsNullOrEmpty(stockName))
                sb.AppendFormat(" and sn.RefNo like '%{0}%' ", stockName);
            if (beginDate > Common.DefaultValue.DefaultTime && endDate > beginDate)
                sb.AppendFormat(" and sto.StockDate between '{0}' and '{1}' ", beginDate.ToString(), endDate.AddDays(1).ToString());

            if (!string.IsNullOrEmpty(stockIds))
                sb.AppendFormat(" and sto.StockId in ({0})", stockIds);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 26
0
        public SelectModel GetFundsLogReportSelect(int pageIndex, int pageSize, string orderStr, DateTime startDate, DateTime endDate,int inCorpId,int outCorpId,int logType)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "fl.FundsLogId desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            sb.Append("fl.FundsLogId,fl.LogDate,fl.InCorpId,inCorp.CorpName as InCorpName,fl.InBankId,inBank.BankName as InBankName,fl.InAccountId");
            sb.Append(",inAcc.AccountNo as InAccountNo,fl.OutCorpId,outCorp.CorpName as OutCorpName,fl.OutBankId,outBank.BankName as OutBankName");
            sb.Append(",fl.OutAccountId,outAcc.AccountNo as OutAccountNo,fl.LogType,lt.DetailName as LogTypeName,cur.CurrencyName");
            sb.Append(",fl.FundsBala,fl.IsVirtualPay,fl.PayMode,pm.DetailName as PayModeName");

            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" NFMT.dbo.Fun_FundsLog fl ");
            sb.Append(" left join NFMT_User.dbo.Corporation inCorp on inCorp.CorpId = fl.InCorpId ");
            sb.Append(" left join NFMT_Basic.dbo.Bank inBank on inBank.BankId = fl.InBankId ");
            sb.Append(" left join NFMT_Basic.dbo.BankAccount inAcc on inAcc.BankAccId = fl.InAccountId ");
            sb.Append(" left join NFMT_User.dbo.Corporation outCorp on outCorp.CorpId = fl.OutCorpId ");
            sb.Append(" left join NFMT_Basic.dbo.Bank outBank on outBank.BankId = fl.OutBankId ");
            sb.Append(" left join NFMT_Basic.dbo.BankAccount outAcc on outAcc.BankAccId = fl.OutAccountId ");
            sb.Append(" left join NFMT_Basic.dbo.BDStyleDetail lt on lt.StyleDetailId = fl.LogType ");
            sb.Append(" left join NFMT_Basic.dbo.Currency cur on fl.CurrencyId = cur.CurrencyId ");
            sb.Append(" left join NFMT_Basic.dbo.BDStyleDetail pm on pm.StyleDetailId = fl.PayMode ");
            select.TableName = sb.ToString();

            sb.Clear();
            sb.Append(" fl.LogStatus>=50 ");
            if (startDate > Common.DefaultValue.DefaultTime && endDate > startDate)
                sb.AppendFormat(" and fl.LogDate between '{0}' and '{1}' ", startDate.ToString(), endDate.ToString());
            if (inCorpId > 0)
                sb.AppendFormat(" and fl.InCorpId = {0} ", inCorpId);
            if (outCorpId > 0)
                sb.AppendFormat(" and fl.OutCorpId = {0} ", outCorpId);
            if (logType > 0)
                sb.AppendFormat(" and fl.LogType = {0} ", logType);
            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 27
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int roleId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "RM.RoleMenuID desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " RM.RoleMenuID,R.RoleName,M.MenuName,M.MenuDesc  ";
            select.TableName = " dbo.RoleMenu RM left join dbo.Role R on RM.RoleId = R.RoleId left join dbo.Menu M on RM.MenuId = M.MenuId ";

            if (roleId > 0)
                select.WhereStr = string.Format(" RM.RoleId = {0}", roleId);

            return select;
        }
Ejemplo n.º 28
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int empId)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "detail.DetailId desc";
            else
                select.OrderStr = orderStr;

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("detail.DetailId,e.Name,ag.AuthGroupName,");
            sb.Append("case when ISNULL(ag.AssetId,0)=0 then '全部' else a.AssetName end as AssetName,");
            sb.Append("case when ISNULL(ag.TradeDirection,0)=0 then '全部' else bd1.DetailName end as TradeDirection,");
            sb.Append("case when ISNULL(ag.TradeBorder,0)=0 then '全部' else bd2.DetailName end as TradeBorder,");
            sb.Append("case when ISNULL(ag.ContractInOut,0)=0 then '全部' else bd3.DetailName end as ContractInOut,");
            sb.Append("case when ISNULL(ag.ContractLimit,0)=0 then '全部' else bd4.DetailName end as ContractLimit,");
            sb.Append("case when ISNULL(ag.CorpId,0)=0 then '全部' else c.CorpName end as CorpName,bdd.StatusName");
            select.ColumnName = sb.ToString();

            sb.Clear();
            sb.Append(" dbo.AuthGroupDetail detail ");
            sb.Append(" left join NFMT_User.dbo.Employee e on detail.EmpId = e.EmpId ");
            sb.Append(" left join dbo.AuthGroup ag on detail.AuthGroupId = ag.AuthGroupId ");
            sb.Append(" left join NFMT_Basic.dbo.Asset a on ag.AssetId = a.AssetId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd1 on ag.TradeDirection = bd1.StyleDetailId and bd1.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.TradeDirection);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd2 on ag.TradeBorder = bd2.StyleDetailId and bd2.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.TradeBorder);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd3 on ag.ContractInOut = bd3.StyleDetailId and bd3.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.ContractSide);
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStyleDetail bd4 on ag.ContractLimit = bd4.StyleDetailId and bd4.BDStyleId = {0} ", (int)NFMT.Data.StyleEnum.ContractLimit);
            sb.Append(" left join dbo.Corporation c on ag.CorpId = c.CorpId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStatusDetail bdd on detail.DetailStatus = bdd.DetailId and bdd.StatusId = {0} ", (int)NFMT.Common.StatusTypeEnum.通用状态);
            select.TableName = sb.ToString();

            sb.Clear();
            sb.AppendFormat(" detail.EmpId = {0} and detail.DetailStatus = {1} ", empId, (int)Common.StatusEnum.已生效);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 29
0
        /// <summary>
        /// 获取分页查询对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="orderStr"></param>
        /// <param name="companyId"></param>
        /// <param name="key"></param>
        /// <param name="currency"></param>
        /// <returns></returns>
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int companyId, string accountNo, int bankId, int currencyId, int status)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "BA.BankAccId desc";
            else
                select.OrderStr = orderStr;

            select.ColumnName = " bk.BankAccId,bk.CurrencyId as CurrencyId,bk.BankId as BankId, bk.CompanyId as CorpId,CorpName,BankName,AccountNo,CurrencyName,BankAccDesc,bd.StatusName ";

            System.Text.StringBuilder sb = new StringBuilder();
            sb.Append(" dbo.BankAccount bk ");
            sb.AppendFormat(" left join dbo.BDStatusDetail bd on bk.BankAccStatus = bd.DetailId and bd.StatusId = {0} ", (int)Common.StatusTypeEnum.通用状态);
            sb.Append(" left join dbo.Bank b on b.BankId = bk.BankId ");
            sb.Append(" left join dbo.Currency c on c.CurrencyId = bk.CurrencyId ");
            sb.Append(" left join NFMT_User.dbo.Corporation cp on cp.CorpId = bk.CompanyId ");

            select.TableName = sb.ToString();
            sb.Clear();

            sb.Append(" 1 = 1 ");

            if (companyId > 0)
                sb.AppendFormat(" and bk.CompanyId = {0} ", companyId);
            if (!string.IsNullOrEmpty(accountNo))
                sb.AppendFormat(" and bk.AccountNo like '%{0}%' ", accountNo);
            if (currencyId > 0)
                sb.AppendFormat(" and bk.CurrencyId = {0} ", currencyId);
            if (bankId > 0)
                sb.AppendFormat(" and bk.BankId = {0} ", bankId);
            if (status > 0)
                sb.AppendFormat(" and bk.BankAccStatus = {0} ", status);

            select.WhereStr = sb.ToString();

            return select;
        }
Ejemplo n.º 30
0
        public SelectModel GetSelectModel(int pageIndex, int pageSize, string orderStr, int issueBank, int adviseBank, int status, DateTime datef, DateTime datet)
        {
            NFMT.Common.SelectModel select = new NFMT.Common.SelectModel();

            select.PageIndex = pageIndex;
            select.PageSize = pageSize;
            if (string.IsNullOrEmpty(orderStr))
                select.OrderStr = "lc.LcId asc";
            else
                select.OrderStr = orderStr;

            int statusId = (int)NFMT.Common.StatusTypeEnum.通用状态;

            select.ColumnName = " lc.LcId,b1.BankName as IssueBank,b2.BankName as AdviseBank,lc.IssueDate,CONVERT(varchar,lc.FutureDay) + '天' as FutureDay,CONVERT(varchar,lc.LcBala) + c.CurrencyName as LcBala,lc.LCStatus,bd.StatusName ";

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(" dbo.Con_Lc lc left join NFMT_Basic.dbo.Bank b1 on lc.IssueBank = b1.BankId ");
            sb.Append(" left join NFMT_Basic.dbo.Bank b2 on lc.AdviseBank = b2.BankId ");
            sb.Append(" left join NFMT_Basic.dbo.Currency c on lc.Currency = c.CurrencyId ");
            sb.AppendFormat(" left join NFMT_Basic.dbo.BDStatusDetail bd on lc.LCStatus = bd.DetailId and bd.StatusId = {0}", statusId);
            select.TableName = sb.ToString();

            sb.Clear();
            sb.Append(" 1=1 ");

            if (issueBank > 0)
                sb.AppendFormat(" and lc.IssueBank = {0}", issueBank);
            if (adviseBank > 0)
                sb.AppendFormat(" and lc.AdviseBank = {0}", adviseBank);
            if (status > 0)
                sb.AppendFormat(" and lc.LCStatus = {0}", status);
            if (datef > Common.DefaultValue.DefaultTime && datet >= datef)
                sb.AppendFormat(" and lc.IssueDate >= '{0}' and lc.IssueDate < '{1}' ", datef.ToString(), datet.AddDays(1).ToString());

            select.WhereStr = sb.ToString();

            return select;
        }