public static Ui.DataGrid GetContract_CustomerGrid(int ContractID, string orderBy, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[ContractID]=@ContractID");
parameters.Add(new SqlParameter("@ContractID", ContractID));
string fieldList = "[Contract_Customer].*";
string Statement = " from [Contract_Customer] where " + string.Join(" and ", conditions.ToArray());
var list = GetList <Contract_Customer>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
var chargeList = ChargeSummary.GetChargeSummaries().ToArray();
list = list.Select(p =>
{
var myChargeList = chargeList.Where(q => p.ChargeIDList.Contains(q.ID)).ToArray();
if (myChargeList.Length > 0)
{
p.ChargeName = string.Join(",", myChargeList.Select(q => q.Name).ToArray());
}
return(p);
}).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
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 Ui.DataGrid GetWechat_LotteryActivityPrizeGridByKeywords(string Keywords, int ActivityID, 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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([LevelName] like @Keywords or [PrizeName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (ActivityID > 0)
{
conditions.Add("[ActivityID]=@ActivityID");
parameters.Add(new SqlParameter("@ActivityID", ActivityID));
}
string fieldList = "[Wechat_LotteryActivityPrize].* ";
string Statement = " from [Wechat_LotteryActivityPrize] where " + string.Join(" and ", conditions.ToArray());
Wechat_LotteryActivityPrize[] list = GetList <Wechat_LotteryActivityPrize>(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 Ui.DataGrid GetSiteVersionGrid(string Keywords, string orderBy, long startRowIndex, int pageSize, string VersionType, bool OnlyAPP = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (OnlyAPP)
{
conditions.Add("([VersionType] = 'android' or [VersionType] = 'ios')");
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("[VersionDesc] like @Keywords");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (!string.IsNullOrEmpty(VersionType))
{
conditions.Add("[VersionType] = @VersionType");
parameters.Add(new SqlParameter("@VersionType", VersionType));
}
string fieldList = "[SiteVersion].* ";
string Statement = " from [SiteVersion] where " + string.Join(" and ", conditions.ToArray());
SiteVersion[] list = GetList <SiteVersion>(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 Ui.DataGrid GetGuaranteeRoomFeeGridByRoomID(List <int> RoomID, int CategoryID, string orderBy, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[ChargeState] in(1,4)");
conditions.Add("[RealCost]>isnull([BackCost],0)");
if (RoomID.Count > 0)
{
conditions.Add("[RoomID] in (" + string.Join(",", RoomID.ToArray()) + ")");
}
if (CategoryID > 0)
{
conditions.Add("[CategoryID]=@CategoryID");
parameters.Add(new SqlParameter("@CategoryID", CategoryID));
}
conditions.Add("isnull([BackCost],0)<[RealCost]");
string fieldList = "A.*";
string Statement = " from (select *,(select sum(RealCost) from [RoomFeeHistory] where ChargeID=[ViewGuaranteeHistoryFee].ChargeID and ChargeState=3 and [ParentHistoryID]=[ViewGuaranteeHistoryFee].HistoryID) as BackCost from [ViewGuaranteeHistoryFee])A where " + string.Join(" and ", conditions.ToArray());
ViewGuaranteeHistoryFee[] list = new ViewGuaranteeHistoryFee[] { };
list = GetList <ViewGuaranteeHistoryFee>(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 Ui.DataGrid GetWechat_SurveyQuestionGridByKeywords(string Keywords, List <int> SurveyIDList, 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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([QuestionContent] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (SurveyIDList.Count > 0)
{
conditions.Add("[SurveyID] in (" + string.Join(",", SurveyIDList.ToArray()) + ")");
}
string fieldList = "[Wechat_SurveyQuestion].* ";
string Statement = " from [Wechat_SurveyQuestion] where " + string.Join(" and ", conditions.ToArray());
Wechat_SurveyQuestion[] list = GetList <Wechat_SurveyQuestion>(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 Ui.DataGrid GetMall_UserLevelApproveDetailGridByKeywords(string keywords, int status, long startRowIndex, int pageSize)
{
long totalRows = 0;
string OrderBy = " order by [RequestTime] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (status >= 0)
{
conditions.Add("[ApproveStatus]=@ApproveStatus");
parameters.Add(new SqlParameter("@ApproveStatus", status));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([NickName] like @keywords or [PhoneNumber] like @keywords or [LevelName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select [Mall_UserLevelApprove].*,[User].NickName,[User].PhoneNumber,[Mall_UserLevel].Name as LevelName,[Mall_UserBalance].[PaymentMethod] as UserBalancePaymentMethod,[Mall_UserBalance].[BalanceValue] as UserBalanceValue,[RoomPhoneRelation].RelatePhoneNumber,[RoomPhoneRelation].RelationName from [Mall_UserLevelApprove] left join [User] on [User].UserID=[Mall_UserLevelApprove].UserID left join [Mall_UserLevel] on [Mall_UserLevel].ID=[Mall_UserLevelApprove].[ApproveUserLevelID] left join [Mall_UserBalance] on [Mall_UserBalance].ID=[Mall_UserLevelApprove].[UserBalanceID] left join [RoomPhoneRelation] on [RoomPhoneRelation].ID=[Mall_UserLevelApprove].[RoomPhoneRelationID])A where " + string.Join(" and ", conditions.ToArray());
Mall_UserLevelApproveDetail[] list = GetList <Mall_UserLevelApproveDetail>(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 Ui.DataGrid GeWechat_ServiceGridByKeywords(string Keywords, string ServiceType, 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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([ServiceContent] like @Keywords or [PhoneNumber] like @Keywords or [OpenID] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (!string.IsNullOrEmpty(ServiceType))
{
conditions.Add("[ServiceType]=@ServiceType");
parameters.Add(new SqlParameter("@ServiceType", ServiceType));
}
string fieldList = "[Wechat_Service].*,(select [FullName] from [Project] where [Project].ID=[Wechat_Service].RoomID) as RoomFullName,(select [Name] from [Project] where [Project].ID=[Wechat_Service].RoomID) as RoomName,(select [NickName] from [Wechat_User] where [Wechat_Service].OpenID=[Wechat_User].OpenID) as NickName";
string Statement = " from [Wechat_Service] where " + string.Join(" and ", conditions.ToArray());
Wechat_ServiceDetail[] list = GetList <Wechat_ServiceDetail>(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 Ui.DataGrid GeUserGridByKeywords(string Keywords, 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");
conditions.Add("([Type]=@UserType1 or [Type]=@UserType2)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.SystemUser.ToString()));
parameters.Add(new SqlParameter("@UserType2", UserTypeDefine.APPUser.ToString()));
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([LoginName] like @Keywords or [PhoneNumber] like @Keywords or [RealName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string fieldList = "[User].* ";
string Statement = " from [User] where " + string.Join(" and ", conditions.ToArray());
User[] list = new User[] { };
list = GetList <User>(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 Ui.DataGrid GetMall_BusinessGridByKeywords(string Keywords, string orderBy, long startRowIndex, int pageSize, int Status)
{
ResetParams();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (Status > 0)
{
conditions.Add("[Status]=@Status");
parameters.Add(new SqlParameter("@Status", Status));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([BusinessName] like @Keywords or [BusinessAddress] like @Keywords or [ContactName] like @Keywords or [ContactPhone] like @Keywords or [LicenseNumber] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string fieldList = "[Mall_Business].*";
string Statement = " from [Mall_Business] where " + string.Join(" and ", conditions.ToArray());
Mall_BusinessDetail[] list = GetList <Mall_BusinessDetail>(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 Ui.DataGrid GetMall_ThreadCommentDetailGridByKeyword(int ThreadID, string keywords, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ThreadID <= 0)
{
return(new Ui.DataGrid());
}
conditions.Add("([ThreadID]=@ThreadID)");
parameters.Add(new SqlParameter("@ThreadID", ThreadID));
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Comment] like @keywords or [UserName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
string OrderBy = " order by [AddTime] desc";
string fieldList = "A.*";
string Statement = " from (select *,(select NickName from [User] where UserID=Mall_ThreadComment.UserID) as NickName from [Mall_ThreadComment])A where " + string.Join(" and ", conditions.ToArray());
var list = GetList <Mall_ThreadCommentDetail>(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 Ui.DataGrid GetPaySummaryGridByKeywords(string Keywords, 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");
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("[PayName] like @Keywords");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
#endregion
string fieldList = "[PaySummary].*";
string Statement = " from [PaySummary] where " + string.Join(" and ", conditions.ToArray()) + cmd;
PaySummary[] list = new PaySummary[] { };
list = GetList <PaySummary>(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 Ui.DataGrid GetViewContractFeeHistoryGridByContractID(List <int> ContractIDList, bool IncludIsCharged, 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 (ContractIDList.Count > 0)
{
conditions.Add("[ContractID] in (" + string.Join(",", ContractIDList.ToArray()) + ")");
}
if (IncludIsCharged)
{
conditions.Add("[ChargeState] in (1,4)");
}
string fieldList = "[ViewContractFeeHistory].* ";
string Statement = " from [ViewContractFeeHistory] where " + string.Join(" and ", conditions.ToArray());
ViewContractFeeHistory[] list = new ViewContractFeeHistory[] { };
list = GetList <ViewContractFeeHistory>(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 Ui.DataGrid GetViewContractRoomListByKeywords(int ContractID, string guid, 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 (ContractID > 0)
{
conditions.Add("[ContractID]=@ContractID");
parameters.Add(new SqlParameter("@ContractID", ContractID));
}
else if (!string.IsNullOrEmpty(guid))
{
conditions.Add("[guid]=@guid");
parameters.Add(new SqlParameter("@guid", guid));
}
string fieldList = "[ViewContractRoom].*";
string Statement = " from [ViewContractRoom] where " + string.Join(" and ", conditions.ToArray());
ViewContractRoom[] list = new ViewContractRoom[] { };
list = GetList <ViewContractRoom>(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 Ui.DataGrid GetCKInCategoryGridByKeywords(string Keywords, string CategoryType, 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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([InCategoryName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (!string.IsNullOrEmpty(CategoryType))
{
conditions.Add("([CategoryType]=@CategoryType)");
parameters.Add(new SqlParameter("@CategoryType", CategoryType));
}
string fieldList = "[CKInCategory].*";
string Statement = " from [CKInCategory] where " + string.Join(" and ", conditions.ToArray());
CKInCategory[] list = new CKInCategory[] { };
list = GetList <CKInCategory>(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 Ui.DataGrid GeWechat_UserGridByKeywords(string Keywords, int Wechat_MsgID, int ChooseState, 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 (ChooseState == 1)
{
conditions.Add("isnull([IsChosen],0)>0");
}
else if (ChooseState == 2)
{
conditions.Add("isnull([IsChosen],0)=0");
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([NickName] like @Keywords or [City] like @Keywords or [Province] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
parameters.Add(new SqlParameter("@Wechat_MsgID", Wechat_MsgID));
string fieldList = "A.*";
string Statement = " from (select Wechat_User.*,(select count(1) from Wechat_MsgUser where [OpenID]=Wechat_User.OpenId and Wechat_MsgID=@Wechat_MsgID) as IsChosen from [Wechat_User])A where " + string.Join(" and ", conditions.ToArray());
Wechat_UserDetail[] list = GetList<Wechat_UserDetail>(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 Ui.DataGrid GeWechat_RentHomeDetailGridByKeywords(string Keywords, List <int> AreaIDList, List <int> BuildingIDList, 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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([AreaName] like @Keywords or [HomeName] like @Keywords or [HomeLocation] like @Keywords or [PhoneNumber] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (AreaIDList.Count > 0)
{
conditions.Add("[AreaID] in (" + string.Join(",", AreaIDList.ToArray()) + ")");
}
if (BuildingIDList.Count > 0)
{
conditions.Add("[BuildingID] in (" + string.Join(",", BuildingIDList.ToArray()) + ")");
}
string fieldList = "A.*";
string Statement = " from (select *,(select AreaName from [Wechat_RentArea] where ID=Wechat_RentHome.AreaID) as AreaName,(select BuildingName from [Wechat_RentBuilding] where ID=Wechat_RentHome.BuildingID) as BuildingName from [Wechat_RentHome])A where " + string.Join(" and ", conditions.ToArray());
Wechat_RentHomeDetail[] list = GetList <Wechat_RentHomeDetail>(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 Ui.DataGrid GetCheque_InDetailGridByKeywords(int InSummaryID, string GUID, string orderBy, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[GUID]=@GUID");
parameters.Add(new SqlParameter("@GUID", GUID));
if (InSummaryID > 0)
{
conditions.Add("[InSummaryID]=@InSummaryID");
parameters.Add(new SqlParameter("@InSummaryID", InSummaryID));
}
string fieldList = "[Cheque_InDetail].*";
string Statement = " from [Cheque_InDetail] where " + string.Join(" or ", conditions.ToArray());
Cheque_InDetail[] list = GetList <Cheque_InDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
string footertext = "select '合计' as [ProductName],'' as [ModelNumber],'' as [Unit], sum(isnull([TotalCost],0)) as TotalCost,sum(isnull([TotalTaxCost],0)) as TotalTaxCost from [Cheque_InDetail] where " + string.Join(" or ", conditions.ToArray()) + @" UNION ALL select '税价合计(大写)' as[ProductName], dbo.MoneyToCapital(sum(isnull([TotalSummaryCost],0)),0,2) as [ModelNumber],'小写' as[Unit], sum(isnull([TotalSummaryCost],0)) as TotalCost, 0 as TotalTaxCost from [Cheque_InDetail] where " + string.Join(" or ", conditions.ToArray());
dg.footer = GetList <Cheque_InDetail>(footertext, parameters).ToArray();
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetProject_ServiceTypeDelayGrid(string Keywords)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("ParentID=1");
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("Name like @Keywords");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string cmdtext = "select Project.ID,Project.Name from Project where " + string.Join(" and ", conditions) + " order by ID desc";
var list = GetList <Project>(cmdtext, parameters).ToArray();
var delayList = Project_ServiceTypeDelay.GetProject_ServiceTypeDelays().ToArray();
var results = new List <Dictionary <string, object> >();
foreach (var item in list)
{
var resultItem = new Dictionary <string, object>();
resultItem["ProjectID"] = item.ID;
resultItem["ProjectName"] = item.Name;
var myDelayItem = delayList.FirstOrDefault(p => p.ProjectID == item.ID);
resultItem["DelayHour"] = myDelayItem != null?myDelayItem.DelayHour.ToString("0") : "";
results.Add(resultItem);
}
var dg = new Ui.DataGrid();
dg.rows = results;
dg.total = results.Count;
dg.page = 1;
return(dg);
}
public static Ui.DataGrid GetMall_BalanceRuleGridByKeywords(string keywords, long startRowIndex, int pageSize, int RuleType)
{
long totalRows = 0;
string OrderBy = " order by [AddTime] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Title] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (RuleType > 0)
{
conditions.Add("[RuleType]=@RuleType");
parameters.Add(new SqlParameter("@RuleType", RuleType));
}
string fieldList = "Mall_BalanceRule.*";
string Statement = " from Mall_BalanceRule where " + string.Join(" and ", conditions.ToArray());
Mall_BalanceRule[] list = GetList <Mall_BalanceRule>(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 Ui.DataGrid GetWechat_HouseServiceDetailGridByKeywords(string Keywords, List <int> CategoryIDList, string orderBy, long startRowIndex, int pageSize, int type)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (type == 1)
{
conditions.Add("[IsWechatShow]=1");
}
if (type == 2 || type == 3)
{
conditions.Add("([IsAPPCustomerShow]=1 or [IsAPPUserShow]=1)");
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([Title] like @Keywords or [ContactPhone] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (CategoryIDList.Count > 0)
{
conditions.Add("[CategoryID] in (" + string.Join(",", CategoryIDList.ToArray()) + ")");
}
string fieldList = "A.*";
string Statement = " from (select *,(select CategoryName from [Wechat_HouseServiceCategory] where ID=Wechat_HouseService.CategoryID) as CategoryName from [Wechat_HouseService])A where " + string.Join(" and ", conditions.ToArray());
Wechat_HouseServiceDetail[] list = GetList <Wechat_HouseServiceDetail>(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 Ui.DataGrid GetViewWechatLotteryCheckerList(string keywords, int activityID, 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 (!string.IsNullOrEmpty(keywords))
{
parameters.Add(new SqlParameter("@Keywords", "%" + keywords + "%"));
conditions.Add("([NickName] like @Keywords or [RealName] like @Keywords or [LoginName] like @Keywords)");
}
if (activityID > 0)
{
parameters.Add(new SqlParameter("@ActivityID", activityID));
conditions.Add("[ActivityID]=@ActivityID");
}
var list = GetList <ViewWechatLotteryChecker>("*", "from [ViewWechatLotteryChecker] where " + string.Join(" and ", conditions.ToArray()), 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 Ui.DataGrid GetSysManualGridByKeywords(string Keywords, List <int> CategoryIDList, 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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([Title] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (CategoryIDList.Count > 0)
{
conditions.Add("[CategoryID] in (" + string.Join(",", CategoryIDList.ToArray()) + ")");
}
string fieldList = "[SysManual].*,(select CategoryName from [SysManualCategory] where ID=[SysManual].CategoryID) as CategoryName";
string Statement = " from [SysManual] where " + string.Join(" and ", conditions.ToArray());
SysManualDetail[] list = GetList <SysManualDetail>(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 Ui.DataGrid GetMall_UserJiXiaoPointDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, 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 (StartTime > DateTime.MinValue)
{
conditions.Add("[ApproveTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[ApproveTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([InfoName] like @Keywords or [CategoryName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string fieldList = "A.* ";
string Statement = " from (select *,(select LoginName from [User] where [UserID]=[Mall_UserJiXiaoPoint].UserID) as UserName from [Mall_UserJiXiaoPoint])A where " + string.Join(" and ", conditions.ToArray());
Mall_UserJiXiaoPointDetail[] list = GetList <Mall_UserJiXiaoPointDetail>(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 Ui.DataGrid GetContract_TempPriceGridByGuid(string guid, string orderBy, long startRowIndex, int pageSize, int ChargeID, int ContractID = 0)
{
ReSetParams();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
if (!string.IsNullOrEmpty(guid))
{
conditions.Add("[GUID]=@guid");
parameters.Add(new SqlParameter("@guid", guid));
}
string fieldList = "A.* ";
string Statement = " from (select [ViewContract_TempPrice].*,(select top 1 ID from [Contract_RoomCharge] where [Contract_TempPriceID]=[ViewContract_TempPrice].ID) as Contract_RoomChargeID, " + ContractID + " as ContractID from [ViewContract_TempPrice])A where " + string.Join(" and ", conditions.ToArray());
ViewContract_TempPrice[] list = GetList <ViewContract_TempPrice>(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 Ui.DataGrid GetMall_AmountRuleDetailGridByKeywords(string keywords, long startRowIndex, int pageSize, int RuleType, int AmountType)
{
long totalRows = 0;
string OrderBy = " order by [AmountType] asc,[RuleType] asc, [StartAmount] asc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (AmountType > 0)
{
conditions.Add("[AmountType]=@AmountType");
parameters.Add(new SqlParameter("@AmountType", AmountType));
}
if (RuleType > 0)
{
conditions.Add("[RuleType]=@RuleType");
parameters.Add(new SqlParameter("@RuleType", RuleType));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Title] like @keywords or [UserLevelID] in (select [ID] from [Mall_UserLevel] where [Name] like @keywords))");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select *,(select Name from [Mall_UserLevel] where [ID]=[Mall_AmountRule].[UserLevelID]) as PartnerRankName from [Mall_AmountRule])A where " + string.Join(" and ", conditions.ToArray());
Mall_AmountRuleDetail[] list = GetList <Mall_AmountRuleDetail>(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 Ui.DataGrid GeWechat_MsgGridByKeywords(string Keywords, string MsgType, string orderBy, long startRowIndex, int pageSize, int type, int msgtypeid, List <int> EqualProjectIDList = null)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
cmdlist.Add("exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and [ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
cmdlist.Add("exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and not exists (select 1 from Project where ID=Wechat_MsgProject.[ProjectID] and ID!=1))");
cmdlist.Add("not exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and [ProjectID]!=1)");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (type == 1)
{
conditions.Add("([IsWechatSend]=1 or ([IsWechatSend] is null and [IsCustomerAPPSend] is null and [IsUserAPPSend] is null))");
}
if (type == 2 || type == 3)
{
conditions.Add("([IsCustomerAPPSend]=1 or [IsUserAPPSend]=1)");
}
if (msgtypeid == 1)
{
conditions.Add("[MsgType]=@MsgType");
parameters.Add(new SqlParameter("@MsgType", Utility.EnumModel.WechatMsgType.tongzhi.ToString()));
}
if (msgtypeid == 2)
{
conditions.Add("[MsgType]=@MsgType");
parameters.Add(new SqlParameter("@MsgType", Utility.EnumModel.WechatMsgType.huodong.ToString()));
}
if (msgtypeid == 3)
{
conditions.Add("[MsgType]=@MsgType");
parameters.Add(new SqlParameter("@MsgType", Utility.EnumModel.WechatMsgType.news.ToString()));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([MsgTitle] like @Keywords or [MsgContent] like @Keywords or [MsgSummary] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (!string.IsNullOrEmpty(MsgType))
{
conditions.Add("[MsgType]=@MsgType");
parameters.Add(new SqlParameter("@MsgType", MsgType));
}
string fieldList = "[Wechat_Msg].*";
string Statement = " from [Wechat_Msg] where " + string.Join(" and ", conditions.ToArray());
Wechat_Msg[] list = GetList <Wechat_Msg>(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 Ui.DataGrid GetViewCustomerServiceInDetailGridByKeywords(DateTime StartTime, DateTime EndTime, int PayStatus, string BalanceStatus, List <int> RoomIDList, List <int> ProjectIDList, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[ServiceID]>0");
if (StartTime > DateTime.MinValue)
{
conditions.Add("[AddTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[AddTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (PayStatus == 1)
{
conditions.Add("isnull([TotalFee],0)>0");
}
else if (PayStatus == 0)
{
conditions.Add("isnull([TotalFee],0)=0");
}
if (!string.IsNullOrEmpty(BalanceStatus))
{
conditions.Add("isnull([BalanceStatus],'balanceno')=@BalanceStatus");
parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string fieldList = "[ViewCustomerServiceInDetail].*";
string Statement = " from [ViewCustomerServiceInDetail] where " + string.Join(" and ", conditions.ToArray());
ViewCustomerServiceInDetail[] list = new ViewCustomerServiceInDetail[] { };
if (canexport)
{
list = GetList <ViewCustomerServiceInDetail>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewCustomerServiceInDetail>(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 Ui.DataGrid GetChargeMeter_ProjectDetailGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int MeterCategoryID, int MeterType, int MeterChargeID, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false, bool IsWritePoint = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([MeterName] like @Keywords or [MeterNumber] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (MeterCategoryID > 0)
{
conditions.Add("[MeterCategoryID]=@MeterCategoryID");
parameters.Add(new SqlParameter("@MeterCategoryID", MeterCategoryID));
}
if (MeterType > 0)
{
conditions.Add("[MeterType]=@MeterType");
parameters.Add(new SqlParameter("@MeterType", MeterType));
}
if (MeterChargeID > 0)
{
conditions.Add("[MeterChargeID]=@MeterChargeID");
parameters.Add(new SqlParameter("@MeterChargeID", MeterChargeID));
}
string cmdcolumns = string.Empty;
ChargeMeter_ProjectDetail[] list = new ChargeMeter_ProjectDetail[] { };
if (canexport)
{
string cmdtext = "select * from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray()) + " order by " + orderBy;
list = GetList <ChargeMeter_ProjectDetail>(cmdtext, parameters).ToArray();
}
else
{
string fieldList = @"A.*";
string Statement = " from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray());
list = GetList <ChargeMeter_ProjectDetail>(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 Ui.DataGrid GetViewChequeOutSummaryGridByKeywords(string keywords, List <int> SellerList, List <int> ProductList, List <int> DepartmentList, List <int> ProjectList, DateTime StartTime, DateTime EndTime, string ChequeCode, 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 (StartTime > DateTime.MinValue)
{
conditions.Add("CONVERT(varchar(100), [ChequeTime], 23)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("CONVERT(varchar(100), [ChequeTime], 23)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(ChequeCode))
{
conditions.Add("[ChequeCode] like @ChequeCode");
parameters.Add(new SqlParameter("@ChequeCode", "%" + ChequeCode + "%"));
}
if (SellerList.Count > 0)
{
conditions.Add("([SellerID] in (" + string.Join(",", SellerList.ToArray()) + "))");
}
if (ProductList.Count > 0)
{
conditions.Add("[ProductID] in (" + string.Join(",", ProductList.ToArray()) + ")");
}
if (DepartmentList.Count > 0)
{
conditions.Add("([DepartmentID] in (" + string.Join(",", DepartmentList.ToArray()) + "))");
}
if (ProjectList.Count > 0)
{
conditions.Add("([ProjectID] in (" + string.Join(",", ProjectList.ToArray()) + "))");
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([DepartmentName] like @keywords or [ProjectName] like @keywords or [SellerName] like @keywords or [SellerTaxNumber] like @keywords or [SellerAddressPhone] like @keywords or [SellerBankAccount] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
string fieldList = "[ViewChequeOutSummary].*,(select sum(isnull([TotalSummaryCost],0)) from [Cheque_OutDetail] where [OutSummaryID]=[ViewChequeOutSummary].ID) as [ChequeTotalCost]";
string Statement = " from [ViewChequeOutSummary] where " + string.Join(" and ", conditions.ToArray());
ViewChequeOutSummary[] list = GetList <ViewChequeOutSummary>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
string footerttext = "select sum(isnull([TotalCost],0)) as TotalCost,sum(isnull([TotalTaxCost],0)) as TotalTaxCost,sum(isnull([TotalCount],0)) as TotalCount,(select sum(isnull([TotalSummaryCost],0)) from [Cheque_OutDetail] where [Cheque_OutDetail].OutSummaryID in (select ID from [ViewChequeOutSummary] where " + string.Join(" and ", conditions.ToArray()) + ")) as ChequeTotalCost from [ViewChequeOutSummary] where " + string.Join(" and ", conditions.ToArray());
dg.footer = GetList <ViewChequeOutSummary>(footerttext, parameters).ToArray();
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
