/// <summary>
/// 导出
/// </summary>
/// <param name="startYM"></param>
/// <param name="endYM"></param>
/// <param name="state"></param>
/// <returns></returns>
public JsonResult Export(PagerInfo pagerInfo, string JXSBM)
{
string sql = @"select DZJEBH
,a.jxs 经销商
,a.jxsbm 到账金额编号
,b.DZJE 到账金额
, to_char(CJJEDZSJ,'yyyy-mm-dd HH24:mi') 创建到账金额时间
,to_char(JEDZSJ,'yyyy-mm-dd') 金额到账时间
,CWDZBZ 到账金额备注
from I_Repayment a
join I_DZJEList b on a.objectid = b.parentobjectid
where a.JXSBM = '{0}' {1}";
#region 排序
string OrderBy = " ORDER BY CJJEDZSJ desc ";
if (pagerInfo.iSortCol_0 != 0)
{
OrderBy = " ORDER BY ";
if (pagerInfo.iSortCol_0 == 3)
{
OrderBy += " CJJEDZSJ " + pagerInfo.sSortDir_0.ToUpper();
}
}
#endregion
string UserCode = this.UserValidator.User.Appellation;
sql = string.Format(sql, JXSBM, OrderBy);
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, "到账金额明细表" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}
public JsonResult ExportCJH()
{
string UserCode = this.UserValidator.User.Appellation;
string sql = @"select V.UNIT_NO 车辆编号,V.ASSET_DESC 车型,V.VIN_NO 临时车架号,'' 永久车架号 from IN_WFS.V_LOAN_STOCK_LIST V
where V.ORDER_NO = 'Y' and V.VIN_UPDATE = 'N' and dealer_code = '" + UserCode + "' and PAID_AMT>0 ";
DataTable dt = ExecuteDataTableSql(dataCode, sql);
CurrencyClass dd = new CurrencyClass();
dd.ExportExcel(dt, "需更新车架号" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 导出授信额度报告
/// </summary>
public void ExportSXED()
{
DistributorController dc = new DistributorController();
string sheetName = string.Empty;
string sql = string.Format(@"select v.Dealer_Code 经销商编码 ,v.dealer_name 经销商名称 ,v.Make 制造商,to_char(d.expiry_date,'yyyy-mm-dd') 授信到期日,v.availed_limit 贷款余额, v.balance_limit 可用授信额度
from IN_WFS.v_dealer_make_availed_limit2 v join IN_WFS.V_DEALER_EXPIRY_DATE d on v.Dealer_Code=d.Dealer_Code ");
DataTable dt = dc.ExecuteDataTableSql("Wholesale", sql);
string date = DateTime.Now.ToString("yyyyMMdd");
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, sheetName + "信审部-授信额度报告导出_" + date);
}
/// <summary>
/// 贷款车辆-报告导出
/// </summary>
/// <returns></returns>
public void ExportLoan(string dkbh, string jxs, string jxscode)
{
string sheetName = string.Empty;
// string jxsid = GetUserCode(jxs).Trim();
string sql = string.Format(@" select to_char(rownum) 序号,DKBH 贷款编号,CX 车型,CJHXZ 车架号性质,CJH 车架号,YS 颜色 ,DKJE 贷款金额 from I_CLXX where DKBH= '{0}' and jxs = '{1}'", dkbh, jxs);
// union all
// select '合计' 合计 ,N' ' 车辆编号,N' ' 车型 ,N' ' 车架号性质,N' ' 车架号,N' ' 颜色, sum(DKJE) 贷款金额 from I_CLXX where DKBH= '{0}' group by DKJE
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
string date = DateTime.Now.ToString("yyyyMMdd");
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, sheetName + jxscode + "贷款车辆_" + date);
//return Json(new { Count = "" }, JsonRequestBehavior.AllowGet);
}
public JsonResult Export(string startYM, string endYM, string state)
{
string sql = @"select BILL_NO 付款编号
,to_char(BILL_DATE,'yyyy-mm') 月份
,case when to_char(BILL_DATE,'yyyy-mm')=to_char(sysdate,'yyyy-mm') then to_char(sysdate-1,'yyyy-mm-dd')
else to_char(last_day(trunc(BILL_DATE,'MONTH')),'yyyy-mm-dd') end 计息截止日期
,to_char(BILL_DUE_DATE,'yyyy-mm-dd') 付款到期日
,V.Asset_Num 付息车辆数 " + ",V.\"Invoice_Amount\" 利息金额 "
+
@",Case when v.STATUS_CODE = 'CL' then '已付' else '未付' end 利息账单状态
from IN_WFS.V_ALL_BILL v where v.Agency_Code='{0}' and v.BILL_NO like 'FPM%' {1}
";
#region 查询条件
string conditions = "";
if (!string.IsNullOrEmpty(startYM))
{
conditions += string.Format(" AND to_char(BILL_DATE,'yyyy-mm') >= '{0}' ", startYM);
}
if (!string.IsNullOrEmpty(endYM))
{
conditions += string.Format(" AND to_char(BILL_DATE,'yyyy-mm') <= '{0}' ", endYM);
}
if (!string.IsNullOrEmpty(state))
{
if (state == "已付")
{
conditions += string.Format(" AND v.STATUS_CODE = '{0}' ", "CL");
}
if (state == "未付")
{
conditions += string.Format(" AND v.STATUS_CODE = '{0}' ", "OP");
}
}
string UserCode = this.UserValidator.User.Appellation;
sql = string.Format(sql, UserCode, conditions);
DataTable dt = ExecuteDataTableSql(dataCode, sql);
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, "账单列表" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
#endregion
}
public JsonResult ExportLoan(string type, string FrameNo, string Models, string state, string Manufacturer, string FrameNoNature, string address, string StartTime, string EndTime)
{
string sheetName = string.Empty;
DataTable dt = new DataTable();
if (type == "H3")
{
dt = ExportH3LoanList(FrameNo, Models, state, Manufacturer, FrameNoNature, address, StartTime, EndTime);
sheetName = "待提交";
}
else
{
dt = ExportWFSLoanList(FrameNo, Models, state, Manufacturer, FrameNoNature, address, StartTime, EndTime);
}
string date = DateTime.Now.ToString("yyyyMMdd");
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, sheetName + "贷款列表导出" + date);
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}
static unsafe void UnsafeCode2()
{
Console.WriteLine("Size of Currency struct is " + sizeof(CurrencyStruct));
CurrencyStruct amount1, amount2;
CurrencyStruct *pAmount = &amount1;
long * pDollars = &(pAmount->Dollars);
byte * pCents = &(pAmount->Cents);
Console.WriteLine("Address of amount1 is 0x{0:X}", (uint)&amount1);
Console.WriteLine("Address of amount2 is 0x{0:X}", (uint)&amount2);
Console.WriteLine("Address of pAmt is 0x{0:X}", (uint)&pAmount);
Console.WriteLine("Address of pDollars is 0x{0:X}", (uint)&pDollars);
Console.WriteLine("Address of pCents is 0x{0:X}", (uint)&pCents);
pAmount->Dollars = 20;
*pCents = 50;
Console.WriteLine("amount1 contains " + amount1);
--pAmount; // this should get it to point to amount2
Console.WriteLine("amount2 has address 0x{0:X} and contains {1}",
(uint)pAmount, *pAmount);
// do some clever casting to get pCents to point to cents
// inside amount2
CurrencyStruct *pTempCurrency = (CurrencyStruct *)pCents;
pCents = (byte *)(--pTempCurrency);
Console.WriteLine("Address of pCents is now 0x{0:X}", (uint)&pCents);
Console.WriteLine("\nNow with classes");
// now try it out with classes
CurrencyClass amount3 = new CurrencyClass();
fixed(long *pDollars2 = &(amount3.Dollars))
fixed(byte *pCents2 = &(amount3.Cents))
{
Console.WriteLine("amount3.Dollars has address 0x{0:X}", (uint)pDollars2);
Console.WriteLine("amount3.Cents has address 0x{0:X}", (uint)pCents2);
*pDollars2 = -100;
Console.WriteLine("amount3 contains " + amount3);
}
}
/// <summary>
/// 查询语句
/// </summary>
public DataSet CreditUdInfo(PagerInfo pagerInfo, string JXS, string StartTime, string EndTime)
{
var UserID = this.UserValidator.UserID;
//获取经销商code
string codesql = string.Format(@"select
distinct C.JXSCODE JXSCODE
from I_CREDITEXTENSION C
inner join ot_instancecontext i on i.bizobjectid=c.objectid
inner join OT_WorkItem w on w.instanceid=i.objectid
where w.WORKFLOWCODE='CreditExtension'and Participant='{0}' ", UserID);
DataTable codedt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(codesql);
for (int i = 0; i < codedt.Rows.Count; i++)
{
var UserCode = codedt.Rows[i]["JXSCODE"].ToString();
string flag = new CurrencyClass().CheckTime(UserCode);
if (flag == "1")
{
new CurrencyClass().SUMMARY(UserCode);
}
}
string sql = string.Format(@"select * from (select distinct
w.OBJECTID w_obj,
c.OBJECTID c_obj,
Z.CREDIT_LIMIT_SYNC z_sxed,
v.availed_limit dkye ,
Z.AVAILABLE_LIMIT_SYNC z_kysxed ,
Z.dealer_code z_jxscode,
to_char(C.SXDQR,'yyyy-mm-dd hh24:mm:ss') c_sxdqr,
C.UserCode c_jxscode,
C.JXS c_jxs
from I_CREDITEXTENSION C
left join IN_WFS.V_ZSUMMARY_DLR@to_auth_wfs Z
on trim(Z.dealer_code)=trim(C.JXSCODE)
join in_wfs.v_dealer_balance_limit@to_auth_wfs v on trim(v.dealer_code)=trim(C.JXSCODE)
inner join ot_instancecontext i on i.bizobjectid=c.objectid
inner join OT_WorkItem w on w.instanceid=i.objectid
where w.WORKFLOWCODE='CreditExtension'and Participant='{0}' ) where 1=1 ", UserID);//and , UserID, -- Z.UTILIZED_LIMIT_SYNC z_jzjryyed ,
#region 查询条件
if (!string.IsNullOrEmpty(JXS))
{
sql += string.Format(" and c_jxs ='{0}'", JXS);
}
if (!string.IsNullOrEmpty(StartTime))
{
sql += string.Format(" AND c_sxdqr >= to_char(to_date('{0}','yyyy-mm-dd'),'yyyy-mm-dd')\r\n", StartTime);
}
if (!string.IsNullOrEmpty(EndTime))
{
sql += string.Format(" AND c_sxdqr < to_char(to_date('{0}','yyyy-mm-dd'),'yyyy-mm-dd')\r\n", EndTime);
}
#endregion
#region 排序
string OrderBy = " ORDER BY c_sxdqr desc ";
if (pagerInfo.iSortCol_0 != 0)
{
OrderBy = " ORDER BY ";
if (pagerInfo.iSortCol_0 == 4)
{
OrderBy += " c_sxdqr " + pagerInfo.sSortDir_0.ToUpper();
}
}
sql += OrderBy;
var sql1 = "select a1.* from (SELECT rownum rn, aa.* from ( " + sql + " ) aa ) a1 where 1=1";
DataTable count = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql1);
#endregion
int startindex = pagerInfo.StartIndex == 1 ? 0 : pagerInfo.StartIndex;
sql1 += "and rn <=" + pagerInfo.EndIndex.ToString() + " and rn >= " + startindex.ToString();
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql1);
DataSet ds = new DataSet();
ds.Tables.Add(count);
ds.Tables.Add(dt);
return(ds);
}
static unsafe void UnsafeCode2()
{
Console.WriteLine("Size of Currency struct is " + sizeof(CurrencyStruct));
CurrencyStruct amount1, amount2;
CurrencyStruct* pAmount = &amount1;
long* pDollars = &(pAmount->Dollars);
byte* pCents = &(pAmount->Cents);
Console.WriteLine("Address of amount1 is 0x{0:X}", (uint)&amount1);
Console.WriteLine("Address of amount2 is 0x{0:X}", (uint)&amount2);
Console.WriteLine("Address of pAmt is 0x{0:X}", (uint)&pAmount);
Console.WriteLine("Address of pDollars is 0x{0:X}", (uint)&pDollars);
Console.WriteLine("Address of pCents is 0x{0:X}", (uint)&pCents);
pAmount->Dollars = 20;
*pCents = 50;
Console.WriteLine("amount1 contains " + amount1);
--pAmount; // this should get it to point to amount2
Console.WriteLine("amount2 has address 0x{0:X} and contains {1}",
(uint)pAmount, *pAmount);
// do some clever casting to get pCents to point to cents
// inside amount2
CurrencyStruct* pTempCurrency = (CurrencyStruct*)pCents;
pCents = (byte*)(--pTempCurrency);
Console.WriteLine("Address of pCents is now 0x{0:X}", (uint)&pCents);
Console.WriteLine("\nNow with classes");
// now try it out with classes
CurrencyClass amount3 = new CurrencyClass();
fixed (long* pDollars2 = &(amount3.Dollars))
fixed (byte* pCents2 = &(amount3.Cents))
{
Console.WriteLine("amount3.Dollars has address 0x{0:X}", (uint)pDollars2);
Console.WriteLine("amount3.Cents has address 0x{0:X}", (uint)pCents2);
*pDollars2 = -100;
Console.WriteLine("amount3 contains " + amount3);
}
}
public JsonResult ExportDTL(PagerInfo pagerInfo, string fkbh, string cjh, string cx)
{
string sql = @" select distinct
V2.UNIT_NO 车辆编号
,V2.ASSET_DESC 车型
,V2.vin_no 车架号
,to_char(V2.CALC_END_DATE,'yyyy-mm-dd') 计息截至日期
<<<<<<< .mine
,case when V2.TRANSACTION_TYPE ='DSP' then to_char(V3.LL) else null end 产品利率
,V2.REQUESTED_AMT 贷款金额
||||||| .r1089
,case when V2.TRANSACTION_TYPE ='DSP' then V3.LL else null end 产品利率
,V.REQUESTED_AMT 贷款金额
=======
,case when V2.TRANSACTION_TYPE ='DSP' then V3.LL else null end 产品利率
,V2.REQUESTED_AMT 贷款金额
>>>>>>> .r1485
,V2.TRANSACTION_AMT 利息金额
,CASE When V2.TRANSACTION_TYPE ='DSP'then '利息' else '罚息' end 交易类型
from IN_WFS.V_ASSET_FPM_INFO V2
join (select wm_concat( CONCAT(CONCAT(CONCAT( CONCAT(CONCAT
( DAY_FROM,'-') ,DAY_TO),'天 '), INTEREST_RATE),'%')) LL
,asset_code
,make
,dealer_code
from( select distinct v.day_from,v.day_to,v.interest_rate,v.asset_code,v.make,dealer_code from IN_WFS.V_DEALER_MAKE_MODEL_RATE v
where v.dealer_code = '{0}' order by day_to)
group by make,asset_code,dealer_code) V3 on trim(V2.ASSET_CODE) = trim(V3.ASSET_CODE) and trim(V2.MAKE) = trim(V3.MAKE) and trim(v2.dealer_code) = trim(V3.dealer_code)
where trim(V2.dealer_code) = '{0}' and V2.BILL_NO ='{1}' {2} {3}";
string conditions = "";
if (!string.IsNullOrEmpty(cjh))
{
conditions += string.Format("AND V2.vin_no = '{0}' ", cjh);
}
if (!string.IsNullOrEmpty(cx))
{
conditions += string.Format("AND V2.ASSET_DESC like '%{0}%' ", cx);
}
#region 排序
string OrderBy = " ORDER BY V2.UNIT_NO ";
if (pagerInfo.iSortCol_0 != 0)
{
OrderBy = " ORDER BY ";
if (pagerInfo.iSortCol_0 == 0)
{
OrderBy += " V2.UNIT_NO " + pagerInfo.sSortDir_0.ToUpper();
}
}
#endregion
string UserCode = this.UserValidator.User.Appellation;
sql = string.Format(sql, UserCode, fkbh, conditions, OrderBy);
DataTable dt = ExecuteDataTableSql(dataCode, sql);
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, "账单明细列表" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}
public JsonResult ExportHK(PagerInfo pagerInfo, string JXS, string JXSCODE)
{
string sql = string.Format(@"select jxs 经销商名称 ,jxsbm 经销商编码 ,to_char(EXPIRY_DATE,'yyyy-mm-dd') 授信到期日,BONDPROPORTION 保证金比例 ,a.TOTALDZJE 到账总金额, a.TOTALLXJE 还款利息金额,a.TOTALHKJE 还款本金,a.wfpbj2 未分配金额,to_char(jedzsj,'yyyy-mm-dd') 入账日期,to_char(cjjedzsj,'yyyy-mm-dd hh24:mi:ss') 创建入账时间,c.sequenceno 编号
from i_Repayment a
join i_Dzjelist b on a.objectid = b.parentobjectid
join Ot_Instancecontext c on c.bizobjectid = a.objectid
join IN_WFS.V_ZSUMMARY_DLR@TO_AUTH_WFS v on trim(v.dealer_code)=trim(a.jxsbm)
left join i_bond j on trim(a.jxsbm)=trim(j.distributorcode) and FINALSTATE='已生效' where c.state=4");
#region 查询条件
if (!string.IsNullOrEmpty(JXS))
{
sql += string.Format(" and a.jxs like '%{0}%'", JXS);
}
if (!string.IsNullOrEmpty(JXSCODE))
{
sql += string.Format(" and a.jxsbm like '%{0}%'", JXSCODE);
}
#endregion
#region 排序
string OrderBy = " ORDER BY a.jxsbm";
if (pagerInfo.iSortCol_0 != 0)
{
OrderBy = " ORDER BY ";
if (pagerInfo.iSortCol_0 == 1)
{
OrderBy += " a.jxsbm " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 2)
{
OrderBy += " EXPIRY_DATE " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 3)
{
OrderBy += " TOTALLXJE " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 4)
{
OrderBy += " TOTALDZJE " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 5)
{
OrderBy += " TOTALHKJE " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 6)
{
OrderBy += " wfpbj2 " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 7)
{
OrderBy += " jedzsj " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 8)
{
OrderBy += " cjjedzsj " + pagerInfo.sSortDir_0.ToUpper();
}
}
sql += OrderBy;
#endregion
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, "经销商还款信息" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}
public JsonResult Export(PagerInfo pagerInfo, string JXS, string JXSCODE)
{
string sql = @"select distinct trim(i.DEALER_NAME) 经销商名称,
i.DEALER_CODE 经销商编号,
to_char(EXPIRY_DATE,'yyyy-mm-dd') 授信到期日,
CREDIT_LIMIT_SYNC 核准融资额度,
UTILIZED_LIMIT_SYNC 截至今日可用额度,
AVAILABLE_LIMIT_SYNC 目前可用授信额度,
BONDPROPORTION 保证金比例,
FPM_UNPAID 未付利息金额,
TOTAL_UNITS 已放款车辆总计,
VIN_NO_UPDATED_NUM 车架号待修改,
ASSET_OVERDUE 已到期车辆,
VEHICLES_MATURING_IN_2_WEEKS 近1月即将到期车辆
from IN_WFS.V_ZSUMMARY_DLR@TO_AUTH_WFS v
inner join IN_WFS.V_DEALER_INFO@TO_AUTH_WFS i on v.dealer_code=i.dealer_code
left join i_bond j on trim(v.dealer_code)=j.distributorcode and FINALSTATE='已生效'
where 1=1 {0} {1}";
#region 查询条件
string conditions = "";
if (!string.IsNullOrEmpty(JXS))
{
conditions += string.Format(" and i.DEALER_NAME like '%{0}%'", JXS);
}
if (!string.IsNullOrEmpty(JXSCODE))
{
conditions += string.Format(" and i.DEALER_CODE like '%{0}%'", JXSCODE);
}
#endregion
#region 排序
string OrderBy = " ORDER BY i.DEALER_CODE";
if (pagerInfo.iSortCol_0 != 0)
{
OrderBy = " ORDER BY ";
if (pagerInfo.iSortCol_0 == 6)
{
OrderBy += " FPM_UNPAID " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 2)
{
OrderBy += " to_char(EXPIRY_DATE,'yyyy-mm-dd') " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 1)
{
OrderBy += " i.DEALER_CODE " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 3)
{
OrderBy += " CREDIT_LIMIT_SYNC " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 4)
{
OrderBy += " UTILIZED_LIMIT_SYNC " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 5)
{
OrderBy += " AVAILABLE_LIMIT_SYNC " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 6)
{
OrderBy += " BONDPROPORTION " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 8)
{
OrderBy += " TOTAL_UNITS " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 9)
{
OrderBy += " VIN_NO_UPDATED_NUM " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 10)
{
OrderBy += " ASSET_OVERDUE " + pagerInfo.sSortDir_0.ToUpper();
}
else if (pagerInfo.iSortCol_0 == 11)
{
OrderBy += " VEHICLES_MATURING_IN_2_WEEKS " + pagerInfo.sSortDir_0.ToUpper();
}
}
#endregion
string UserCode = this.UserValidator.User.Appellation;
sql = string.Format(sql, conditions, OrderBy);
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
int count1 = 0;
int count2 = 0;
string dpcl1 = @"select count(1) c from IN_WFS.V_STOCK_DC_HIS@TO_AUTH_WFS v
join IN_WFS.V_DEALER_EXPIRY_DATE@TO_AUTH_WFS d on d.Make = v.Make and d.dealer_code = v.dealer_code
Left join (select a.* from (select states,a.HKSPRP,cjh,b.MODIFIEDTIME from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,a.HKSPRP,a.cjh,b.MODIFIEDTIME from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
join (
select MaX(a.MODIFIEDTIME) MODIFIEDTIME,cjh from (
select states,b.MODIFIEDTIME,cjh from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,b.MODIFIEDTIME,a.cjh from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
group by cjh) b on a.cjh = b.cjh and a.MODIFIEDTIME = b.MODIFIEDTIME ) c on c.cjh = V.VIN_NO
where (case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end)<to_char(d.CURR_DATE,'yyyy-mm-dd')
and c.states is null and v.dealer_code = {0}";
string dpcl2 = @"select count(1) c from
IN_WFS.V_SALES_WFS_DT@TO_AUTH_WFS v
join IN_WFS.V_DEALER_EXPIRY_DATE@TO_AUTH_WFS d on d.Make = v.Make and d.dealer_code = v.dealer_code
join IN_WFS.V_LOAN_STOCK_LIST@TO_AUTH_WFS v2 on v2.dealer_code = v.dealer_code and v2.UNIT_NO = v.UNIT_NO and v2.VIN_NO = v.VIN_NO
Left join (select a.* from (select states,a.HKSPRP,cjh,b.MODIFIEDTIME from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,a.HKSPRP,a.cjh,b.MODIFIEDTIME from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
join (
select MaX(a.MODIFIEDTIME) MODIFIEDTIME,cjh from (
select states,b.MODIFIEDTIME,cjh from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,b.MODIFIEDTIME,a.cjh from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
group by cjh) b on a.cjh = b.cjh and a.MODIFIEDTIME = b.MODIFIEDTIME ) c on c.cjh = V.VIN_NO
where v.dealer_code = {0} and Not EXISTS (select 1 from IN_WFS.V_STOCK_DC_HIS@TO_AUTH_WFS v1 where v1.VIN_NO=v.VIN_NO )
and V.LOAN_STATUS='60'and SETTLEMENT_DATE is null and v2.STOCK_STATUS = 'I' and (case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end)<to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null and v.PAID_AMT >0";
dpcl1 = string.Format(dpcl1, dt.Rows[i]["经销商编号"].ToString());
dpcl2 = string.Format(dpcl2, dt.Rows[i]["经销商编号"].ToString());
var dpclCount = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteScalar(dpcl1);
var dpclCount2 = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteScalar(dpcl2);
count1 = Convert.ToInt32(dpclCount) + Convert.ToInt32(dpclCount2);
string dpcl11 = @"select count(distinct V.UNIT_NO)
from IN_WFS.V_STOCK_DC_HIS@TO_AUTH_WFS v
join IN_WFS.V_DEALER_EXPIRY_DATE@TO_AUTH_WFS d on d.Make = v.Make and d.dealer_code = v.dealer_code
Left join (select a.* from (select states,a.HKSPRP,cjh,b.MODIFIEDTIME from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,a.HKSPRP,a.cjh,b.MODIFIEDTIME from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
join (
select MaX(a.MODIFIEDTIME) MODIFIEDTIME,cjh from (
select states,b.MODIFIEDTIME,cjh from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,b.MODIFIEDTIME,a.cjh from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
group by cjh) b on a.cjh = b.cjh and a.MODIFIEDTIME = b.MODIFIEDTIME ) c on c.cjh = V.VIN_NO
where v.dealer_code = {0} and (case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end)<to_char(ADD_MONTHS(d.CURR_DATE,1),'yyyy-mm-dd')
and (case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end)>=to_char(d.CURR_DATE,'yyyy-mm-dd')
and (c.states = '已拒绝' or c.states is null)";
string dpc12 = @"select count(V.UNIT_NO)
from IN_WFS.V_SALES_WFS_DT@TO_AUTH_WFS v
join IN_WFS.V_DEALER_EXPIRY_DATE@TO_AUTH_WFS d on d.Make = v.Make and d.dealer_code = v.dealer_code
join IN_WFS.V_LOAN_STOCK_LIST@TO_AUTH_WFS v2 on v2.dealer_code = v.dealer_code and v2.UNIT_NO = v.UNIT_NO and v2.VIN_NO = v.VIN_NO
Left join (select a.* from (select states,a.HKSPRP,cjh,b.MODIFIEDTIME from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,a.HKSPRP,a.cjh,b.MODIFIEDTIME from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
join (
select MaX(a.MODIFIEDTIME) MODIFIEDTIME,cjh from (
select states,b.MODIFIEDTIME,cjh from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,b.MODIFIEDTIME,a.cjh from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
group by cjh) b on a.cjh = b.cjh and a.MODIFIEDTIME = b.MODIFIEDTIME ) c on c.cjh = V.VIN_NO
where v.PAID_AMT >0 and v.dealer_code = {0} and Not EXISTS (select 1 from IN_WFS.V_STOCK_DC_HIS@TO_AUTH_WFS v1 where v1.VIN_NO=v.VIN_NO )
and (case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end)<to_char(ADD_MONTHS(d.CURR_DATE,1),'yyyy-mm-dd')
and (case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end)>=to_char(d.CURR_DATE,'yyyy-mm-dd')
and V.LOAN_STATUS='60'and SETTLEMENT_DATE is null and v2.STOCK_STATUS = 'I' and c.states is null and v.PAID_AMT >0";
dpcl11 = string.Format(dpcl11, dt.Rows[i]["经销商编号"].ToString());
dpc12 = string.Format(dpc12, dt.Rows[i]["经销商编号"].ToString());
var dpclCount11 = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteScalar(dpcl11);
var dpclCount12 = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteScalar(dpc12);
count2 = Convert.ToInt32(dpclCount11) + Convert.ToInt32(dpclCount12);
dt.Rows[i]["已到期车辆"] = count1;
dt.Rows[i]["近1月即将到期车辆"] = count2;
}
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, "经销商汇总信息" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}
public JsonResult Export(string FrameNo, string Models, string state, string Manufacturer, string FrameNoNature, string startTime, string endTime, string address, string repaymentStartTime, string repaymentEndTime, string CLBH)
{
string UserCode = this.UserValidator.User.Appellation;
string sql = @" select 车辆编号,车型,车架号性质,车架号,贷款到期日,贷款金额,还款状态,还款申请日期,制造商 from ( select a.* from (
select distinct V.UNIT_NO 车辆编号
,V.MODEL 车型
,case when (V.ORDER_NO = 'Y' and V.VIN_UPDATE = 'N') then '临时' else '永久' end 车架号性质
,V.VIN_NO 车架号
,case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end 贷款到期日
--,V.Total_FPM LXZJ
,V.REQUESTED_AMT 贷款金额
,case when (to_char(V.MATURITY_DATE,'yyyy-mm-dd')>=to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null ) and (to_char(d.expiry_date,'yyyy-mm-dd')>=to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null) then '未到期'
when (to_char(V.MATURITY_DATE,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null) or (to_char(d.expiry_date,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null)then '已到期'
when c.states = '已拒绝' then '已拒绝'
else '处理中' end 还款状态
,to_char(c.HKSPRP,'yyyy-mm-dd HH24:mi:ss') 还款申请日期
,V.MAKE 制造商
,case when (to_char(V.MATURITY_DATE,'yyyy-mm-dd')>=to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null ) and (to_char(d.expiry_date,'yyyy-mm-dd')>=to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null) then 1
when (to_char(V.MATURITY_DATE,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null) or (to_char(d.expiry_date,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd')and c.states is null)then 2
when c.states = '已拒绝' then 3
else 4 end HKZT_ORDER
--,(select to_char(vv.TRANSACTION_DATE,'yyyy-mm-dd') from IN_WFS.V_PV_INFO@TO_AUTH_WFS vv where trim(vv.agency_code)=trim(v.dealer_code) and trim(vv.UNIT_NO)=trim(v.UNIT_NO)) 贷款起始日
from IN_WFS.V_STOCK_DC_HIS@TO_AUTH_WFS v
join IN_WFS.V_DEALER_EXPIRY_DATE@TO_AUTH_WFS d on d.Make = v.Make and d.dealer_code = v.dealer_code
Left join IN_WFS.V_VIN_UPDATE_HIS@TO_AUTH_WFS v1 on v1.new_value = V.VIN_NO
Left join (select a.* from (select states,a.HKSPRP,cjh,b.MODIFIEDTIME from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,a.HKSPRP,a.cjh,b.MODIFIEDTIME from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
join (
select MaX(a.MODIFIEDTIME) MODIFIEDTIME,cjh from (
select states,b.MODIFIEDTIME,cjh from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,b.MODIFIEDTIME,a.cjh from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
group by cjh) b on a.cjh = b.cjh and a.MODIFIEDTIME = b.MODIFIEDTIME ) c on c.cjh = V.VIN_NO
where v.dealer_code = {0}
union
select V.UNIT_NO 车辆编号
,V.ASSET_DESC 车型
, '永久' 车架号性质
,V.VIN_NO 车架号
,case when d.expiry_date <V.MATURITY_DATE then to_char(d.expiry_date,'yyyy-mm-dd')else to_char(V.MATURITY_DATE,'yyyy-mm-dd') end 贷款到期日
,V.APPROVED_AMT 贷款金额
, case when (V.LOAN_STATUS='60'and SETTLEMENT_DATE is not null) then '已付清'
when (V.LOAN_STATUS='60'and SETTLEMENT_DATE is null and v2.STOCK_STATUS = 'I'and c.states is null and （to_char(V.MATURITY_DATE,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd') or to_char(d.expiry_date,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd'))) then '已到期'
else '处理中' end 还款状态
,nvl(to_char(C.HKSPRP,'yyyy-mm-dd HH24:mi:ss'),to_char(v.SETTLEMENT_DATE,'yyyy-mm-dd HH24:mi:ss')） 还款申请日期
,V.MAKE 制造商
, case when (V.LOAN_STATUS='60'and SETTLEMENT_DATE is not null) then 5
when (V.LOAN_STATUS='60'and SETTLEMENT_DATE is null and v2.STOCK_STATUS = 'I'and c.states is null and （to_char(V.MATURITY_DATE,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd') or to_char(d.expiry_date,'yyyy-mm-dd')<to_char(d.CURR_DATE,'yyyy-mm-dd'))) then 1
else 4 end HKZT_ORDER
--,(select to_char(vv.TRANSACTION_DATE,'yyyy-mm-dd') from IN_WFS.V_PV_INFO@TO_AUTH_WFS vv where trim(vv.agency_code)=trim(v.dealer_code) and trim(vv.UNIT_NO)=trim(v.UNIT_NO)) 贷款起始日
from IN_WFS.V_SALES_WFS_DT@TO_AUTH_WFS v
join IN_WFS.V_DEALER_EXPIRY_DATE@TO_AUTH_WFS d on d.Make = v.Make and d.dealer_code = v.dealer_code
join IN_WFS.V_LOAN_STOCK_LIST@TO_AUTH_WFS v2 on v2.dealer_code = v.dealer_code and v2.UNIT_NO = v.UNIT_NO and v2.VIN_NO = v.VIN_NO
Left join IN_WFS.V_VIN_UPDATE_HIS@TO_AUTH_WFS v1 on v1.new_value = V.VIN_NO
Left join (select a.* from (select states,a.HKSPRP,cjh,b.MODIFIEDTIME from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,a.HKSPRP,a.cjh,b.MODIFIEDTIME from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
join (
select MaX(a.MODIFIEDTIME) MODIFIEDTIME,cjh from (
select states,b.MODIFIEDTIME,cjh from I_HK_List a
join i_repaymentdistributor b on a.parentobjectid = b.objectid where trim(a.JXSBM) ='{0}'
union
select a.states,b.MODIFIEDTIME,a.cjh from I_HKList a
join i_Repayment b on a.parentobjectid = b.objectid where b.JXSBM ='{0}') a
group by cjh) b on a.cjh = b.cjh and a.MODIFIEDTIME = b.MODIFIEDTIME ) c on c.cjh = V.VIN_NO
where v.dealer_code = {0} and v.PAID_AMT >0 and Not EXISTS (select 1 from IN_WFS.V_STOCK_DC_HIS@TO_AUTH_WFS v1 where v1.VIN_NO=v.VIN_NO )) a where 1=1 {1} ORDER BY HKZT_ORDER asc, a.还款申请日期 desc ,a.车辆编号 asc ) q ";//a.还款申请日期 desc , a.车辆编号
#region 查询条件
string conditions = "";
if (!string.IsNullOrEmpty(FrameNo))
{
conditions += string.Format("AND ( a.车架号 like '%{0}%' or a.车架号 like '%{0}%') ", FrameNo);
}
if (!string.IsNullOrEmpty(Models))
{
conditions += string.Format("AND a.车型 like '%{0}%' ", Models);
}
if (!string.IsNullOrEmpty(state))
{
conditions += string.Format("AND a.还款状态 = '{0}' ", state);
}
if (!string.IsNullOrEmpty(Manufacturer))
{
conditions += string.Format("AND a.制造商 like '%{0}%' ", Manufacturer);
}
if (!string.IsNullOrEmpty(FrameNoNature))
{
conditions += string.Format("AND a.车架号性质 = '{0}' ", FrameNoNature);
}
//if (!string.IsNullOrEmpty(address))
//{
// conditions += string.Format("AND trim(V.ADDRESS) ='{0}' ", address);
// conditions2 += string.Format("AND trim(V.ADDRESS) ='{0}' ", address);
//}
if (!string.IsNullOrEmpty(startTime))
{
conditions += string.Format("AND 贷款到期日>='{0}'\r\n", startTime);
}
if (!string.IsNullOrEmpty(endTime))
{
conditions += string.Format("AND 贷款到期日<='{0}'\r\n", endTime);
}
if (!string.IsNullOrEmpty(repaymentStartTime))
{
conditions += string.Format("AND substr(还款申请日期,0,10)>='{0}'\r\n", repaymentStartTime);
}
if (!string.IsNullOrEmpty(repaymentEndTime))
{
conditions += string.Format("AND substr(还款申请日期,0,10)<='{0}'\r\n", repaymentEndTime);
}
if (!string.IsNullOrEmpty(CLBH))
{
conditions += string.Format("AND a.车辆编号 like '%{0}%' ", CLBH);
}
#endregion
sql = string.Format(sql, UserCode, conditions);
DataTable dt = this.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
CurrencyClass dd = new CurrencyClass();
dd.ExportReportCurrency(dt, "还款列表" + DateTime.Now.ToString("yyyyMMdd"));
return(Json(new { Count = "" }, JsonRequestBehavior.AllowGet));
}