public static Ui.DataGrid GetRoomPhoneRelationGridByRoomID(int RoomID, string RelationType, string orderBy, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomID > 0)
{
conditions.Add("[RoomID]=@RoomID");
parameters.Add(new SqlParameter("@RoomID", RoomID));
}
if (!string.IsNullOrEmpty(RelationType))
{
conditions.Add("([RelationType]=@RelationType or [RelationType] is null)");
parameters.Add(new SqlParameter("@RelationType", RelationType));
}
string fieldList = "[RoomPhoneRelation].* ";
string Statement = " from [RoomPhoneRelation] where " + string.Join(" and ", conditions.ToArray());
RoomPhoneRelation[] list = new RoomPhoneRelation[] { };
list = GetList <RoomPhoneRelation>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static RoomPhoneRelation GetRoomPhoneRelationsByRoomIDAndPhone(int RoomID, string Phone, SqlHelper helper)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomID > 0)
{
conditions.Add("[RoomID]=@RoomID");
parameters.Add(new SqlParameter("@RoomID", RoomID));
}
if (!string.IsNullOrEmpty(Phone))
{
conditions.Add("[RelatePhoneNumber]=@RelatePhoneNumber");
parameters.Add(new SqlParameter("@RelatePhoneNumber", Phone));
}
RoomPhoneRelation relation = GetOne <RoomPhoneRelation>("SELECT top 1 * FROM [RoomPhoneRelation] where " + string.Join(" and ", conditions.ToArray()), parameters, helper);
return(relation);
}
public static ViewRoomBasic[] GetViewRoomBasicListByUserID(User user, bool IncludeRelation = true, string PhoneNumber = "")
{
var phone_list = RoomPhoneRelation.GetRoomPhoneRelationsByPhone(PhoneNumber, uid: user.FinalUserID);
Mall_UserProject.Insert_UserProjectList(phone_list, user);
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[isParent]=0");
List <string> cmdlist = new List <string>();
string cmdwhere = " and [UserID]=@UserID and isnull([IsDisable],0)=0";
cmdlist.Add("[RoomID] in (select [ProjectID] from [Mall_UserProject] where 1=1 " + cmdwhere + ")");
if (IncludeRelation)
{
cmdlist.Add("[RoomID] in (select [RoomID] from [RoomRelation] where [GUID] in (select [GUID] from [RoomRelation] where [RoomID] in (select [ProjectID] from [Mall_UserProject] where 1=1 " + cmdwhere + ")))");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
parameters.Add(new SqlParameter("@UserID", user.FinalUserID));
ViewRoomBasic[] list = new ViewRoomBasic[] { };
list = GetList <ViewRoomBasic>("select * from [ViewRoomBasic] where " + string.Join(" and ", conditions.ToArray()) + " order by [DefaultOrder] asc", parameters).ToArray();
return(list);
}
public static RoomPhoneRelation GetDefaultInChargeFeeRoomPhoneRelation(int RoomID, int ContractID, SqlHelper helper)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ContractID > 0)
{
conditions.Add("[ContractID]=@ContractID");
parameters.Add(new SqlParameter("@ContractID", ContractID));
}
else if (RoomID > 0)
{
conditions.Add("[RoomID]=@RoomID");
parameters.Add(new SqlParameter("@RoomID", RoomID));
}
RoomPhoneRelation data = GetOne <RoomPhoneRelation>("select * from [RoomPhoneRelation] where [IsChargeFee]=1 and " + string.Join(" and ", conditions.ToArray()), parameters, helper);
if (data == null)
{
data = GetOne <RoomPhoneRelation>("select * from [RoomPhoneRelation] where [IsChargeMan]=1 and " + string.Join(" and ", conditions.ToArray()), parameters, helper);
}
return(data);
}
public static RoomFee SetInfo_RoomFee(int RoomID, DateTime StartTime, DateTime EndTime, decimal Cost, decimal RealCost, decimal UnitPrice, int ChargeID, RoomFee data = null, decimal UseCount = 0, string Remark = "", bool IsCharged = false, int ChargeFeeID = 0, bool IsStart = true, decimal Discount = 0, decimal ChargeFee = 0, decimal TotalRealCost = 0, decimal TotalDiscountCost = 0, decimal RestCost = 0, int ContractID = 0, int RelatedFeeID = 0, int DefaultChargeManID = 0, string DefaultChargeManName = "", int Contract_RoomChargeID = 0, int ContractDivideID = 0, bool cansave = false, DateTime?NewEndTime = null, bool OnlyOnceCharge = false, int OutDays = 0, int DiscountID = 0, DateTime?CuiShouStartTime = null, DateTime?CuiShouEndTime = null, int ChongDiChargeID = 0, string TradeNo = "", int OrderID = 0, bool IsTempFee = false, bool IsMeterFee = false, bool IsImportFee = false, bool IsCycleFee = false, int ImportFeeID = 0, decimal RoomFeeCoefficient = 0, DateTime?RoomFeeWriteDate = null, int ChargeMeterProjectFeeID = 0, decimal RoomFeeStartPoint = 0, decimal RoomFeeEndPoint = 0, SqlHelper helper = null, int HistoryRoomFeeID = 0)
{
if (data == null)
{
data = new RoomFee();
data.AddTime = DateTime.Now;
data.AddUserName = User.GetCurrentUserName();
}
data.RoomID = RoomID;
data.UseCount = UseCount;
data.StartTime = StartTime;
data.EndTime = EndTime;
data.Cost = Cost;
data.RealCost = RealCost;
data.Remark = Remark;
data.IsCharged = IsCharged;
data.ChargeFeeID = 0;
data.ChargeID = ChargeID;
data.IsStart = IsStart;
data.UnitPrice = UnitPrice;
data.Discount = Discount;
data.ChargeFee = ChargeFee;
data.TotalRealCost = TotalRealCost;
data.TotalDiscountCost = TotalDiscountCost;
data.RestCost = RestCost;
data.ContractID = ContractID;
data.RelatedFeeID = RelatedFeeID;
if (DefaultChargeManID <= 0)
{
RoomPhoneRelation default_relation = null;
if (helper == null)
{
default_relation = RoomPhoneRelation.GetDefaultInChargeFeeRoomPhoneRelation(data.RoomID, data.ContractID);
}
else
{
default_relation = RoomPhoneRelation.GetDefaultInChargeFeeRoomPhoneRelation(data.RoomID, data.ContractID, helper);
}
if (default_relation != null)
{
DefaultChargeManID = default_relation.ID;
DefaultChargeManName = default_relation.RelationName;
}
}
data.DefaultChargeManID = DefaultChargeManID;
data.DefaultChargeManName = DefaultChargeManName;
data.Contract_RoomChargeID = Contract_RoomChargeID;
data.ContractDivideID = ContractDivideID;
if (NewEndTime != null && NewEndTime.HasValue)
{
data.NewEndTime = Convert.ToDateTime(NewEndTime);
}
data.OnlyOnceCharge = OnlyOnceCharge;
data.OutDays = OutDays;
data.DiscountID = DiscountID;
if (CuiShouStartTime != null && CuiShouStartTime.HasValue)
{
data.CuiShouStartTime = Convert.ToDateTime(CuiShouStartTime);
}
if (CuiShouEndTime != null && CuiShouEndTime.HasValue)
{
data.CuiShouEndTime = Convert.ToDateTime(CuiShouEndTime);
}
data.ChongDiChargeID = ChongDiChargeID;
data.TradeNo = TradeNo;
data.OrderID = OrderID;
data.IsTempFee = IsTempFee;
data.IsMeterFee = IsMeterFee;
data.IsImportFee = IsImportFee;
data.IsCycleFee = IsCycleFee;
data.ImportFeeID = ImportFeeID;
data.RoomFeeCoefficient = RoomFeeCoefficient;
data.ChargeMeterProjectFeeID = ChargeMeterProjectFeeID;
data.RoomFeeStartPoint = RoomFeeStartPoint;
data.RoomFeeEndPoint = RoomFeeEndPoint;
if (RoomFeeWriteDate != null && RoomFeeWriteDate.HasValue)
{
data.RoomFeeWriteDate = Convert.ToDateTime(RoomFeeWriteDate);
}
if (cansave)
{
if (helper == null)
{
using (helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
data.Save(helper);
ChargeFeePriceRange.CreateChargeFeePriceRangeByImportFeeIDList(helper, RoomFeeID: data.ID, HistoryRoomFeeID: HistoryRoomFeeID);
helper.Commit();
}
catch (Exception)
{
helper.Rollback();
}
}
}
else
{
data.Save(helper);
ChargeFeePriceRange.CreateChargeFeePriceRangeByImportFeeIDList(helper, RoomFeeID: data.ID, HistoryRoomFeeID: HistoryRoomFeeID);
}
}
return(data);
}
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 GetMall_DoorCardDetailGridByKeywords(string keywords, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime)
{
long totalRows = 0;
string OrderBy = " order by [CardName] asc, [SortOrder] asc, [AddTime] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[AddTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[AddTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([CardName] like @keywords or [CardSummary] like @keywords or [CardUID] like @keywords or [DoorNumber] like @keywords or ([ID] in (select [DoorCardID] from [Mall_DoorCardDevice] where [DoorDeviceID] in (select [DeviceID] from [Mall_DoorDevice] where [DeviceName] like @keywords))))");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
if (ProjectIDList.Count > 0 && RoomIDList.Count == 0)
{
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
}
List <string> cmdlist = new List <string>();
if (RoomIDList.Count > 0)
{
conditions.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[A].[RoomPhoneRelationID] and[RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
}
if (EqualIDList.Count > 0)
{
cmdlist.Add("[ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ")");
cmdlist.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[A].[RoomPhoneRelationID] and exists(select 1 from [Project] where ID=[RoomPhoneRelation].[RoomID] and [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ")))");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (InIDList.Count > 0)
{
cmdlist = new List <string>();
foreach (var InID in InIDList)
{
cmdlist.Add("([ProjectID] in (select ID from [Project] where AllParentID like '%," + InID.ToString() + ",%'))");
cmdlist.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[A].[RoomPhoneRelationID] and exists(select 1 from [Project] where ID=[RoomPhoneRelation].[RoomID] and AllParentID like '%," + InID.ToString() + ",%'))");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string fieldList = "[A].*";
string Statement = " from (select *,(select top 1 [StartTime] from [RoomFeeHistory] where [ChargeID] in (select ID from [ChargeSummary] where [ChargeFeeType]=1) and ChargeState in (1,4) and [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].RoomPhoneRelationID) order by ChargeTime desc) as StartTime,(select top 1 [EndTime] from [RoomFeeHistory] where [ChargeID] in (select ID from [ChargeSummary] where [ChargeFeeType]=1) and ChargeState in (1,4) and [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].RoomPhoneRelationID) order by ChargeTime desc) as EndTime,(select top 1 [StartTime] from [RoomFee] where [ChargeID] in (select ID from [ChargeSummary] where [ChargeFeeType]=1) and [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].RoomPhoneRelationID) and StartTime is not null order by StartTime asc) as FeeStartTime from [Mall_DoorCard])A where " + string.Join(" and ", conditions.ToArray());
Mall_DoorCardDetail[] list = GetList <Mall_DoorCardDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
var mList = list.Where(p => p.UserID > 0).ToArray();
int MinUserID = 0;
int MaxUserID = 0;
if (mList.Length > 0)
{
MinUserID = mList.Min(p => p.UserID);
MaxUserID = mList.Max(p => p.UserID);
}
var userList = User.GetSysUserList(IncludeLock: false, MinUserID: MinUserID, MaxUserID: MaxUserID);
List <int> card_idlist = list.Select(p => p.ID).ToList();
var card_device_list = Mall_DoorCardDevice.GetMall_DoorCardDevices().Where(p => card_idlist.Contains(p.DoorCardID)).ToArray();
var device_list = Mall_DoorDevice.GetALLActiveMall_DoorDeviceList().ToArray();
mList = list.Where(p => p.RoomPhoneRelationID > 0).ToArray();
int MinID = 0;
int MaxID = 0;
if (mList.Length > 0)
{
MinID = mList.Min(p => p.RoomPhoneRelationID);
MaxID = mList.Max(p => p.RoomPhoneRelationID);
}
var relationList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxID(MinID, MaxID);
int MinRoomID = 0;
int MaxRoomID = 0;
if (relationList.Length > 0)
{
MinRoomID = relationList.Min(p => p.RoomID);
MaxRoomID = relationList.Max(p => p.RoomID);
}
var projectList = Project.GetProjectListByMinMaxID(MinRoomID, MaxRoomID);
foreach (var item in list)
{
var my_card_device_list = card_device_list.Where(p => p.DoorCardID == item.ID).ToArray();
List <int> my_deviceidlist = my_card_device_list.Select(p => p.DoorDeviceID).ToList();
var my_device_list = device_list.Where(p => my_deviceidlist.Contains(p.DeviceID)).ToArray();
if (my_device_list.Length > 0)
{
item.DeviceInfo = string.Join(",", my_device_list.Select(p =>
{
return("【" + p.DeviceID + "," + p.DeviceName + "】");
}).ToArray());
}
var myRelation = relationList.FirstOrDefault(p => p.ID == item.RoomPhoneRelationID);
if (myRelation != null)
{
var myProject = projectList.FirstOrDefault(p => p.ID == myRelation.RoomID);
item.UserInfo = myRelation.RelationName;
if (myProject != null)
{
item.UserInfo += "【" + myProject.FullName + "-" + myProject.Name + "】";
}
}
var myUser = userList.FirstOrDefault(q => q.LoginName == item.AddUserName);
if (myUser != null)
{
item.AddUserName = myUser.FinalRealName;
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString["RoomID"] != null)
{
int.TryParse(Request.QueryString["RoomID"], out RoomID);
}
if (Request.QueryString["DefaultRelationID"] != null)
{
int.TryParse(Request.QueryString["DefaultRelationID"], out DefaultRelationID);
}
if (Request.QueryString["ContractID"] != null)
{
int.TryParse(Request.QueryString["ContractID"], out ContractID);
}
if (Request.QueryString["PrintID"] != null)
{
int.TryParse(Request.QueryString["PrintID"], out PrintID);
}
if (PrintID > 0)
{
var history_list = ViewRoomFeeHistory.GetViewRoomFeeHistoryListByPrintID(PrintID);
if (history_list.Length > 0)
{
DefaultRelationID = history_list[0].DefaultChargeManID;
RoomID = history_list[0].RoomID;
ContractID = history_list[0].ContractID;
CanPrintCheque = true;
}
}
Foresight.DataAccess.RoomPhoneRelation data = null;
RoomBasic basic = null;
using (SqlHelper helper = new SqlHelper())
{
if (DefaultRelationID > 0)
{
data = RoomPhoneRelation.GetRoomPhoneRelation(DefaultRelationID, helper);
}
if (data == null)
{
data = RoomPhoneRelation.GetDefaultInChargeFeeRoomPhoneRelation(RoomID, ContractID, helper);
}
if (data == null)
{
basic = RoomBasic.GetRoomBasicByRoomID(RoomID, helper);
}
}
if (data != null)
{
this.RelationID = data.ID;
this.tdCompanyName.Value = data.CompanyName;
this.tdAddress.Value = data.HomeAddress;
this.tdBuyerTaxpayerNumber.Value = data.TaxpayerNumber;
this.tdBuyerBankAccountNo.Value = data.BankAccountNo;
this.tdBuyerBankName.Value = data.BankName;
this.tdBuyerEmailAddress.Value = data.EmailAddress;
}
if (basic != null)
{
this.tdCompanyName.Value = basic.ChequeCompanyName;
this.tdAddress.Value = basic.ChequeAddress;
this.tdBuyerTaxpayerNumber.Value = basic.ChequeTaxpayerNumber;
this.tdBuyerBankAccountNo.Value = basic.ChequeBankNo;
this.tdBuyerBankName.Value = basic.ChequeBankName;
this.tdBuyerEmailAddress.Value = basic.ChequeEmailAddress;
}
var company = Company.GetCompanies().FirstOrDefault();
if (company != null)
{
this.tdSellerCompanyName.Value = string.IsNullOrEmpty(company.ChequeTitle) ? company.CompanyName : company.ChequeTitle;
this.tdSellerAddress.Value = company.Address;
this.tdSellerTaxpayerNumber.Value = company.TaxpayerNumber;
this.tdSellerBankNo.Value = company.BankAccountNo;
this.tdSellerBankName.Value = company.BankName;
this.tdReCheckUserName.Value = company.ReCheckUserName;
this.tdCheque_SL.Value = company.Cheque_SL > decimal.MinValue ? company.Cheque_SL.ToString("0.00") : string.Empty;
this.tdCheque_FLBM.Value = company.Cheque_FLBM;
}
}
}
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);
}
