/// <summary>
/// 列表
/// </summary>
/// yangj 16.07.01
/// <param name="dictionaryTypeId">字典类型ID</param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum, bdt.BDT_ID,bdt.Name, temp.Name AS ParentName
FROM BANK_DictionaryType AS bdt
RIGHT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY BDT_ID DESC) AS rownum,BDT_ID FROM BANK_DictionaryType
)AS tmp ON bdt.BDT_ID = tmp.BDT_ID
LEFT JOIN BANK_DictionaryType
AS temp ON bdt.ParentType = temp.BDT_ID
WHERE tmp.rownum > @Begin
" );
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(
@"SELECT COUNT(*) FROM BANK_DictionaryType
" );
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
/// <summary>
/// 查找指定的借贷信息
/// </summary>
/// zouql 16.08.30
/// <param name="financeId">融资标识</param>
/// <returns>借贷信息</returns>
public BorrowInfo Find(int financeId)
{
SqlCommand comm = new SqlCommand(@"
SELECT
BI_ID,
FinanceId,
ApprovalPrincipal,
InterestRate,
FinancingPeriods,
RepaymentInterval,
RepaymentMethod,
RepaymentDate,
FinanceAddDate,
[State],
OncePayMonths,
FinalRatio,
CustomerBailRatio,
FinalCost,
ExtralCost
FROM
FANC_Borrow
WHERE FinanceId=@financeId
");
DHelper.AddParameter(comm, "@financeId", SqlDbType.Int, financeId);
var dt = DHelper.ExecuteDataTable(comm);
return(Load(dt.Rows[0]));
}
/// <summary>
/// 查找所有借贷信息
/// </summary>
/// zouql 16.08.30
/// <returns>借贷信息List</returns>
public List <BorrowInfo> FindAll()
{
SqlCommand comm = new SqlCommand(@"
SELECT
BI_ID,
FinanceId,
ApprovalPrincipal,
InterestRate,
FinancingPeriods,
RepaymentInterval,
RepaymentMethod,
RepaymentDate,
FinanceAddDate,
[State],
OncePayMonths,
FinalRatio,
CustomerBailRatio,
FinalCost,
ExtralCost
FROM FANC_Borrow
WHERE FinanceId=@financeId
");
var dt = DHelper.ExecuteDataTable(comm);
return(LoadAll(dt.Rows));
}
/// <summary>
/// 获取授信信息列表
/// </summary>
/// yaoy 16.03.30
/// <param name="data"></param>
/// <param name="page"></param>
/// <returns></returns>
public DataTable List(Pagination page, NameValueCollection data)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT temp.rownum,ui.*,cci.Name,cci.CreditId,dbo.Dic(2, ui.Status) AS StatusDesc,rol.Name AS RoleName FROM USER_UserInfo AS ui
RIGHT JOIN (
SELECT TOP(@End) ROW_NUMBER() OVER(ORDER BY UserId DESC) AS rownum, UserId, CreditId FROM CRET_Account AS ca
WHERE (@CreditId IS NULL OR ca.CreditId = @CreditId)
) AS temp ON temp.UserId = ui.UI_ID
LEFT JOIN CRET_CreditInfo AS cci ON cci.CreditId = temp.CreditId
LEFT JOIN USER_Relation AS ur ON ur.UserId = ui.UI_ID
LEFT JOIN USER_Role AS rol ON rol.UR_ID = ur.RoleId
WHERE temp.rownum> @Begin ORDER BY cci.CreditId DESC
");
DHelper.AddInParameter(comm, "@CreditId", SqlDbType.Int, data["CreditId"]);
DHelper.AddInParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddInParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(@"
SELECT COUNT(UserId) FROM CRET_Account
WHERE (@CreditId IS NULL OR CreditId = @CreditId)
");
DHelper.AddInParameter(commPage, "@CreditId", SqlDbType.Int, data["CreditId"]);
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 获取元代码名称集合
/// </summary>
/// zouql 16.07.07
/// <returns>集合</returns>
public List <ComboInfo> GetComListMateName()
{
var sql =
@"
SELECT MetaCode,Name FROM BANK_Meta
";
SqlCommand cmd = DHelper.GetSqlCommand(sql);
var table = DHelper.ExecuteDataTable(cmd);
var list = new List <ComboInfo>();
for (var i = 0; i < table.Rows.Count; i++)
{
var p1 = table.Rows[i]["MetaCode"];
var p2 = table.Rows[i]["Name"];
if (p1 == null)
{
p1 = string.Empty;
}
if (p2 == null)
{
p2 = string.Empty;
}
list.Add(new ComboInfo(p1.ToString(), p2.ToString()));
}
return(list);
}
/// <summary>
/// 根据父节点查找字典
/// </summary>
/// <param name="parentCode"></param>
/// <param name="dictionaryTypeID"></param>
/// yand 16.07.18
/// <returns></returns>
public List <ComboInfo> GetComListByPanrentCode(int parentCode, int dictionaryTypeID)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT Code,Name FROM BANK_DictionaryCode
WHERE ParentCode = @ParentCode AND BDT_ID = @dictionaryTypeID
");
DHelper.AddInParameter(comm, "@dictionaryTypeID", SqlDbType.Int, dictionaryTypeID);
DHelper.AddInParameter(comm, "@ParentCode", SqlDbType.NChar, parentCode);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
var cbi = new ComboInfo(string.Empty, string.Empty);
object p1 = dr["Code"];
object p2 = dr["Name"];
if (p1 != null)
{
cbi.value = p1.ToString();
}
if (p2 != null)
{
cbi.text = p2.ToString();
}
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 模糊查询元代码名称集合()
/// </summary>
/// <param name="value">查询值</param>
/// <param name="type">类型</param>
/// <returns>集合</returns>
public List <ComboInfo> GetComListMateName(string value, string type)
{
var sql =
@"
SELECT MetaCode,Name FROM BANK_Meta WHERE [Type]=@t AND Name LIKE @value
";
SqlCommand cmd = DHelper.GetSqlCommand(sql);
DHelper.AddInParameter(cmd, "@value", SqlDbType.NVarChar, "%" + value + "%");
DHelper.AddInParameter(cmd, "@t", SqlDbType.TinyInt, byte.Parse(type));
var table = DHelper.ExecuteDataTable(cmd);
var list = new List <ComboInfo>();
for (var i = 0; i < table.Rows.Count; i++)
{
var p1 = table.Rows[i]["MetaCode"];
var p2 = table.Rows[i]["Name"];
if (p1 == null)
{
p1 = string.Empty;
}
if (p2 == null)
{
p2 = string.Empty;
}
list.Add(new ComboInfo(p1.ToString(), p2.ToString()));
}
return(list);
}
/// <summary>
/// 分页查询
/// </summary>
/// qiy 16.03.29
/// <param name="page">分页信息</param>
/// <param name="filter">筛选条件</param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum, ci.CreditId,
ci.Name, ci.Type, dbo.Dic(4, ci.Type) AS TypeDesc, ci.LineOfCredit, ci.AddDate, ci.Remarks,
cpi.Bail, cpi.ControllerName, cpi.ControllerTelephone
FROM CRET_CreditInfo AS ci
RIGHT JOIN (
SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY CreditId DESC) AS rownum, CreditId FROM CRET_CreditInfo
) AS tmp ON ci.CreditId = tmp.CreditId
LEFT JOIN CRET_PartnerInfo AS cpi ON ci.CreditId = cpi.CreditId
WHERE tmp.rownum > @Begin
" );
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(@"
SELECT COUNT(*) FROM CRET_CreditInfo
" );
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 产品筛选(产品名)
/// </summary>
/// yangj 16.08.02
/// <returns></returns>
public List <ComboInfo> FindByProduceName(int creditId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT DISTINCT Name
FROM PROD_ProduceInfo AS A
LEFT JOIN CRET_BindProduce AS B
ON A.ProduceId = B.ProduceId
WHERE B.CreditId = @CreditId;");
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, creditId);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["Name"].ToString(), dr["Name"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 产品筛选
/// </summary>
/// yangj 16.08.02
/// <param name="produceName">产品名</param>
/// <param name="repaymentMethod">还款方式</param>
/// <param name="financingPeriods">融资期限</param>
/// <returns></returns>
public List <ComboInfo> FindProduct(string produceName, string repaymentMethod, string financingPeriods, int creditId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT A.ProduceId,
Code
FROM PROD_ProduceInfo AS A
LEFT JOIN CRET_BindProduce AS B
ON A.ProduceId = B.ProduceId
WHERE B.CreditId = @CreditId
AND (@Name IS NULL OR Name=@Name)
AND (@RepaymentMethod IS NULL OR RepaymentMethod=@RepaymentMethod)
AND (@FinancingPeriods IS NULL OR FinancingPeriods=@FinancingPeriods)");
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, creditId);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, produceName);
DHelper.AddParameter(comm, "@RepaymentMethod", SqlDbType.Int, repaymentMethod);
DHelper.AddParameter(comm, "@FinancingPeriods", SqlDbType.Int, financingPeriods);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["ProduceId"].ToString(), dr["Code"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 提取父节点（无重复）
/// </summary>
/// zouql 16.07.06
/// <returns></returns>
public List <ComboInfo> GetComList()
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT DISTINCT Code,Name
FROM BANK_DictionaryCode
");
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
var cbi = new ComboInfo(string.Empty, string.Empty);
object p1 = dr["Code"];
object p2 = dr["Name"];
if (p1 != null)
{
cbi.value = p1.ToString();
}
if (p2 != null)
{
cbi.text = p2.ToString();
}
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 产品列表查询方法
/// </summary>
/// cais 16.03.28
/// <param name="pagination">分页</param>
/// <param name="filter">参数</param>
/// <returns></returns>
public DataTable Find(Models.Pagination pagination, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT
Produce.ProduceId,Code,Name,(InterestRate * 12) as InterestRate,(Rate * 12) as Rate,MinFinancingRatio,MaxFinancingRatio,FinalRatio,FinancingPeriods,RepaymentInterval,CustomerBailRatio,CustomerPoundage,AddDate,Remarks,AdditionalGPSCost,AdditionalOtherCost, dbo.Dic(3,RepaymentMethod) AS paymothed FROM PROD_ProduceInfo
AS Produce LEFT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY ProduceId DESC) AS rownum,ProduceId FROM PROD_ProduceInfo
WHERE @Code_Name IS NULL OR (Code like '%'+@Code_Name+'%' OR Name like '%'+@Code_Name+'%')) AS TMP
ON Produce.ProduceId=TMP.ProduceId WHERE TMP.rownum>@Begin
");
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, pagination.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, pagination.End);
DHelper.AddParameter(comm, "@Code_Name", SqlDbType.VarChar, filter["ProductCodeOrName"]);
SqlCommand commPage = DHelper.GetSqlCommand(@"SELECT Count(*) FROM PROD_ProduceInfo WHERE @Code_Name IS NULL OR (Code like '%'+@Code_Name+'%' OR Name like '%'+@Code_Name+'%')");
DHelper.AddParameter(commPage, "@Code_Name", SqlDbType.NVarChar, filter["ProductCodeOrName"]);
pagination.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
/// <summary>
/// 查询列表
/// </summary>
/// yaoy 15.11.30
/// qiy 15.12.04
/// <returns></returns>
public DataTable List()
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT DT_ID, Field, Name, IsCommon, Seed FROM SYS_DicType
");
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 获取行业门类
/// </summary>
/// yand 16.05.31
/// <returns></returns>
public DataTable GetIndustry()
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT MainID,MainCode,MainName FROM BANK_Industry
");
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 获取行政区划第一级
/// </summary>
/// yand 16.05.25
/// <returns></returns>
public DataTable GetProvice()
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT Code,ProvincesOrCity FROM BANK_Administration WHERE Code like '%0000'
");
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 根据行业ID查询行业标识
/// </summary>
/// yand 16.07.06
/// <returns></returns>
public DataTable GetIndustryByID(int IndustryID)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT MainID,MainCode,MainName FROM BANK_Industry WHERE MainID = @IndustryID
");
DHelper.AddInParameter(comm, "@IndustryId", SqlDbType.Int, IndustryID);
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 通过FinanceId 查询 产品列表查询方法
/// </summary>
/// cais 16.03.31
/// <param name="financeId"></param>
/// <returns></returns>
public List <ApplicantInfo> Find(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(
@"SELECT * FROM FANC_ApplicantInfo WHERE FinanceId = @FinanceId ORDER BY Type");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 获取当前的该类型的信息记录
/// </summary>
/// yand 16.10.14
/// <param name="partnerName"></param>
/// <returns></returns>
public List <ReportFilesInfo> FindFileByPartnerName(string partnerName)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_ReportFiles WHERE ReportTextName LIKE @PartnerName +'%' ORDER BY ReportTextName DESC
");
DHelper.AddInParameter(comm, "@PartnerName", SqlDbType.NVarChar, partnerName);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 集合
/// </summary>
/// yaoy 15.11.30
/// qiy 15.12.03
/// <returns></returns>
public DataTable List()
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT Field, Type, sdt.Name AS TypeName, Code, sdc.Name, Remarks FROM SYS_DicCommon AS sdc
LEFT JOIN SYS_DicType AS sdt ON sdt.DT_ID = sdc.Type
");
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 根据节点ID查询行为信息
/// </summary>
/// yand 15.12.03
/// qiy 16.03.08
/// <param name="nodeId">节点ID</param>
/// <returns></returns>
public List <ActionInfo> FindByNode(int nodeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT ActionId, NodeId, Transfer, Name, Type, AllocationType, Description, Method FROM FLOW_Action WHERE NodeId = @NodeId
");
DHelper.AddParameter(comm, "@NodeId", SqlDbType.Int, nodeId);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 根据信息记录ID获取段列表
/// </summary>
/// yand 16.05.25
/// <param name="InfoTypeId">信息记录ID</param>
/// <returns></returns>
public List <DataSegmentInfo> FindByInfoTypeId(int InfoTypeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_DataSegment WHERE BIT_ID = @InfoTypeId
");
DHelper.AddInParameter(comm, "@InfoTypeId", SqlDbType.Int, InfoTypeId);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 集合
/// </summary>
/// yaoy 16.07.04
/// <param name="dataSegmentId">段规则ID</param>
/// <returns></returns>
public List <SegmentRulesInfo> List(int dataSegmentId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_SegmentRules WHERE BDS_ID = @DataSegmentId ORDER BY BDS_ID ,Position
");
DHelper.AddInParameter(comm, "@DataSegmentId", SqlDbType.Int, dataSegmentId);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
///
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public FlowForm Find(int Id)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM FLOW_Form WHERE FormId = @FormId
");
DHelper.AddInParameter(comm, "@FormId", SqlDbType.Int, Id);
return(Load(DHelper.ExecuteDataTable(comm)));
}
/// <summary>
/// 查找
/// zouql 16.09.20
/// </summary>
/// <param name="ruleTypeId">标识</param>
/// <returns>实体</returns>
public RuleTypeInfo Find(int ruleTypeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_RuleType WHERE RuleTypeId=@RuleTypeId
");
DHelper.AddInParameter(comm, "@RuleTypeId", SqlDbType.Int, ruleTypeId);
return(Load(DHelper.ExecuteDataTable(comm)));
}
/// <summary>
/// 根据元数据ID获取数据段集合
/// </summary>
/// yaoy 16.09.20
/// <param name="metaCode"></param>
/// <returns></returns>
public List <SegmentRulesInfo> FindSegmentRulesByMetaCode(int metaCode)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_SegmentRules WHERE MetaCode = @MetaCode
");
DHelper.AddInParameter(comm, "@MetaCode", SqlDbType.Int, metaCode);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 根据元素ID查找html元素实体
/// </summary>
/// yand 16.07.04
/// <param name="metaCode">数据元ID</param>
/// <returns></returns>
public List <HtmlElementInfo> Find(int metaCode)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT he.* FROM BANK_HtmlElement AS he LEFT JOIN BANK_MetaComponents AS mc ON he.BHE_ID = mc.BHE_ID WHERE MetaCode = @metaCode AND BHE_Type =1
");
DHelper.AddInParameter(comm, "@metaCode", SqlDbType.Int, metaCode);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 查询文件信息
/// </summary>
/// yaoy 16.05.25
/// <param name="reportFilesId"></param>
/// <returns></returns>
public InformationRecordInfo Find(int recordId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_InformationRecord WHERE RecordID = @RecordID
");
DHelper.AddInParameter(comm, "@RecordID", SqlDbType.Int, recordId);
return(Load(DHelper.ExecuteDataTable(comm)));
}
/// <summary>
/// 获取申请人姓名和ID
/// zouql 16.07.27
/// yangj 16.09.14(还款用户直接默认为主要申请人名字，不用下拉菜单)
/// </summary>
/// <returns></returns>
public ApplicantInfo Option(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"SELECT ApplicantId,Name FROM FANC_ApplicantInfo WHERE Type = 1 AND FinanceId = @FinanceId");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt.Rows.Count > 0 ? Load(dt.Rows[0]) : null);
}
/// <summary>
/// 根据行为ID查找对应的内容
/// </summary>
/// yand 16.09.10
/// <param name="actionId"></param>
/// <returns></returns>
public ActionNoticeInfo FindByActionId(int actionId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM Notice_ActionNotice WHERE ActionId=@ActionId
");
DHelper.AddInParameter(comm, "@ActionId", SqlDbType.Int, actionId);
return(Load(DHelper.ExecuteDataTable(comm)));
}
/// <summary>
/// 列表
/// </summary>
/// yaoy 16.05.26
/// <returns></returns>
public List <MessageFileTypeInfo> List()
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_MessageFileType
");
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt.Rows.Count > 0 ? LoadAll(dt.Rows) : null);
}