public static Ui.DataGrid GetRoomBasicListByKeywords(List <int> RoomIDList, List <int> ProjectIDList, string RoomOwner, string OwnerPhone, string Keywords, List <string> SearchAreas, string orderBy, long startRowIndex, int pageSize, string OpenID, int ConnectStatus, bool canexport = false, int CompanyID = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([isParent],0)=0");
if (CompanyID > 0)
{
conditions.Add("exists(select 1 from Project where [CompanyID]=@CompanyID and [ID]=ViewRoomBasic.RoomID)");
parameters.Add(new SqlParameter("@CompanyID", CompanyID));
}
if (!string.IsNullOrEmpty(OpenID))
{
if (ConnectStatus > int.MinValue)
{
if (ConnectStatus == 1)
{
conditions.Add("[RoomID] in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
else if (ConnectStatus == 0)
{
conditions.Add("[RoomID] not in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
}
}
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
cmd += " and (" + GetSearchConditions(SearchAreas, Keywords) + ")";
}
#endregion
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [RoomID] =" + ProjectID + ")");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(RoomOwner))
{
conditions.Add("[RoomOwner]=@RoomOwner");
parameters.Add(new SqlParameter("@RoomOwner", RoomOwner));
}
if (!string.IsNullOrEmpty(OwnerPhone))
{
conditions.Add("[OwnerPhone]=@OwnerPhone");
parameters.Add(new SqlParameter("@OwnerPhone", OwnerPhone));
}
string fieldList = "[ViewRoomBasic].*";
string Statement = " from [ViewRoomBasic] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewRoomBasic[] list = new ViewRoomBasic[] { };
var phoneList = new RoomPhoneRelation[] { };
int MinRoomID = 0;
int MaxRoomID = 0;
if (canexport)
{
list = GetList <ViewRoomBasic>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewRoomBasic>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
if (list.Length == 0)
{
dg.rows = list;
dg.total = 0;
dg.page = pageSize;
return(dg);
}
MinRoomID = list.Min(p => p.RoomID);
MaxRoomID = list.Max(p => p.RoomID);
phoneList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxRoomID(MinRoomID, MaxRoomID);
var fieldlist = Foresight.DataAccess.DefineField.GetDefineFieldsByTable_Name(Utility.EnumModel.DefineFieldTableName.RoomBasic.ToString()).Where(p => p.IsShown).ToArray();
var contentlist = Foresight.DataAccess.RoomBasicField.GetRoomBasicFieldsByRoomIDList(MinRoomID, MaxRoomID);
var results = list.Select(p =>
{
var dic = p.ToJsonObject(ignoreDBColumn: false);
dic["RoomOwner1Name"] = string.Empty;
dic["RoomOwner1Phone"] = string.Empty;
dic["RoomOwner2Name"] = string.Empty;
dic["RoomOwner2Phone"] = string.Empty;
dic["Rent1Name"] = string.Empty;
dic["Rent1Phone"] = string.Empty;
if (phoneList.Length > 0)
{
var myPhoneList1 = phoneList.Where(q => q.RoomID == p.RoomID && q.RelationType.Equals("homefamily")).OrderByDescending(q => q.IsDefault).ThenBy(q => q.ID).ToArray();
var myPhoneList2 = phoneList.Where(q => q.RoomID == p.RoomID && q.RelationType.Equals("rentfamily")).OrderByDescending(q => q.IsDefault).ThenBy(q => q.ID).ToArray();
int count = 0;
foreach (var item in myPhoneList1)
{
count++;
dic["RoomOwner" + count.ToString() + "Name"] = item.RelationName;
dic["RoomOwner" + count.ToString() + "Phone"] = item.RelatePhoneNumber;
}
count = 0;
foreach (var item in myPhoneList2)
{
count++;
dic["Rent" + count.ToString() + "Name"] = item.RelationName;
dic["Rent" + count.ToString() + "Phone"] = item.RelatePhoneNumber;
}
}
foreach (var item in fieldlist)
{
var contentmodel = contentlist.FirstOrDefault(q => q.FieldID == item.ID && q.RoomID == p.RoomID);
dic[item.FieldName] = contentmodel == null ? "" : contentmodel.FieldContent;
}
return(dic);
}).ToList();
dg.rows = results;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetRoomBasicListByKeywords(List <int> RoomIDList, List <int> ProjectIDList, string RoomOwner, string OwnerPhone, string Keywords, List <string> SearchAreas, string orderBy, long startRowIndex, int pageSize, string OpenID, int ConnectStatus, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([isParent],0)=0");
if (!string.IsNullOrEmpty(OpenID))
{
if (ConnectStatus > int.MinValue)
{
if (ConnectStatus == 1)
{
conditions.Add("[RoomID] in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
else if (ConnectStatus == 0)
{
conditions.Add("[RoomID] not in (select [ProjectID] from [WechatUser_Project] where OpenID=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
}
}
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
cmd += " and (" + GetSearchConditions(SearchAreas, Keywords) + ")";
}
#endregion
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [RoomID] =" + ProjectID + ")");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(RoomOwner))
{
conditions.Add("[RoomOwner]=@RoomOwner");
parameters.Add(new SqlParameter("@RoomOwner", RoomOwner));
}
if (!string.IsNullOrEmpty(OwnerPhone))
{
conditions.Add("[OwnerPhone]=@OwnerPhone");
parameters.Add(new SqlParameter("@OwnerPhone", OwnerPhone));
}
string fieldList = "[ViewRoomBasic].*";
string Statement = " from [ViewRoomBasic] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewRoomBasic[] list = new ViewRoomBasic[] { };
var phoneList = new RoomPhoneRelation[] { };
int MinRoomID = 0;
int MaxRoomID = 0;
if (canexport)
{
list = GetList <ViewRoomBasic>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
if (list.Length > 0)
{
MinRoomID = list.Min(p => p.RoomID);
MaxRoomID = list.Max(p => p.RoomID);
}
phoneList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxRoomID(MinRoomID, MaxRoomID);
}
else
{
list = GetList <ViewRoomBasic>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
MinRoomID = list.Min(p => p.RoomID);
MaxRoomID = list.Max(p => p.RoomID);
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
var fieldlist = Foresight.DataAccess.DefineField.GetDefineFieldsByTable_Name(Utility.EnumModel.DefineFieldTableName.RoomBasic.ToString()).Where(p => p.IsShown).ToArray();
var contentlist = Foresight.DataAccess.RoomBasicField.GetRoomBasicFieldsByRoomIDList(MinRoomID, MaxRoomID);
var results = list.Select(p =>
{
var dic = p.ToJsonObject(ignoreDBColumn: false);
if (phoneList.Length > 0)
{
var myPhoneList = phoneList.Where(q => q.RoomID == p.RoomID).OrderByDescending(q => q.IsDefault).ThenBy(q => q.ID).ToArray();
if (myPhoneList.Length > 0)
{
var strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationName)).Select(q => q.RelationName).ToArray();
if (strList.Length > 0)
{
dic["RelationName"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationTypeDesc)).Select(q => q.RelationTypeDesc).ToArray();
if (strList.Length > 0)
{
dic["RelationTypeDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelatePhoneNumber)).Select(q => q.RelatePhoneNumber).ToArray();
if (strList.Length > 0)
{
dic["RelatePhoneNumber"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IDCardTypeDesc)).Select(q => q.IDCardTypeDesc).ToArray();
if (strList.Length > 0)
{
dic["IDCardTypeDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationIDCard)).Select(q => q.RelationIDCard).ToArray();
if (strList.Length > 0)
{
dic["RelationIDCard"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BirthdayDesc)).Select(q => q.BirthdayDesc).ToArray();
if (strList.Length > 0)
{
dic["Birthday"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.EmailAddress)).Select(q => q.EmailAddress).ToArray();
if (strList.Length > 0)
{
dic["EmailAddress"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.HomeAddress)).Select(q => q.HomeAddress).ToArray();
if (strList.Length > 0)
{
dic["HomeAddress"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.OfficeAddress)).Select(q => q.OfficeAddress).ToArray();
if (strList.Length > 0)
{
dic["OfficeAddress"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BankAccountName)).Select(q => q.BankAccountName).ToArray();
if (strList.Length > 0)
{
dic["BankAccountName"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BankAccountNo)).Select(q => q.BankAccountNo).ToArray();
if (strList.Length > 0)
{
dic["BankAccountNo"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomOne)).Select(q => q.CustomOne).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomOne"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomTwo)).Select(q => q.CustomTwo).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomTwo"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomThree)).Select(q => q.CustomThree).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomThree"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CustomFour)).Select(q => q.CustomFour).ToArray();
if (strList.Length > 0)
{
dic["RelateCustomFour"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.Interesting)).Select(q => q.Interesting).ToArray();
if (strList.Length > 0)
{
dic["Interesting"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.ConsumeMore)).Select(q => q.ConsumeMore).ToArray();
if (strList.Length > 0)
{
dic["ConsumeMore"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.BelongTeam)).Select(q => q.BelongTeam).ToArray();
if (strList.Length > 0)
{
dic["BelongTeam"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.OneCardNumber)).Select(q => q.OneCardNumber).ToArray();
if (strList.Length > 0)
{
dic["OneCardNumber"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.ChargeForMan)).Select(q => q.ChargeForMan).ToArray();
if (strList.Length > 0)
{
dic["ChargeForMan"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IsDefaultDesc)).Select(q => q.IsDefaultDesc).ToArray();
if (strList.Length > 0)
{
dic["IsDefaultDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IsChargeFeeDesc)).Select(q => q.IsChargeFeeDesc).ToArray();
if (strList.Length > 0)
{
dic["IsChargeFeeDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.RelationPropertyDesc)).Select(q => q.RelationPropertyDesc).ToArray();
if (strList.Length > 0)
{
dic["RelationPropertyDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.CompanyName)).Select(q => q.CompanyName).ToArray();
if (strList.Length > 0)
{
dic["CompanyName"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.IsChargeManDesc)).Select(q => q.IsChargeManDesc).ToArray();
if (strList.Length > 0)
{
dic["IsChargeManDesc"] = string.Join(",", strList);
}
strList = myPhoneList.Where(q => !string.IsNullOrEmpty(q.Remark)).Select(q => q.Remark).ToArray();
if (strList.Length > 0)
{
dic["RoomPhoneRelationRemark"] = string.Join(",", strList);
}
}
}
foreach (var item in fieldlist)
{
var contentmodel = contentlist.FirstOrDefault(q => q.FieldID == item.ID && q.RoomID == p.RoomID);
dic[item.FieldName] = contentmodel == null ? "" : contentmodel.FieldContent;
}
return(dic);
}).ToList();
dg.rows = results;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}