public JsonResult Getloanlist(string[] param)
{
string QUERYKEY = param[0];
string MessageType = param[1];
var UserID = this.UserValidator.UserID;
Organization.Unit u = OThinker.H3.Controllers.AppUtility.Engine.Organization.GetUnit(UserID);
string UserCode = ((OThinker.Organization.User)u).Appellation;
string sql = string.Empty;
if (MessageType == "1")
{
#region 贷款申请状态
sql = string.Format(@" select distinct UNIT_NO CLBH , CX , CJHXZ, CJH,
TO_CHAR(DKJE ,'FM999,999,999,999,990.00') DKJE,
case when V.LOAN_STATUS ='05' or V.LOAN_STATUS ='10' then '处理中'
when V.LOAN_STATUS = '40' and V.PAID_AMT>0 then '已放款'
when V.LOAN_STATUS = '40' and V.PAID_AMT<=0 then '处理中'
when V.LOAN_STATUS = '50' or V.LOAN_STATUS ='30' then '已拒绝'
when V.LOAN_STATUS = '60' then '已售出' end SQZT
from I_CLXX C join IN_WFS.V_LOAN_STOCK_LIST@To_Auth_Wfs v on trim(c.CJH) = trim(v.VIN_NO) where DKBH='{0}'", QUERYKEY);
#endregion
}
else if (MessageType == "3")
{
#region 临时车架号提醒
// sql = string.Format(@"select UNIT_NO CLBH ,ASSET_DESC CX,VIN_NO CJH from IN_WFS.V_LOAN_STOCK_LIST v
// where ORDER_NO = 'Y' and VIN_UPDATE = 'N' and V.LOAN_STATUS = '40' and V.PAID_AMT>0
// and STOCK_DATE <(to_date('{0}','yyyy-MM-dd') - 5） and v.dealer_code='{1}'",
// DateTime.Now.ToShortDateString(), UserCode);
sql = string.Format(@"select distinct UNIT_NO CLBH ,MODEL CX,VIN_NO CJH from
IN_WFS.V_STOCK_DC_HIS v where ORDER_NO = 'Y' and VIN_UPDATE = 'N'
and LOAN_GIVEN_DATE <=(to_date('{0}','yyyy-MM-dd') - 5)
and v.dealer_code='{1}'", DateTime.Now.ToShortDateString(), QUERYKEY);
#endregion
}
else if (MessageType == "4")
{
#region 车架号修改
sql = string.Format(@"select BATCH, VEHICLENUMBER CLBH, CARMODEL CX , TEMPORARYFRAMENUMBER LSCJH, PERMANENTFRAMENUMBER YJCJH from I_FrameNumberMiddleTable where BATCH='{0}'", QUERYKEY);
#endregion
}
DataTable dt = new DataTable();
DistributorController DC = new DistributorController();
if (MessageType == "4" || MessageType == "1")
{
dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
}
else
{
dt = DC.ExecuteDataTableSql("Wholesale", sql);
}
return(Json(new { RowData = DistributorController.ToJson(dt) }, JsonRequestBehavior.AllowGet));
}
public JsonResult GetOPRWorkItemList(PagerInfo pagerInfo, string distributorName, string startDate, string endDate)
{
int total = 0;
DataTable dtWorkitem = QueryOPRWorkItemListData(pagerInfo, distributorName, startDate, endDate, ref total);
return(Json(new { RowCount = total, RowData = DistributorController.ToJson(dtWorkitem) }, JsonRequestBehavior.AllowGet));
}
public JsonResult GetMessageList(PagerInfo pagerInfo, string State)
{
DataSet ds = MessageList(pagerInfo, State);
DistributorController dc = new DistributorController();
DistributorController.ToJson(ds.Tables[0]);
int _RowCount = ds.Tables[0].Rows.Count;
return(Json(new { RowCount = _RowCount, RowData = DistributorController.ToJson(ds.Tables[1]), _Count = ds.Tables[2].Rows.Count }, JsonRequestBehavior.AllowGet));
}
public JsonResult GetWORKITEMID(string QUERYKEY)
{
var UserID = this.UserValidator.UserID;
#region 清算异常
string sql = string.Format(@" select objectid WORKITEMID from Ot_Workitemfinished where instanceid='{0}' and PARTICIPANT='{1}'", QUERYKEY, UserID);
#endregion
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
return(Json(new { RowData = DistributorController.ToJson(dt) }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 查询
/// </summary>
public JsonResult GetLoanApprovalInfo(PagerInfo pagerInfo, string JXS, string StartTime, string EndTime)
{
DataSet ds = LoanApproval(pagerInfo, JXS, StartTime, EndTime);
DistributorController dc = new DistributorController();
DistributorController.ToJson(ds.Tables[0]);
int _RowCount = ds.Tables[0].Rows.Count;
return(Json(new { RowCount = _RowCount, RowData = DistributorController.ToJson(ds.Tables[1]) }, JsonRequestBehavior.AllowGet));
}
public JsonResult JXSHKInfo(PagerInfo pagerInfo, string JXS, string JXSCODE)
{
DataSet ds = GETJXSHKInfo(pagerInfo, JXS, JXSCODE);
DistributorController dc = new DistributorController();
DistributorController.ToJson(ds.Tables[0]);
int _RowCount = ds.Tables[0].Rows.Count;
return(Json(new { RowCount = _RowCount, RowData = DistributorController.ToJson(ds.Tables[1]) }, 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>
/// <param name="userCode"></param>
public void ExportToExcelByTemplate(string MAKE)
{
var UserID = this.UserValidator.UserID;
Organization.Unit u = OThinker.H3.Controllers.AppUtility.Engine.Organization.GetUnit(UserID);
string userCode = ((OThinker.Organization.User)u).Appellation;
string msg = string.Empty;
try
{
DistributorController DC = new DistributorController();
#region 取数据可卖车型、颜色
string sql = "select distinct trim(ASSET_CODE） ASSET_CODE ,trim(ASSET_DESC） ASSET_DESC from IN_WFS.V_DEALER_MAKE_MODEL_NAME_CODE v where v.dealer_code =" + userCode + " and MAKE ='" + MAKE + "'";
DataTable CXTable = DC.ExecuteDataTableSql("Wholesale", sql);
string sql2 = "select trim(COLOR) COLOR,trim(COLOR_CODE) COLOR_CODE from IN_WFS.V_DEALER_MAKE_COLOR v where v.dealer_code = " + userCode + " and MAKE ='" + MAKE + "'";
DataTable YSTable = DC.ExecuteDataTableSql("Wholesale", sql2);
#endregion
#region 打开Excel表格模板，并初始化到NPOI对象中
IWorkbook wk = null;
string filePath = Server.MapPath(@"~/Sheets/Wholesale/Distributor/申请贷款模板.xlsx");
if (!System.IO.File.Exists(filePath))
{
msg = "模板不存在！";
OThinker.H3.Controllers.AppUtility.Engine.LogWriter.Write(msg);
return;
}
string extension = System.IO.Path.GetExtension(filePath);
FileStream fs = System.IO.File.OpenRead(filePath);
if (extension.Equals(".xls"))
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
}
fs.Close();
#endregion
#region 数据处理
ISheet sheet = wk.GetSheetAt(1);
ISheet sheetRef = wk.GetSheet("Sheet2");
ISheet sheetRef2 = wk.GetSheet("Sheet3");
ICell cell1 = null; //数据行中的第一列
ICell cell22 = null; //数据行中的第二列
int CXCount = CXTable.Rows.Count;
int YSCount = YSTable.Rows.Count;
for (int i = 0; i < CXCount; i++)
{
var row = sheetRef.CreateRow(i + 1);
cell1 = row.CreateCell(0);
cell1.SetCellValue(CXTable.Rows[i]["ASSET_DESC"] + string.Empty);
//if (i < YSCount)
//{
// cell2 = row.CreateCell(1);
// cell2.SetCellValue(YSTable.Rows[i]["COLOR"] + string.Empty);
//}
}
for (int i = 0; i < YSCount; i++)
{
var row = sheetRef2.CreateRow(i + 1);
cell22 = row.CreateCell(0);
cell22.SetCellValue(YSTable.Rows[i]["COLOR"] + string.Empty);
}
#endregion
#region 表格导出
System.IO.MemoryStream ms = new System.IO.MemoryStream();
wk.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(MAKE + "申请贷款模版", System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
wk = null;
#endregion
}
catch (Exception ex)
{
msg = ex.ToString();
OThinker.H3.Controllers.AppUtility.Engine.LogWriter.Write(msg);
}
}