/// <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>
/// 根据ID更新审核信息
/// </summary>
/// yangj 16.08.30
/// <param name="value">审核实体</param>
/// <returns></returns>
public int Update(ReviewInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE FANC_ReviewInfo SET
RepaymentDate = @RepaymentDate,
FinanceCost = @FinanceCost,
FinalCost = @FinalCost,
Payment = @Payment,
AdvicefinanceMoney = @AdvicefinanceMoney,
ApprovalPrincipal = @ApprovalPrincipal,
ApprovalFinanceRatio = @ApprovalFinanceRatio,
ReviewType = @ReviewType
WHERE FinanceId = @FinanceId");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, value.FinanceId);
DHelper.AddParameter(comm, "@AdvicefinanceMoney", SqlDbType.Decimal, value.AdvicefinanceMoney);
DHelper.AddParameter(comm, "@ApprovalPrincipal", SqlDbType.Decimal, value.ApprovalPrincipal);
DHelper.AddParameter(comm, "@ApprovalFinanceRatio", SqlDbType.Float, value.ApprovalFinanceRatio);
DHelper.AddParameter(comm, "@FinanceCost", SqlDbType.Decimal, value.FinanceCost);
DHelper.AddParameter(comm, "@FinalCost", SqlDbType.Decimal, value.FinalCost);
DHelper.AddParameter(comm, "@Payment", SqlDbType.Decimal, value.Payment);
DHelper.AddParameter(comm, "@RepaymentDate", SqlDbType.Int, value.RepaymentDate);
DHelper.AddParameter(comm, "@ReviewType", SqlDbType.TinyInt, value.ReviewType);
return(DHelper.ExecuteNonQuery(comm));
}
/// <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.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>
/// 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>
/// <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>
/// 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>
/// 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>
/// <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>
/// 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>
/// 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>
/// qiy 16.03.31
/// qiy 16.05.31
/// yangj 16.07.26 新增融资预估金额字段
/// zouql 16.07.28 新增厂商指导价字段
/// zouql 16.08.04 新增融资实际金额字段
/// <param name="value">值</param>
/// <returns></returns>
public int Update(FinanceInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE FANC_FinanceInfo SET
ProduceId = @ProduceId,
Type = @Type,
IntentionPrincipal = @IntentionPrincipal,
ApprovalValue = @ApprovalValue,
Remarks = @Remarks ,
OncePayMonths=@OncePayMonths,
MarginMoney=@MarginMoney,
PaymonthlyMoney=@PaymonthlyMoney,
OnepayInterestMoney=@OnepayInterestMoney,
ActualcashMoney=@ActualcashMoney
WHERE FinanceId = @FinanceId
" );
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, value.FinanceId);
DHelper.AddParameter(comm, "@ProduceId", SqlDbType.Int, value.ProduceId);
DHelper.AddParameter(comm, "@Type", SqlDbType.TinyInt, value.Type);
// 建议融资金额
DHelper.AddParameter(comm, "@IntentionPrincipal", SqlDbType.Decimal, value.IntentionPrincipal);
DHelper.AddParameter(comm, "@ApprovalValue", SqlDbType.Decimal, value.ApprovalValue);
DHelper.AddParameter(comm, "@Remarks", SqlDbType.NVarChar, value.Remarks);
DHelper.AddParameter(comm, "@OncePayMonths", SqlDbType.Int, value.OncePayMonths);
// 保证金、先付月供金额、一次性付息金额、实际用款金额
DHelper.AddParameter(comm, "@MarginMoney", SqlDbType.Decimal, value.MarginMoney);
DHelper.AddParameter(comm, "@PaymonthlyMoney", SqlDbType.Decimal, value.PaymonthlyMoney);
DHelper.AddParameter(comm, "@OnepayInterestMoney", SqlDbType.Decimal, value.OnepayInterestMoney);
DHelper.AddParameter(comm, "@ActualcashMoney", SqlDbType.Decimal, value.ActualcashMoney);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 更新[未使用]
/// </summary>
/// qiy 16.03.08
/// <param name="value"></param>
/// <returns></returns>
public bool Update(ActionInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE FLOW_Action SET
Node = @NodeId,
Transfer = @Transfer,
Name = @Name,
Type = @Type,
AllocationType = @AllocationType,
Description = @Description,
Method = @Method
WHERE ActionId = @ActionId
");
DHelper.AddParameter(comm, "@ActionId", SqlDbType.Int, value.ActionId);
DHelper.AddParameter(comm, "@NodeId", SqlDbType.Int, value.NodeId);
DHelper.AddParameter(comm, "@Transfer", SqlDbType.Int, value.Transfer);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, value.Name);
DHelper.AddParameter(comm, "@Type", SqlDbType.TinyInt, value.AllocationType);
DHelper.AddParameter(comm, "@Description", SqlDbType.NVarChar, value.Description);
DHelper.AddParameter(comm, "@Method", SqlDbType.VarChar, value.Method);
return(DHelper.ExecuteNonQuery(comm) > 0);
}
/// <summary>
/// 更新
/// </summary>
/// qiy 16.03.29
/// <param name="value">值</param>
/// <returns></returns>
public int Update(PartnerInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE CRET_PartnerInfo SET
Bail = @Bail,
Address = @Address,
ProxyArea = @ProxyArea,
VehicleManage = @VehicleManage,
ControllerName = @ControllerName,
ControllerIdentity = @ControllerIdentity,
ControllerTelephone = @ControllerTelephone,
Province=@Province,
City=@City
WHERE CreditId = @CreditId
" );
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, value.CreditId);
DHelper.AddParameter(comm, "@Bail", SqlDbType.Decimal, value.Bail);
DHelper.AddParameter(comm, "@Address", SqlDbType.NVarChar, value.Address);
DHelper.AddParameter(comm, "@ProxyArea", SqlDbType.NVarChar, value.ProxyArea);
DHelper.AddParameter(comm, "@VehicleManage", SqlDbType.NVarChar, value.VehicleManage);
DHelper.AddParameter(comm, "@ControllerName", SqlDbType.NVarChar, value.ControllerName);
DHelper.AddParameter(comm, "@ControllerIdentity", SqlDbType.NVarChar, value.ControllerIdentity);
DHelper.AddParameter(comm, "@ControllerTelephone", SqlDbType.NVarChar, value.ControllerTelephone);
DHelper.AddParameter(comm, "@Province", SqlDbType.NVarChar, value.Province);
DHelper.AddParameter(comm, "@City", SqlDbType.NVarChar, value.City);
return(DHelper.ExecuteNonQuery(comm));
}
/// <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.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>
/// 根据ApplicantId 删除相对应的附加申请人（担保人、联系人）
/// </summary>
/// cais 16.03.31
/// <param name="applicantId"></param>
/// <returns></returns>
public int Delete(int applicantId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"DELETE FANC_ApplicantInfo WHERE ApplicantId = @ApplicantId");
DHelper.AddParameter(comm, "@ApplicantId", SqlDbType.Int, applicantId);
return(DHelper.ExecuteNonQuery(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>
/// 根据一个产品ID ，修改对应的产品
/// </summary>
/// cais 16.03.29
/// yangj 16.07.25(新增融资返范围)
/// <param name="produce"></param>
/// <returns></returns>
public int Update(ProduceInfo produce)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE PROD_ProduceInfo SET Code=@Code ,
Name=@Name,
InterestRate=@InterestRate,
Rate=@Rate,
RepaymentMethod=@RepaymentMethod,
MinFinancingRatio=@MinFinancingRatio,
MaxFinancingRatio=@MaxFinancingRatio,
FinalRatio=@FinalRatio,
FinancingPeriods=@FinancingPeriods,
RepaymentInterval=@RepaymentInterval,
CustomerBailRatio=@CustomerBailRatio,
CustomerPoundage=@CustomerPoundage,
AddDate=@AddDate,
Remarks=@Remarks,
AdditionalGPSCost=@AdditionalGPSCost,
AdditionalOtherCost=@AdditionalOtherCost,
IsVehiclePrice=@IsVehiclePrice,
IsPurchaseTax=@IsPurchaseTax,
IsBusinessInsurance=@IsBusinessInsurance,
IsTafficCompulsoryInsurance=@IsTafficCompulsoryInsurance,
IsVehicleVesselTax=@IsVehicleVesselTax,
IsExtendedWarrantyInsurance=@IsExtendedWarrantyInsurance,
IsOther=@IsOther
WHERE ProduceId=@ProduceId");
DHelper.AddParameter(comm, "@Code", SqlDbType.NVarChar, produce.Code);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, produce.Name);
DHelper.AddParameter(comm, "@InterestRate", SqlDbType.Float, (produce.InterestRate) / 12);
DHelper.AddParameter(comm, "@Rate", SqlDbType.Float, (produce.Rate) / 12);
DHelper.AddParameter(comm, "@RepaymentMethod", SqlDbType.Int, produce.RepaymentMethod);
DHelper.AddParameter(comm, "@MinFinancingRatio", SqlDbType.Int, produce.MinFinancingRatio);
DHelper.AddParameter(comm, "@MaxFinancingRatio", SqlDbType.Int, produce.MaxFinancingRatio);
DHelper.AddParameter(comm, "@FinalRatio", SqlDbType.Int, produce.FinalRatio);
DHelper.AddParameter(comm, "@FinancingPeriods", SqlDbType.Int, produce.FinancingPeriods);
DHelper.AddParameter(comm, "@RepaymentInterval", SqlDbType.Int, produce.RepaymentInterval);
DHelper.AddParameter(comm, "@CustomerBailRatio", SqlDbType.Float, produce.CustomerBailRatio);
DHelper.AddParameter(comm, "@CustomerPoundage", SqlDbType.Decimal, produce.CustomerPoundage);
DHelper.AddParameter(comm, "@AddDate", SqlDbType.DateTime, DateTime.Now);
DHelper.AddParameter(comm, "@Remarks", SqlDbType.NVarChar, produce.Remarks);
DHelper.AddParameter(comm, "@AdditionalGPSCost", SqlDbType.Decimal, produce.AdditionalGPSCost);
DHelper.AddParameter(comm, "@AdditionalOtherCost", SqlDbType.Decimal, produce.AdditionalOtherCost);
// 新增融资范围
DHelper.AddParameter(comm, "@IsVehiclePrice", SqlDbType.Bit, produce.IsVehiclePrice);
DHelper.AddParameter(comm, "@IsPurchaseTax", SqlDbType.Bit, produce.IsPurchaseTax);
DHelper.AddParameter(comm, "@IsBusinessInsurance", SqlDbType.Bit, produce.IsBusinessInsurance);
DHelper.AddParameter(comm, "@IsTafficCompulsoryInsurance", SqlDbType.Bit, produce.IsTafficCompulsoryInsurance);
DHelper.AddParameter(comm, "@IsVehicleVesselTax", SqlDbType.Bit, produce.IsVehicleVesselTax);
DHelper.AddParameter(comm, "@IsExtendedWarrantyInsurance", SqlDbType.Bit, produce.IsExtendedWarrantyInsurance);
DHelper.AddParameter(comm, "@IsOther", SqlDbType.Bit, produce.IsOther);
DHelper.AddParameter(comm, "@ProduceId", SqlDbType.Int, produce.ProduceId);
return(DHelper.ExecuteNonQuery(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>
/// 删除根据reportId
/// </summary>
/// yand 16.10.14
/// <param name="reportId"></param>
/// <returns></returns>
public int DeleteByReportId(int reportId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
DELETE Bank_TempRecord WHERE ReportID = @ReportID
");
DHelper.AddInParameter(comm, "@ReportID", SqlDbType.Int, reportId);
return(Convert.ToInt32(DHelper.ExecuteNonQuery(comm)));
}
/// <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>
/// 获取当前的该类型的信息记录
/// </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 16.05.25
/// <param name="reportFilesId"></param>
/// <returns></returns>
public int Delete(int reportFilesId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
DELETE FROM BANK_ReportFiles WHERE FileID = @FileID
");
DHelper.AddInParameter(comm, "@FileID", SqlDbType.Int, reportFilesId);
return(Convert.ToInt32(DHelper.ExecuteNonQuery(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>
/// 根据信息记录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>
/// cais 16.04.08
/// <param name="referenceId">文件引用id</param>
public void Delete(int referenceId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE SYS_FileList WHERE ReferenceId=@ReferenceId"
);
DHelper.AddParameter(comm, "@ReferenceId", SqlDbType.Int, referenceId);
DHelper.ExecuteNonQuery(comm);
}
/// <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>
/// 根据元数据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));
}