public static PaySummaryAnalysis[] GetPaySummaryAnalysisList(List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int UserID = 0)
{
List <string> conditions = new List <string>();
List <SqlParameter> parameters = new List <SqlParameter>();
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 (StartTime > DateTime.MinValue)
{
conditions.Add("[PayTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[PayTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string cmdtext = "select A.* from (select ProjectID,PaySummaryID,sum(PayMoney) as TotalCost from PayService where " + string.Join(" and ", conditions.ToArray()) + " group by ProjectID, PaySummaryID)A where A.TotalCost>0";
PaySummaryAnalysis[] list = GetList <PaySummaryAnalysis>(cmdtext, parameters).ToArray();
return(list);
}
public static RoomFeeHistory[] GetRoomFeeHistoryListByRoomIDList(List <int> RoomIDList, int ChargeID = 0, List <int> ChargeFeeID = null, List <int> ProjectIDList = null, int UserID = 0, int ChargeFeeType = 0, bool OnlyChargeFee = false)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
if (OnlyChargeFee)
{
conditions.Add("ChargeState in (1,4)");
}
if (ChargeFeeType > 0)
{
conditions.Add("[ChargeID] in (select [ID] from [ChargeSummary] where [ChargeFeeType]=@ChargeFeeType)");
parameters.Add(new SqlParameter("@ChargeFeeType", ChargeFeeType));
}
if (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
if (ChargeFeeID != null && ChargeFeeID.Count > 0)
{
conditions.Add("[ChargeFeeID] in (" + string.Join(",", ChargeFeeID.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "[RoomID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: true, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
return(GetList <RoomFeeHistory>("select * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static PrintRoomFeeHistory[] GetPrintRoomFeeHistoryListByRoomProjectIDList(DateTime StartTime, DateTime EndTime, List <int> ProjectIDList, List <int> RoomIDList, int UserID = 0, List <int> ChargeStateList = null)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ChargeStateList != null && ChargeStateList.Count > 0)
{
conditions.Add("[ChargeState] in (" + string.Join(",", ChargeStateList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
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 (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[ChargeTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[ChargeTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string cmdtext = "select * from [PrintRoomFeeHistory] where [ID] in (select [PrintID] from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + ")";
return(GetList <PrintRoomFeeHistory>(cmdtext, parameters).ToArray());
}
public static void GetChargeMeter_ProjectDetailRoomIDList(int RoomID, out int PreRoomID, out int NextRoomID, out int CurrentRoomID, string keywords = "", List <int> ALLProjectIDList = null, int UserID = 0, int ProjectID = 0)
{
PreRoomID = 0;
NextRoomID = 0;
CurrentRoomID = 0;
var parameters = new List <SqlParameter>();
var conditions = new List <string>();
conditions.Add("[IsAPPWriteEnable]=1");
conditions.Add("[MeterType]=1");
var list = new ChargeMeter_Project[] { };
if (ALLProjectIDList != null && ALLProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ALLProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Project].[Name] like @keywords or [Project].[FullName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (ProjectID > 0)
{
conditions.Add("([Project].[ID]=@ProjectID or [Project].[AllParentID] like @AllParentID)");
parameters.Add(new SqlParameter("@ProjectID", ProjectID));
parameters.Add(new SqlParameter("@AllParentID", "%," + ProjectID + ",%"));
}
list = GetList <ChargeMeter_Project>("select [ChargeMeter_Project].[ProjectID] from [ChargeMeter_Project] left join [Project] on [Project].ID=[ChargeMeter_Project].ProjectID where " + string.Join(" and ", conditions.ToArray()) + " order by [Project].DefaultOrder asc", parameters).ToArray();
if (list.Length == 0)
{
return;
}
var ProjectIDList = list.Select(p => p.ProjectID).Distinct().ToArray();
if (RoomID > 0)
{
for (int i = 0; i < ProjectIDList.Length; i++)
{
if (RoomID == ProjectIDList[i])
{
CurrentRoomID = RoomID;
if (i > 0)
{
PreRoomID = ProjectIDList[i - 1];
}
if (i < ProjectIDList.Length - 1)
{
NextRoomID = ProjectIDList[i + 1];
}
}
}
return;
}
CurrentRoomID = ProjectIDList[0];
if (ProjectIDList.Length > 1)
{
NextRoomID = ProjectIDList[1];
}
}
public static PaySummaryDetail[] GetPaySummaryDetailListByRoomID(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartTime, DateTime EndTime, int UserID)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (StartTime > DateTime.MinValue)
{
conditions.Add("[PayTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[PayTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string Statement = @" select*,A.TotalMoney from PaySummary left join
(
select PaySummaryID, sum(isnull(PayMoney,0)) as TotalMoney from PayService where " + string.Join(" and ", conditions) + @" group by PaySummaryID
)A on A.PaySummaryID=PaySummary.ID";
PaySummaryDetail[] list = GetList <PaySummaryDetail>(Statement, parameters).ToArray();
return(list);
}
public static ViewImportFeeDetail2[] GetViewImportFeeDetail2ListByBiaoID(List <int> RoomIDList, List <int> ProjectIDList, int ChargeID = 0, DateTime?WriteDate = null, int BiaoID = 0, string BiaoCategory = "", string BiaoName = "", string BiaoGuiGe = "", int UserID = 0)
{
ReSetParams();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
ViewImportFeeDetail2[] list = new ViewImportFeeDetail2[] { };
if (ChargeID > 0)
{
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
conditions.Add("[ChargeID]=@ChargeID");
}
if (BiaoID > 0)
{
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
conditions.Add("[ChargeBiaoID]=@BiaoID");
}
if (!string.IsNullOrEmpty(BiaoCategory))
{
parameters.Add(new SqlParameter("@BiaoCategory", BiaoCategory));
conditions.Add("[ImportBiaoCategory]=@BiaoCategory");
}
if (!string.IsNullOrEmpty(BiaoName))
{
parameters.Add(new SqlParameter("@BiaoName", BiaoName));
conditions.Add("[ImportBiaoName]=@BiaoName");
}
if (!string.IsNullOrEmpty(BiaoGuiGe))
{
parameters.Add(new SqlParameter("@BiaoGuiGe", BiaoGuiGe));
conditions.Add("[ImportBiaoGuiGe]=@BiaoGuiGe");
}
//if (WriteDate.HasValue)
//{
// parameters.Add(new SqlParameter("@WriteDate", WriteDate));
// conditions.Add("[WriteDate]=(select top 1 WriteDate from [ViewImportFee] where WriteDate<@WriteDate and " + string.Join(" and ", conditions.ToArray()) + " order by WriteDate desc)");
//}
list = GetList <ViewImportFeeDetail2>("select * from [ViewImportFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
if (list.Length > 0 && WriteDate.HasValue)
{
var first_fee = list.Where(p => p.WriteDate < WriteDate).OrderByDescending(p => p.WriteDate).FirstOrDefault();
if (first_fee != null)
{
list = list.Where(p => p.WriteDate == first_fee.WriteDate).ToArray();
}
}
return(list);
}
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 CustomerService[] GetCustomerServiceListByEqualProjectID(List <int> RoomIDList, int ServiceStatus, out int TotalCount, int UserID = 0, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, bool GetCount = false)
{
TotalCount = 0;
ResetCache();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ServiceStatus > -1)
{
if (ServiceStatus == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
else
{
conditions.Add("[ServiceStatus]=@ServiceStatus");
}
parameters.Add(new SqlParameter("@ServiceStatus", ServiceStatus));
}
if (GetCount)
{
using (SqlHelper helper = new SqlHelper())
{
var result = helper.ExecuteScalar("select count(1) from [CustomerService] where " + string.Join(" and ", conditions.ToArray()), CommandType.Text, parameters);
if (result != null)
{
int.TryParse(result.ToString(), out TotalCount);
}
}
return(new CustomerService[] { });
}
string cmdtext = "select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray());
CustomerService[] list = GetList <CustomerService>(cmdtext, parameters).ToArray();
return(list);
}
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 GetViewRoomSourceFeeGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int ChargeID, bool IsRoomFee, bool IsContractFee, string RoomProperty, string orderBy, long startRowIndex, int pageSize, int UserID = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[IsStart]=1");
if (!string.IsNullOrEmpty(RoomProperty))
{
conditions.Add("[RoomID] in (select [RoomID] from [RoomBasic] where isnull([RoomProperty],'')=@RoomProperty)");
parameters.Add(new SqlParameter("@RoomProperty", RoomProperty));
}
if (IsRoomFee && !IsContractFee)
{
conditions.Add("isnull([ContractID],0)=0");
}
if (IsContractFee && !IsRoomFee)
{
conditions.Add("isnull([ContractID],0)>0");
}
conditions.Add("([RoomID] in (select [RoomID] from [RoomBasic] where isnull([IsLocked],0)=0) or not exists (select * from [RoomBasic] where isnull([IsLocked],0)=0 and [RoomID]=[ViewRoomSourceFee].[RoomID]))");
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([Name] like @Keywords or [FullName] like @Keywords or [ChargeName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
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 (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
string fieldList = "[ViewRoomSourceFee].*,(select top 1 [RelationName] from [RoomPhoneRelation] where isnull([IsDefault],0)=1 and [RoomID]=[ViewRoomSourceFee].[RoomID]) as [RelationName]";
string Statement = " from [ViewRoomSourceFee] where " + string.Join(" and ", conditions.ToArray());
ViewRoomSourceFee[] list = new ViewRoomSourceFee[] { };
list = GetList <ViewRoomSourceFee>(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 GetPaySummaryAnalysisGrid(List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, int UserID = 0, bool canexport = false)
{
List <string> conditions = new List <string>();
List <SqlParameter> parameters = new List <SqlParameter>();
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 (StartTime > DateTime.MinValue)
{
conditions.Add("[PayTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[PayTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
long totalRows = 0;
string fieldList = "B.*";
string Statement = " from (select A.*,Project.FullName,Project.Name,Project.DefaultOrder from (select ProjectID,ProjectName,RoomName,sum(PayMoney) as TotalCost from PayService where [ProjectID]>0 and " + string.Join(" and ", conditions.ToArray()) + " group by ProjectID,ProjectName,RoomName)A left join Project on Project.ID=A.ProjectID )B where B.TotalCost>0";
PaySummaryAnalysis[] list = new PaySummaryAnalysis[] { };
if (canexport)
{
list = GetList <PaySummaryAnalysis>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <PaySummaryAnalysis>(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 Project_Biao[] GetProject_BiaoListByBiaoID(List <int> RoomIDList, List <int> ProjectIDList, int ChargeID = 0, int BiaoID = 0, string BiaoCategory = "", string BiaoName = "", string BiaoGuiGe = "", int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
Project_Biao[] list = new Project_Biao[] { };
if (ChargeID > 0)
{
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
conditions.Add("[BiaoID] in (select ChargeBiaoID from ChargeSummary_Biao where ChargeID=@ChargeID)");
}
if (BiaoID > 0)
{
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
conditions.Add("[BiaoID]=@BiaoID");
}
if (!string.IsNullOrEmpty(BiaoCategory))
{
parameters.Add(new SqlParameter("@BiaoCategory", BiaoCategory));
conditions.Add("[BiaoCategory]=@BiaoCategory");
}
if (!string.IsNullOrEmpty(BiaoName))
{
parameters.Add(new SqlParameter("@BiaoName", BiaoName));
conditions.Add("[BiaoName]=@BiaoName");
}
if (!string.IsNullOrEmpty(BiaoGuiGe))
{
parameters.Add(new SqlParameter("@BiaoGuiGe", BiaoGuiGe));
conditions.Add("[BiaoGuiGe]=@BiaoGuiGe");
}
list = GetList <Project_Biao>("select * from [Project_Biao] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
return(list);
}
public static Contract_Room[] GetContract_RoomListByRoomIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "[RoomID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
return(GetList <Contract_Room>("select * from [Contract_Room] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static ViewContractChargeSummary[] GetViewContractChargeSummaryByProjectIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
ReSetParams();
List <string> conditions = new List <string>();
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
List <SqlParameter> parameters = new List <SqlParameter>();
return(GetList <ViewContractChargeSummary>("select * from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static PayService[] GetPayServiceList(List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int UserID = 0, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
conditions.Add("isnull([Status],3)=3");
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[PayTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[PayTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string Statement = @" select * from PayService where " + string.Join(" and ", conditions);
PayService[] list = GetList <PayService>(Statement, parameters).ToArray();
return(list);
}
public static ChargeMeter_ProjectDetail[] GetChargeMeter_ProjectDetailListByRoomID(int RoomID, List <int> ProjectIDList = null, int MeterType = 1, int UserID = 0, int ProjectID = 0, string keywords = "")
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[IsAPPWriteEnable]=1");
if (RoomID > 0)
{
conditions.Add("[ProjectID]=@RoomID");
parameters.Add(new SqlParameter("@RoomID", RoomID));
}
if (ProjectID > 0)
{
conditions.Add("([ProjectID]=@ProjectID or [AllParentID] like @AllParentID)");
parameters.Add(new SqlParameter("@ProjectID", ProjectID));
parameters.Add(new SqlParameter("@AllParentID", "%," + ProjectID + ",%"));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([ProjectName] like @keywords or [FullName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (MeterType == 1)
{
conditions.Add("[MeterType]=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
}
else
{
conditions.Add("[MeterType]=2");
}
string cmdtext = "select * from (" + CommSqlText + ") as A where " + string.Join(" and ", conditions.ToArray());
ChargeMeter_ProjectDetail[] list = GetList <ChargeMeter_ProjectDetail>(cmdtext, parameters).ToArray();
return(list);
}
public static Wechat_Contact[] GetWechat_ContactListByMsgPhoneType(string PhoneType, List <int> ProjectIDList = null, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> conditions2 = new List <string>();
conditions.Add("1=1");
conditions2.Add("1=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
if (EqualIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(EqualIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("([ID] in (select [WechatContactID] from [Wechat_ContactProject] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
}
if (!string.IsNullOrEmpty(PhoneType))
{
conditions.Add("[PhoneType]=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
conditions2.Add("[PhoneType]=@PhoneType");
}
else
{
conditions.Add("isnull([PhoneType],'')!=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
conditions2.Add("isnull([PhoneType],'')!=@PhoneType");
}
string cmdtext = "select * from [Wechat_Contact] where " + string.Join(" and ", conditions.ToArray()) + " order by ID desc";
var list = GetList <Wechat_Contact>(cmdtext, parameters).ToArray();
if (list.Length > 0)
{
return(list);
}
return(GetList <Wechat_Contact>("select * from [Wechat_Contact] where " + string.Join(" and ", conditions2.ToArray()) + " order by ID desc", parameters).ToArray());
}
public static RoomFee[] GetRoomFeeListByParams(List <int> ProjectIDList, List <int> RoomIDList, List <int> ChargeIDList, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ChargeIDList.Count > 0)
{
conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
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()) + ")");
}
return(GetList <RoomFee>("select * from [RoomFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static CustomerService[] GetCustomerServiceListByRoomIDList(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0, int Status = 0)
{
ResetCache();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", IsContractFee: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (Status == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
return(GetList <CustomerService>("select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static int GetALLWaringingContractsCount(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
List <string> conditions = new List <string>();
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: false, UserID: UserID);
conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]");
conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@ContractStatus", Utility.EnumModel.ContractStatus.tongguo.ToString()));
conditions.Add("[ContractStatus]=@ContractStatus");
var list = GetList <ContractDetail>("select [ID],[ContractStatus],[RentEndTime] from [Contract] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
ContractDetail.SetContractWarningCount(list);
list = list.Where(p => p.IsRentExpire).ToArray();
return(list.Length);
}
public static ViewCustomerService[] GetViewCustomerServiceListByStatus(int status, int UserID, long startRowIndex, int pageSize, out long totalRows, bool onlybaoshi = false, List <int> ProjectIDList = null)
{
ResetCache();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
string orderby = " order by [AddTime] desc";
conditions.Add("1=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
if (UserID > -1)
{
cmdlist.Add("[ProjectID]=0");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (status >= 0)
{
conditions.Add("[ServiceStatus]=@ServiceStatus");
parameters.Add(new SqlParameter("@ServiceStatus", status));
if (status == 1)
{
orderby = " order by [BanJieTime] desc";
}
}
if (UserID > 0)
{
conditions.Add("([DepartmentID] in (select DepartmentID from [UserDepartment] where [UserID]=@UserID) or DepartmentID is null or DepartmentID=0)");
if (status != 3)
{
conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + UserID.ToString() + ",%'");
}
parameters.Add(new SqlParameter("@UserID", UserID));
}
if (onlybaoshi)
{
string cmdwhere = string.Empty;
if (UserID > 0)
{
cmdwhere += " and [AddUserID]=@AddUserID";
parameters.Add(new SqlParameter("@AddUserID", UserID));
}
conditions.Add("ID in (select ID from [CustomerService] where [WechatServiceID] in (select ID from [Wechat_Service] where 1=1 " + cmdwhere + "))");
}
string fieldList = "[ViewCustomerService].*";
string Statement = " from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray());
var list = GetList <ViewCustomerService>(fieldList, Statement, parameters, orderby, startRowIndex, pageSize, out totalRows).ToArray();
if (UserID == -1)
{
var UserList = User.GetAPPUserList();
var UserIDList = UserList.Select(p => p.UserID).ToList();
list = list.Where(p =>
{
if (string.IsNullOrEmpty(p.ServiceAccpetManID))
{
return(true);
}
string[] ServiceAccpetManIDArray = p.ServiceAccpetManID.Replace("[", ",").Replace("]", ",").Split(',');
foreach (var MyUserID in UserIDList)
{
foreach (var item in ServiceAccpetManIDArray)
{
if (MyUserID.ToString().Equals(item))
{
return(false);
}
}
}
return(true);
}).ToArray();
}
return(list);
}
public static decimal GetSumWeiShuByChargeState(DateTime StartTime, DateTime EndTime, string ChargeMan, List <int> ChargeStateList, List <int> ProjectIDList, List <int> RoomIDList, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
decimal total = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (HistoryIDList != null && HistoryIDList.Count > 0)
{
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID);
conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id in (select [HistoryID] from [RoomFeeHistory] where [PrintID]=[PrintRoomFeeHistory].[ID]) and [UserID]=" + UserID + ")");
}
else if (HistoryIDList.Count > 0)
{
conditions.Add("[PrintRoomFeeHistory].ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))");
}
}
if (RoomFeeOrderID > 0)
{
conditions.Add("isnull([RoomFeeOrderID],0)=@RoomFeeOrderID");
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
conditions.Add("isnull([RoomFeeOrderID],0)=0");
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where (" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeTime]>@StartTime)");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeTime]<@EndTime)");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
#region 收款人查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(ChargeMan))
{
string[] keywords = ChargeMan.Trim().Split(',');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and ([ChargeMan] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ChargeMan] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// conditions.Add("[ChargeMan]=@ChargeMan");
// parameters.Add(new SqlParameter("@ChargeMan", ChargeMan));
//}
if (ChargeStateList.Count > 0)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeState] in (" + string.Join(",", ChargeStateList.ToArray()) + "))");
}
string cmdtext = "select sum(WeiShuMore)-sum(WeiShuConsume) from [PrintRoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmd;
using (SqlHelper helper = new SqlHelper())
{
object obj = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters);
if (obj != null)
{
decimal.TryParse(obj.ToString(), out total);
}
}
return(total);
}
public static Ui.DataGrid GetViewPayServiceGridByKeywords(int PaySummaryID, string Keywords, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, int UserID = 0, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, bool canexport = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (PaySummaryID > 0)
{
conditions.Add("[PaySummaryID]=@PaySummaryID");
parameters.Add(new SqlParameter("@PaySummaryID", PaySummaryID));
}
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
string[] keywords = Keywords.Trim().Split(' ');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and ([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ProjectName] like '%" + keywords[i] + "%' or [PayType] like '%" + keywords[i] + "%' or [AccountType] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%' or [Remark] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[PayTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[PayTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string fieldList = "[ViewPayService].*";
string Statement = " from [ViewPayService] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewPayService[] list = new ViewPayService[] { };
if (canexport)
{
list = GetList <ViewPayService>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewPayService>(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 GetViewZhuangXiuGridByKeywords(string Keywords, string Status, List <int> RoomIDList, List <int> ProjectIDList, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, int UserID = 0)
{
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))
{
string[] keywords = Keywords.Trim().Split(' ');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and ([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ApplicationMa] like '%" + keywords[i] + "%' or [RoomName] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (!string.IsNullOrEmpty(Status))
{
conditions.Add("[Status]=@Status");
parameters.Add(new SqlParameter("@Status", Status));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
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 (StartTime > DateTime.MinValue)
{
if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpiyes.ToString()) || Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpino.ToString()))
{
conditions.Add("[ApproveTime]>=@StartTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.zhuangxiu.ToString()))
{
conditions.Add("[ConfirmZXTime]>=@StartTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.yanshou.ToString()))
{
conditions.Add("[YanShouTime]>=@StartTime");
}
else
{
conditions.Add("[AddTime]>=@StartTime");
}
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpiyes.ToString()) || Status.Equals(Utility.EnumModel.ZhuangXiuStatus.shenpino.ToString()))
{
conditions.Add("[ApproveTime]<=@EndTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.zhuangxiu.ToString()))
{
conditions.Add("[ConfirmZXTime]<=@EndTime");
}
else if (Status.Equals(Utility.EnumModel.ZhuangXiuStatus.yanshou.ToString()))
{
conditions.Add("[YanShouTime]<=@EndTime");
}
else
{
conditions.Add("[AddTime]<=@EndTime");
}
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string fieldList = "[ViewZhuangXiu].*";
string Statement = " from [ViewZhuangXiu] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewZhuangXiu[] list = GetList <ViewZhuangXiu>(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_RoomOwnerBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int BalanceStatus, long startRowIndex, int pageSize, List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
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 (RoomIDList.Count > 0)
{
conditions.Add("[ProjectID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (BalanceStatus == 23)
{
conditions.Add("[BalanceStatus] in (2,3)");
}
else if (BalanceStatus > 0)
{
conditions.Add("[BalanceStatus]=@BalanceStatus");
parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus));
}
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 (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([RoomOwnerName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select [Mall_RoomOwnerBalance].*,[RoomPhoneRelation].RelationName as RoomOwnerName,[Project].FullName,[Project].Name as RoomName from [Mall_RoomOwnerBalance] left join [RoomPhoneRelation] on [RoomPhoneRelation].ID=[Mall_RoomOwnerBalance].RoomPhoneRelationID left join [Project] on [Project].ID=[RoomPhoneRelation].RoomID)A where " + string.Join(" and ", conditions.ToArray());
Mall_RoomOwnerBalanceDetail[] list = GetList <Mall_RoomOwnerBalanceDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
var rule_list = Mall_BalanceRule.GetMall_BalanceRules().ToArray();
foreach (var item in list)
{
var my_rule = rule_list.FirstOrDefault(p => p.ID == item.BalanceRuleID);
if (my_rule != null)
{
item.BalanceRuleName = my_rule.Title;
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetViewContractChargeSummaryGrid(int ContractID, string guid, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, bool IsLinShi = false, int UserID = 0, List <int> RoomIDList = null, List <int> ProjectIDList = null, string keywords = "", string ContractStatus = "", bool IsWarning = false, bool includefooter = false, DateTime?ReadyStartTime = null, DateTime?ReadyEndTime = null, int FeeStatus = 0, List <int> ChargeIDList = null, bool canexport = false)
{
ReSetParams();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([ChargeID],0)!=0");
if (ChargeIDList != null && ChargeIDList.Count > 0)
{
conditions.Add("[ChargeID] in (" + string.Join(",", ChargeIDList.ToArray()) + ")");
}
if (ReadyStartTime.HasValue)
{
DateTime _ReadyStartTime = Convert.ToDateTime(ReadyStartTime);
if (_ReadyStartTime > DateTime.MinValue)
{
conditions.Add("([ReadyChargeTime]>=@ReadyStartTime or [ReadyChargeTime] is null)");
parameters.Add(new SqlParameter("@ReadyStartTime", ReadyStartTime));
}
}
if (ReadyEndTime.HasValue)
{
DateTime _ReadyEndTime = Convert.ToDateTime(ReadyEndTime);
if (_ReadyEndTime > DateTime.MinValue)
{
conditions.Add("([ReadyChargeTime]<=@ReadyEndTime or [ReadyChargeTime] is null)");
parameters.Add(new SqlParameter("@ReadyEndTime", ReadyEndTime));
}
}
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList != null && RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: true);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("(ContractID in (select ID from Contract where [RentName] like @keywords or [ContractNo] like @keywords or [ContractName] like @keywords) or [Name] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (!string.IsNullOrEmpty(ContractStatus))
{
conditions.Add("ContractID in (select ID from Contract where [ContractStatus]=@ContractStatus)");
parameters.Add(new SqlParameter("@ContractStatus", ContractStatus));
}
if (IsWarning)
{
conditions.Add("([ReadyChargeTime]<=@ReadyChargeTime or [ReadyChargeTime] is null)");
parameters.Add(new SqlParameter("@ReadyChargeTime", DateTime.Now));
}
if (IsLinShi)
{
conditions.Add("[ChargeID] in (select ID from ChargeSummary where [FeeType]=4)");
}
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 = "[ViewContractChargeSummary].*";
string Statement = " from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray());
ViewContractChargeSummary[] list = new ViewContractChargeSummary[] { };
if (FeeStatus == 0)
{
if (canexport)
{
list = GetList <ViewContractChargeSummary>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <ViewContractChargeSummary>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
if (list.Length > 0)
{
var RoomFeeIDList = list.Where(p => p.RoomFeeID > 0).Select(p => p.RoomFeeID).ToList();
var ContractChargeIDList = list.Select(p => p.ID).ToList();
ViewRoomFeeHistoryDetailList = ViewRoomFeeHistoryDetail.GetViewRoomFeeHistoryDetailList(new List <int>(), StartTime, EndTime, StartTime, EndTime, new List <int>(), RoomFeeIDList, ContractChargeIDList, UserID: UserID, RequireOrderBy: false).Where(p => p.MonthTotalCost > 0).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
else
{
list = GetList <ViewContractChargeSummary>("select * from [ViewContractChargeSummary] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
if (list.Length > 0)
{
var RoomFeeIDList = list.Where(p => p.RoomFeeID > 0).Select(p => p.RoomFeeID).ToList();
var ContractChargeIDList = list.Select(p => p.ID).ToList();
ViewRoomFeeHistoryDetailList = ViewRoomFeeHistoryDetail.GetViewRoomFeeHistoryDetailList(new List <int>(), StartTime, EndTime, StartTime, EndTime, new List <int>(), RoomFeeIDList, ContractChargeIDList, UserID: UserID, RequireOrderBy: false).Where(p => p.MonthTotalCost > 0).ToArray();
}
if (FeeStatus == 1)
{
list = list.Where(p => p.CalcualtePayCost == 0 || p.CalcualteRestCost > 0).ToArray();
}
else if (FeeStatus == 2)
{
list = list.Where(p => p.CalcualtePayCost > 0 && p.CalcualteRestCost == 0).ToArray();
}
totalRows = list.Length;
int StartRowIndex = Convert.ToInt32(startRowIndex);
var finallist = list.Skip(StartRowIndex).Take(pageSize).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = finallist;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
}
public static Ui.DataGrid GetHistorySummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, string ChargeMan, List <int> ChargeSummaryIDList, List <int> ChargeTypeIDList, List <int> ChargeStateList, int RoomFeeOrderID, bool IsRoomFeeSearch, string OpenID, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
List <string> conditions2 = new List <string>();
conditions2.Add("1=1");
List <string> conditions3 = new List <string>();
conditions3.Add("1=1");
if (HistoryIDList != null && HistoryIDList.Count > 0)
{
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID);
conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")");
conditions2.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id in (select HistoryID from RoomFeeHistory where PrintID=prh.ID) and UserID=" + UserID + ")");
}
else if (HistoryIDList.Count > 0)
{
conditions.Add("rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")");
conditions2.Add("prh.ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))");
}
}
if (!string.IsNullOrEmpty(OpenID))
{
conditions.Add("rfh.[RoomID] in (select [ProjectID] from [WechatUser_Project] where [OpenID]=@OpenID)");
parameters.Add(new SqlParameter("@OpenID", OpenID));
}
if (ChargeStateList.Count > 0)
{
if (ChargeStateList.Contains(1) || ChargeStateList.Contains(4))
{
conditions2.Add("prh.[IsCancel]=0");
}
conditions2.Add("prh.ID in (select PrintID from RoomFeeHistory where ChargeState in (" + string.Join(",", ChargeStateList.ToArray()) + "))");
}
if (RoomFeeOrderID > 0)
{
conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=@RoomFeeOrderID");
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=0");
conditions2.Add("prh.[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)");
if (StartChargeTime > DateTime.MinValue)
{
conditions.Add("rfh.[ChargeTime]>=@StartChargeTime");
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
conditions.Add("rfh.[ChargeTime]<=@EndChargeTime");
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
else
{
if (StartChargeTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)>=@StartChargeTime");
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)<=@EndChargeTime");
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "rfh.[RoomID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "rfh.[RoomID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
#region 收款人查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(ChargeMan))
{
string[] keywords = ChargeMan.Trim().Split(',');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and (prh.[ChargeMan] like '%" + keywords[i] + "%')";
}
else
{
cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and ((prh.[ChargeMan] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// conditions2.Add("prh.[ChargeMan] like '%" + ChargeMan + "%'");
//}
if (ChargeSummaryIDList.Count > 0)
{
conditions.Add("rfh.[ChargeID] in (" + string.Join(",", ChargeSummaryIDList.ToArray()) + ")");
}
if (ChargeTypeIDList.Count > 0)
{
conditions2.Add("(prh.[ChageType1] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ") or prh.[ChageType2] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + "))");
conditions3.Add("cmt.[ChargeTypeID] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ")");
}
var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList();
var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType1 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @")
Group by prh.ChageType1", parameters).ToArray();
var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType2 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @")
Group by prh.ChageType2", parameters).ToArray();
decimal RealCost1 = 0;
decimal RealCost2 = 0;
var finalList = new List <ChargeMoneyTypeDetails>();
foreach (var item in type_list)
{
RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
item.RealCost = RealCost1 + RealCost2;
finalList.Add(item);
}
}
var TypeIDList = type_list.Select(p => p.ChargeTypeID).ToList();
RealCost1 = history1.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
var item = new ChargeMoneyTypeDetails();
item.ChargeTypeID = 0;
item.ChargeTypeName = "其他";
item.RealCost = (RealCost1 + RealCost2);
finalList.Add(item);
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = finalList.ToArray();
dg.total = finalList.Count;
dg.page = 1;
return(dg);
}
public static Ui.DataGrid GetDepositSummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, int CompanyID, string ChargeMan, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
string cmdNow = string.Empty;
string cmdWhere = string.Empty;
if (HistoryIDList != null && HistoryIDList.Count > 0)
{
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID);
cmdNow += " and EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")";
}
else if (HistoryIDList.Count > 0)
{
cmdNow += " and rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")";
}
}
if (RoomFeeOrderID > 0)
{
cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=@RoomFeeOrderID)";
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=0)";
cmdNow += " and ([ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)";
if (StartChargeTime > DateTime.MinValue)
{
cmdNow += " and [ChargeTime]>=@StartChargeTime";
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
cmdNow += " and [ChargeTime]<=@EndChargeTime";
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
else
{
if (StartChargeTime > DateTime.MinValue)
{
cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)>=@StartChargeTime";
parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime));
}
if (EndChargeTime > DateTime.MinValue)
{
cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)<=@EndChargeTime";
parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime));
}
}
#region 收款人查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(ChargeMan))
{
string[] keywords = ChargeMan.Trim().Split(',');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and ([ChargeMan] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([ChargeMan] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([ChargeMan] like '%" + keywords[i] + "%') or ";
}
}
}
cmdNow += cmd;
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// cmdNow += " and [ChargeMan]=@ChargeMan";
// parameters.Add(new SqlParameter("@ChargeMan", ChargeMan));
//}
if (CompanyID != 1 && CompanyID > 0)
{
cmdWhere += " and [CompanyID]=@CompanyID";
parameters.Add(new SqlParameter("@CompanyID", CompanyID));
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IsContractFee: true);
cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")";
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: true, UserID: UserID);
cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")";
}
var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList();
var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealMoneyCost2,0)) as RealCost,ChageType2 as ChargeTypeID from PrintRoomFeeHistory
where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @")
group by ChageType2", parameters).ToArray();
var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(RealMoneyCost2,0)) as RealCost,ChageType1 as ChargeTypeID from PrintRoomFeeHistory
where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @")
group by ChageType1", parameters).ToArray();
decimal RealCost1 = 0;
decimal RealCost2 = 0;
var finalList = new List <ChargeMoneyTypeDetails>();
foreach (var item in type_list)
{
RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
item.RealCost = RealCost1 + RealCost2;
finalList.Add(item);
}
}
var ChargeTypeIDList = type_list.Select(p => p.ChargeTypeID).ToList();
RealCost1 = history1.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
RealCost2 = history2.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost);
if ((RealCost1 + RealCost2) > 0)
{
var item = new ChargeMoneyTypeDetails();
item.ChargeTypeID = 0;
item.ChargeTypeName = "其他";
item.RealCost = (RealCost1 + RealCost2);
finalList.Add(item);
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = finalList.ToArray();
dg.total = finalList.Count;
dg.page = 1;
return(dg);
}
public static Ui.DataGrid GetCustomerServiceGridByKeywords(string Keywords, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, int ServiceStatus, int PayStatus, int ServiceAccpetManID, string orderBy, long startRowIndex, int pageSize, int UserID, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, int ServiceType = 0, int ServiceRange = 1, List <int> PublicProjectIDList = null, bool canexport = false)
{
ResetCache();
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> cmdlist = new List <string>();
conditions.Add("1=1");
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
string[] keywords = Keywords.Trim().Split(' ');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%')";
}
else
{
cmd += " (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and ((isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " (isnull([ServiceNumber],'') like '%" + keywords[i] + "%' or [AddCustomerName] like '%" + keywords[i] + "%' or [AddCallPhone] like '%" + keywords[i] + "%' or [ServiceAccpetMan] like '%" + keywords[i] + "%' or [ServiceFullName] like '%" + keywords[i] + "%' or [ServiceContent] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (ServiceType == 2)
{
conditions.Add("isnull([IsSuggestion],0)=1");
}
else
{
conditions.Add("isnull([IsSuggestion],0)=0");
cmdlist = new List <string>();
if (ServiceRange == 1)
{
conditions.Add("isnull([PublicProjectID],0)=0");
}
if (ServiceRange == 2)
{
conditions.Add("isnull([PublicProjectID],0)>0");
conditions.Add("isnull([ProjectID],0)=0");
}
if (InProjectIDList != null && InProjectIDList.Count > 0)
{
cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(InProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
}
if (EqualProjectIDList != null && EqualProjectIDList.Count > 0)
{
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualProjectIDList.ToArray()) + "))");
}
if (PublicProjectIDList != null && PublicProjectIDList.Count > 0)
{
cmdlist.Add("[PublicProjectID] in (" + string.Join(",", PublicProjectIDList.ToArray()) + ")");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ServiceAccpetManID > 0)
{
conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + ServiceAccpetManID.ToString() + ",%'");
}
if (PayStatus == 0)
{
conditions.Add("isnull([TotalFee],0)=0");
}
if (PayStatus == 1)
{
conditions.Add("isnull([TotalFee],0)>0");
}
if (RoomIDList.Count > 0)
{
cmdlist = new List <string>();
cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[StartTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[StartTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (ServiceStatus > -1)
{
if (ServiceStatus == 100)
{
conditions.Add("[ServiceStatus] in (3,10)");
}
else
{
conditions.Add("[ServiceStatus]=@ServiceStatus");
}
parameters.Add(new SqlParameter("@ServiceStatus", ServiceStatus));
}
}
string fieldList = "[ViewCustomerService].*";
string Statement = " from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()) + cmd;
ViewCustomerService[] list = new ViewCustomerService[] { };
if (canexport)
{
list = GetList <ViewCustomerService>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
totalRows = list.Length;
}
else
{
list = GetList <ViewCustomerService>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
string footer_text = "select '合计' as [ServiceFullName],100 as [ServiceStatus],sum(CASE WHEN ISNUMERIC(HandelFee)=1 THEN convert(decimal(18,2),HandelFee) ELSE 0 END) as HandelFee,sum(isnull(TotalFee,0)) as TotalFee from [ViewCustomerService] where " + string.Join(" and ", conditions.ToArray()) + cmd;
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
dg.footer = GetList <ViewCustomerService>(footer_text, parameters).ToArray();
return(dg);
}
public static Ui.DataGrid GetViewTaiZhangGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int BiaoID, int IsActive, int ID, int RelationStatus, 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("isnull(FullName,'')!=''");
string cmdcondition = ",0 as RelationCount";
if (ID > 0)
{
conditions.Add("[ID]!=@ProjectBiaoID");
conditions.Add("[BiaoGuiGe]!='总表'");
if (RelationStatus == 1)
{
conditions.Add("[ID] in (select [ReationID] from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID)");
}
else if (RelationStatus == 0)
{
conditions.Add("[ID] not in (select [ReationID] from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID)");
}
cmdcondition = ",(select count(1) from [ProjectBiao_Relation] where [ProjectBiaoID]=@ProjectBiaoID and [ReationID]=[ViewTaiZhang].ID) as RelationCount";
parameters.Add(new SqlParameter("@ProjectBiaoID", ID));
}
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("([FullName] like @Keywords or [Name] like @Keywords or [BiaoName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (BiaoID > 0)
{
conditions.Add("[BiaoID]=@BiaoID");
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
}
if (IsActive > int.MinValue)
{
conditions.Add("[IsActive]=@IsActive");
parameters.Add(new SqlParameter("@IsActive", IsActive));
}
string fieldList = @"[ViewTaiZhang].*,
(select sum((isnull(ViewTaiZhang_2.[EndPoint],0)-isnull(ViewTaiZhang_2.StartPoint,0))*
(case when ViewTaiZhang_2.UnitPrice>0 then
ViewTaiZhang_2.UnitPrice else ViewTaiZhang_2.SummaryUnitPrice end)-ViewTaiZhang_2.Rate) from ViewTaiZhang as ViewTaiZhang_2 where ViewTaiZhang_2.ID
in (select ReationID from ProjectBiao_Relation where ProjectBiaoID=ViewTaiZhang.ID)) as CalculateReducePoint " + cmdcondition;
string Statement = " from [ViewTaiZhang] where " + string.Join(" and ", conditions.ToArray());
ViewTaiZhang[] list = new ViewTaiZhang[] { };
if (canexport)
{
list = GetList <ViewTaiZhang>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
totalRows = list.Length;
}
else
{
list = GetList <ViewTaiZhang>(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);
}
