/// <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;
}
/// <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;
}
/// <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;
}
/// <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;
}
/// <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;
}
/// <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;
}
/// <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;
}
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;
}
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;
}
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;
}
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;
}
/// <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;
}
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;
}
/// <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;
}
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;
}
/// <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;
}
/// <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;
}
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;
}
/// <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;
}
/// <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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
/// <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;
}
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;
}
/// <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;
}
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;
}