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));
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="UserID"></param>
/// <param name="Propertystr"></param>
/// <returns></returns>
public JsonResult GetUserProperty(string UserID, string Propertystr)
{
return(ExecuteFunctionRun(() =>
{
if (string.IsNullOrWhiteSpace(UserID))
{
return null;
}
string[] Propertys = Propertystr.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
if (Propertys == null)
{
return null;
}
Dictionary <string, object> PropertyJson = new Dictionary <string, object>();
Organization.Unit unit = this.Engine.Organization.GetUnit(UserID);
if (unit == null)
{
unit = this.Engine.Organization.GetUserByCode(UserID);
if (unit == null)
{
return null;
}
}
foreach (string p in Propertys)
{
if (PropertyJson.ContainsKey(p))
{
continue;
}
switch (p)
{
case Organization.User.PropertyName_DepartmentName:
if (unit.UnitType == Organization.UnitType.User)
{
PropertyJson.Add(p, ((OThinker.Organization.User)unit).DepartmentName);
}
break;
default:
PropertyInfo PropertyInfo = unit.GetType().GetProperty(p);
if (PropertyInfo == null)
{
PropertyJson.Add(p, null);
}
else
{
PropertyJson.Add(p, PropertyInfo.GetValue(unit, null));
}
break;
}
}
return Json(PropertyJson, JsonRequestBehavior.AllowGet);
}, string.Empty));
}
public DataSet MessageList(PagerInfo pagerInfo, string State)
{
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.Format(@"SELECT rownum rn, aa.* from （
select
OBJECTID,USERID, USERCODE, MESSAGE,
to_char(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss') RECEIVETIME ,
ENDTIME,
MESSAGESTATE,
MessageType,
QueryKey
from I_Messagelist
WHERE USERID='{0}' ORDER BY RECEIVETIME desc ) aa where 1=1", UserID);// or USERCODE='{1}', UserCode
#region 查询条件
//总条数
DataTable count = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sql);
if (!string.IsNullOrEmpty(State))
{
sql += string.Format(" and MESSAGESTATE ='{0}'", State);
}
#endregion
// 未读总计
string sqlUN = string.Format(@"select MESSAGESTATE num from I_Messagelist WHERE USERID='{0}' and MESSAGESTATE=0", UserID, UserCode);
DataTable CountUN = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sqlUN);
string sqlstr = "select a1.* from ( " + sql + ") a1 where 1=1 ";
int startindex = pagerInfo.StartIndex == 1 ? 0 : pagerInfo.StartIndex;
sqlstr += "and rn <=" + pagerInfo.EndIndex.ToString() + " and rn >= " + startindex.ToString();
DataTable dt = OThinker.H3.Controllers.AppUtility.Engine.EngineConfig.CommandFactory.CreateCommand().ExecuteDataTable(sqlstr);
DataSet ds = new DataSet();
ds.Tables.Add(count);
ds.Tables.Add(dt);
ds.Tables.Add(CountUN);
return(ds);
}
/// <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);
}
}
/// <summary>
/// 获取指定数据项的值
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
private string GetFieldValue(string field)
{
string param = string.Empty;
Instance.IInstanceDataItem data = this.ActionContext.InstanceData[field];
if (data == null && field.IndexOf(".") > -1)
{
// 处理field逻辑
param = field.Substring(field.IndexOf(".") + 1);
field = field.Substring(0, field.IndexOf("."));
data = this.ActionContext.InstanceData[field];
}
if (data == null)
{
return(string.Empty);
}
string result = string.Empty;
switch (data.LogicType)
{
case Data.DataLogicType.String:
case Data.DataLogicType.ShortString:
case Data.DataLogicType.Decimal:
case Data.DataLogicType.Double:
case Data.DataLogicType.Int:
case Data.DataLogicType.Html:
case Data.DataLogicType.Long:
case Data.DataLogicType.TimeSpan:
result = data.Value + string.Empty;
break;
case Data.DataLogicType.DateTime:
DateTime d = DateTime.Now;
DateTime.TryParse(data.Value + string.Empty, out d);
result = d.ToString("yyyy-MM-dd");
break;
case Data.DataLogicType.SingleParticipant:
if (data.Value + string.Empty != string.Empty)
{
result = this.Engine.Organization.GetName(data.Value + string.Empty);
}
break;
case Data.DataLogicType.MultiParticipant:
{
string[] users = data.Value as string[];
if (users != null && users.Length > 0)
{
Organization.Unit[] units = this.Engine.Organization.GetUnits(users).ToArray();
if (units != null)
{
foreach (Organization.Unit unit in units)
{
result += result == string.Empty ? string.Empty : "、";
result += unit.Name;
}
}
}
}
break;
case Data.DataLogicType.Attachment:
{
Data.AttachmentHeader[] headers = this.Engine.BizObjectManager.QueryAttachment(
this.ActionContext.SchemaCode,
this.ActionContext.BizObjectID,
field,
OThinker.Data.BoolMatchValue.True,
null);
if (headers != null)
{
for (int i = 0; i < headers.Length; i++)
{
result += result == string.Empty ? string.Empty : "<br>";
result += (i + 1) + "、" + headers[i].FileName;
}
}
}
break;
case Data.DataLogicType.Comment:
string activity = string.Empty;
string userId = string.Empty;
if (param.ToLower().StartsWith("activity:"))
{
activity = param.Substring(param.IndexOf(":") + 1);
}
else if (param.ToLower().StartsWith("user:"******":") + 1));
if (u != null)
{
userId = u.ObjectID;
}
}
Data.Comment[] comments = GetComments(field, userId, activity);
if (comments != null)
{
if (param.ToLower() == "username")
{
Organization.Unit u = this.Engine.Organization.GetUnit(comments[comments.Length - 1].UserID);
result = u != null ? u.Name : comments[comments.Length - 1].UserID;
}
else if (param.ToLower() == "datetime")
{
result = comments[comments.Length - 1].CreatedTime.ToString("yyyy-MM-dd");
}
else
{
result = comments[comments.Length - 1].Text;
}
}
break;
case Data.DataLogicType.Bool:
bool b = false;
bool.TryParse(data.Value + string.Empty, out b);
result = b ? "SheetPrint_Yes" : "SheetPrint_No";
break;
case Data.DataLogicType.BizObject:
if (data != null)
{
result = GetBizObjectFieldValue(((BizObject)data), param);
}
break;
case Data.DataLogicType.BizObjectArray:
// 子表打印，需要定义重复节
break;
default:
result = string.Empty;
break;
}
return(result);
}
