public string GetSavedSearches(string DBConnectionString, SaveSearchesRequestData objSavedSearchData)
{
string response = string.Empty;
try
{
DataSet _objDsSearch = new DataSet();
_objDsSearch = DBTask.ExecuteDataset(DBConnectionString, "dh_cl_get_search", objSavedSearchData.SportId, objSavedSearchData.UserId == 0 ? null : objSavedSearchData.UserId, objSavedSearchData.SearchTypeId);
if (_objDsSearch.Tables != null && _objDsSearch.Tables.Count > 0)
{
response = JSONHelper.FromDataTable(_objDsSearch.Tables[0]);
}
}
catch (Exception ex)
{
response = ex.ToString();
}
return(response);
}
/// <summary>
/// 获取入伙待办、进场待办，入伙:Type=1，进场:Type=2
/// </summary>
private string GetOccupationList(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
if (!row.Table.Columns.Contains("Type") || string.IsNullOrEmpty(row["Type"].ToString()))
{
return(JSONHelper.FromString(false, "Type不能为空"));
}
string commID = row["CommID"].ToString();
string type = row["Type"].ToString();
string sql = string.Empty;
if (type == "1")
{
// 入伙待办
sql = @"SELECT ('OccuID='+OccupationId) AS Id, RoomSign, CustName, RoomID, CustID, MobilePhone, PaperCode
FROM View_Tb_Occupation_OccupationBacklog
WHERE OccupationState='待入伙' AND CommID={0} AND ISNULL(IsDelete,0)=0";
}
else if (type == "2")
{
// 进场待办，关联合同
sql = @"SELECT ('ContID='+convert(NVARCHAR(36), x.ContID)) AS Id,x.RoomSign, x.CustName,
x.CustomerLiveRoomID AS RoomID, x.CustID, y.MobilePhone, y.PaperCode
FROM view_HSPR_GetReadyList_Filter x LEFT JOIN Tb_HSPR_Customer y ON x.CustID=y.CustID
WHERE ISNULL(ApproachState, '')='' AND x.CommID={0} AND ISNULL(x.IsDelete,0)=0";
}
else if (type == "3")
{
// 退场待办，关联合同
sql = @"SELECT ('WithdrawalID='+CONVERT(NVARCHAR(36), Id)) AS Id,RoomSign,CustName,RoomID, CustID, MobilePhone, PaperCode FROM VIEW_Tb_Customer_Withdrawal WHERE State = '待退场' AND CommID = '{0}'";
}
else
{
return(new ApiResult(false, "未知任务类型").toJson());
}
sql = string.Format(sql, commID);
int PageIndex = 1;
int PageSize = 10;
if (row.Table.Columns.Contains("PageIndex"))
{
PageIndex = Global_Fun.StrToInt(row["PageIndex"].ToString());
}
if (row.Table.Columns.Contains("PageSize"))
{
PageSize = Global_Fun.StrToInt(row["PageSize"].ToString());
}
using (IDbConnection con = new SqlConnection(PubConstant.hmWyglConnectionString))
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@FldName", "*");
parameters.Add("@PageSize", PageSize);
parameters.Add("@PageIndex", PageIndex);
parameters.Add("@FldSort", "RoomID");
parameters.Add("@Sort", 0);
parameters.Add("@StrCondition", sql);
parameters.Add("@Id", "Id");
parameters.Add("@PageCount", 0, DbType.Int32, ParameterDirection.Output);
parameters.Add("@Counts", 0, DbType.Int32, ParameterDirection.Output);
IEnumerable <dynamic> resultSet = con.Query <dynamic>("Proc_System_TurnPage", parameters, null, false, null, CommandType.StoredProcedure);
int PageCount = parameters.Get <int>("@PageCount");
int Counts = parameters.Get <int>("@Counts");
if (resultSet.Count() > 0)
{
List <dynamic> result = new List <dynamic>();
using (IDbConnection con2 = new SqlConnection(PubConstant.hmWyglConnectionString))
{
foreach (dynamic item in resultSet)
{
// 获取验房记录
sql = @"SELECT * FROM Tb_HSPR_HousingInsp WHERE CustID=@CustID AND RoomID=@RoomID ORDER BY InspDate DESC";
IEnumerable <dynamic> historySet = con2.Query(sql, new { CustID = Convert.ToInt64(item.CustID), RoomID = Convert.ToInt64(item.RoomID) });
item.History = historySet;
if (historySet.Count() > 0)
{
foreach (var historyItem in historySet)
{
sql = @"SELECT a.*, ((SELECT stuff((SELECT ','+FilePath FROM Tb_HSPR_HousingFiles
WHERE InspID=@InspID AND ProjID=a.ProjID FOR XML PATH('')),1,1,''))) AS FilePath
FROM Tb_HSPR_HousingInspDetail a WHERE a.InspID=@InspID ORDER BY ProjID";
IEnumerable <dynamic> historyDetailSet = con2.Query(sql, new { InspID = Convert.ToString(historyItem.InspID) });
historyItem.Detail = historyDetailSet;
}
}
result.Add(item);
}
string resultString = new ApiResult(true, result).toJson();
resultString = resultString.Insert(resultString.Length - 1, string.Format(",\"PageCount\":{0}", PageCount));
return(resultString);
}
}
return(JSONHelper.FromDataTable(new DataTable()));
}
}
/// <summary>
/// 客服动态
/// </summary>
/// <returns></returns>
public string GetIncidentDynamic(DataRow row)
{
#region
int iCommID = 0;
if (row.Table.Columns.Contains("CommID"))
{
iCommID = AppGlobal.StrToInt(row["CommID"].ToString());
}
string userCode = Global_Var.LoginUserCode;
if (row.Table.Columns.Contains("UserCode"))
{
userCode = row["UserCode"].ToString();
}
string organCode = null;
string strCommName = AppGlobal.GetCommName(iCommID);
string strOrganName = "";
//未分派的报事
int NoDispIncidentNum = 0;
//未完结的协调单
int NoCoordinateIncidentNum = 0;
//逾期的协调单
int BeOverdueIncidentNum = 0;
string strDate = "";
string strReText = "";
int iIncidentNum1 = 0;
int iIncidentNum2 = 0;
int iIncidentNum3 = 0;
int iIncidentNum4 = 0;
int iIncidentNum5 = 0;
decimal iIncidentRate1 = 0;
decimal iIncidentRate2 = 0;
decimal iIncidentRate3 = 0;
decimal iIncidentRate4 = 0;
#endregion
string strSQL = "";
int temp = 3;
// 项目客服动态
if (iCommID != 0)
{
strSQL = " and CommID = " + iCommID.ToString() + " and datediff(day,StatDate,getdate()) = 1 and StatType = 1 ";
}
else
{
if (row.Table.Columns.Contains("OrganCode"))
{
organCode = row["OrganCode"].ToString();
}
if (string.IsNullOrEmpty(organCode))
{
organCode = "01";
}
strOrganName = AppGlobal.GetOrganName4(organCode);
// 分公司
if (organCode.Length == 4)
{
temp = 2;
strSQL = " and OrganCode like '" + organCode + "%' and datediff(day,StatDate,getdate()) = 1 and StatType = 2 ";
}
// 总公司
if (organCode.Length == 2)
{
temp = 1;
strSQL = " and datediff(day,StatDate,getdate()) = 1 and StatType = 3 ";
}
}
MobileSoft.BLL.Sys.Bll_Tb_Sys_TakePic Bll = new MobileSoft.BLL.Sys.Bll_Tb_Sys_TakePic();
DataTable dTable = Bll.GetListFromProc(strSQL).Tables[0];
if (dTable.Rows.Count > 0)
{
DataRow DRow = dTable.Rows[0];
NoDispIncidentNum = AppGlobal.StrToInt(DRow["NoDispIncidentNum"].ToString());
NoCoordinateIncidentNum = AppGlobal.StrToInt(DRow["NoCoordinateIncidentNum"].ToString());
BeOverdueIncidentNum = AppGlobal.StrToInt(DRow["BeOverdueIncidentNum"].ToString());
iIncidentNum1 = AppGlobal.StrToInt(DRow["IncidentNum1"].ToString());
iIncidentNum2 = AppGlobal.StrToInt(DRow["IncidentNum2"].ToString());
iIncidentNum3 = AppGlobal.StrToInt(DRow["IncidentNum3"].ToString());
iIncidentNum4 = AppGlobal.StrToInt(DRow["IncidentNum4"].ToString());
iIncidentNum5 = AppGlobal.StrToInt(DRow["IncidentNum5"].ToString());
iIncidentRate1 = AppGlobal.StrToDec(DRow["IncidentRate1"].ToString());
iIncidentRate2 = AppGlobal.StrToDec(DRow["IncidentRate2"].ToString());
iIncidentRate3 = AppGlobal.StrToDec(DRow["IncidentRate3"].ToString());
iIncidentRate4 = AppGlobal.StrToDec(DRow["IncidentRate4"].ToString());
strDate = AppGlobal.StrToDate(DRow["StatDate"].ToString()).ToString("yyyy年MM月dd日");
}
dTable.Dispose();
if (string.IsNullOrEmpty(strDate))
{
strDate = DateTime.Now.ToString("yyyy年MM月dd日");
}
// 读取报时预警信息
MobileSoft.BLL.HSPR.Bll_Tb_HSPR_TempIncidentWarning Bll3 = new MobileSoft.BLL.HSPR.Bll_Tb_HSPR_TempIncidentWarning();
DataTable dTable3 = Bll3.GetList(userCode, iCommID).Tables[0];
string value1 = "";
string value2 = "";
string value3 = "";
if (dTable3.Rows.Count > 0)
{
DataRow DRow = dTable3.Rows[0];
value1 = DRow[0].ToString();
value2 = DRow[1].ToString();
value3 = DRow[2].ToString();
}
switch (temp)
{
case 3:
strReText = string.Format(@"【{0}】<br/>1、截止到{1}，未分派报事{2}件，未完结的派工单{3}件(其中逾期{4}件)，未完结的协调单{5}件(其中逾期{6}件)；本月派工单完结率{7}%，本年累计{8}%；本月协调单完结率{9}%，本年累计{10}%。<br/>2、{11}，共有{12}件报事，其中{13}件未完结。<br/>3、超时预警：已受理未分派超时工单{14}件,已分派未处理超时工单{15}件,已处理未回访超时工单{16}件；",
strCommName, strDate, NoDispIncidentNum.ToString(), iIncidentNum1.ToString(), iIncidentNum2.ToString(), NoCoordinateIncidentNum.ToString(), BeOverdueIncidentNum.ToString(), iIncidentRate1.ToString("N2"), iIncidentRate2.ToString("N2"), iIncidentRate3.ToString("N2"), iIncidentRate4.ToString("N2"), strDate, iIncidentNum4.ToString(), iIncidentNum5.ToString(), value1, value2, value3);
break;
case 2:
case 1:
strReText = string.Format(@"【{0}】<br/>1、截止到{1}，未分派报事{2}件，未完结的派工单{3}件(其中逾期{4}件)，未完结的协调单{5}件(其中逾期{6}件)；本月派工单完结率{7}%，本年累计{8}%；本月协调单完结率{9}%，本年累计{10}%。<br/>2、{11}，共有{12}件报事，其中{13}件未完结。<br/>3、超时预警：已受理未分派超时工单{14}件，已分派未处理超时工单{15}件,已处理未回访超时工单{16}件；",
strOrganName, strDate, NoDispIncidentNum.ToString(), iIncidentNum1.ToString(), iIncidentNum2.ToString(), NoCoordinateIncidentNum.ToString(), BeOverdueIncidentNum.ToString(), iIncidentRate1.ToString("N2"), iIncidentRate2.ToString("N2"), iIncidentRate3.ToString("N2"), iIncidentRate4.ToString("N2"), strDate, iIncidentNum4.ToString(), iIncidentNum5.ToString(), value1, value2, value3);
break;
}
// 客服动态图表统计
MobileSoft.BLL.Sys.Bll_Tb_Sys_TakePicIncidentTypeDeskTop Bll2 = new MobileSoft.BLL.Sys.Bll_Tb_Sys_TakePicIncidentTypeDeskTop();
DataTable dTable2 = Bll2.GetList(organCode, iCommID).Tables[0];
string chart = JSONHelper.FromDataTable(dTable2);
return(JSONHelper.FromJsonString(true, "{\"Info\":\"" + strReText + "\", \"Chart\":" + chart + "}"));
}