public static Ui.DataGrid GetViewChargeSummaryGrid(int FeeType, int CategoryID, string SummaryType, int ContractID, string guid, string orderBy, long startRowIndex, int pageSize, string keywords)
{
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("[Name] like @keywords");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
if (FeeType > 0)
{
conditions.Add("[FeeType]=@FeeType");
parameters.Add(new SqlParameter("@FeeType", FeeType));
}
if (CategoryID > 0)
{
conditions.Add("[CategoryID]=@CategoryID");
parameters.Add(new SqlParameter("@CategoryID", CategoryID));
}
else if (CategoryID == 0)
{
conditions.Add("[CategoryID] not in (select ID from Category)");
}
if (ContractID > 0)
{
conditions.Add("[ID] in (select [ChargeID] from [Contract_ChargeSummary] where [ContractID]=@ContractID)");
parameters.Add(new SqlParameter("@ContractID", ContractID));
}
else if (!string.IsNullOrEmpty(guid))
{
conditions.Add("[ID] in (select [ChargeID] from [Contract_ChargeSummary] where [GUID]=@GUID)");
parameters.Add(new SqlParameter("@GUID", guid));
}
string fieldList = "[ViewChargeSummary].*,'" + SummaryType + "' as [SummaryType] ";
string Statement = " from [ViewChargeSummary] where " + string.Join(" and ", conditions.ToArray());
ViewChargeSummary[] list = new ViewChargeSummary[] { };
list = GetList <ViewChargeSummary>(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 bool InsertRoomFee(List <int> ProjectIDList, List <int> RoomIDList, ViewChargeSummary summary, string StartTime, string EndTime, int IsCycleFee = 0)
{
return(InsertRoomFee(ProjectIDList, RoomIDList, StartTime, EndTime, 0, summary.SummaryUnitPrice, string.Empty, summary, string.Empty, includenopeople: true, IsCycleFee: IsCycleFee));
}
public static bool InsertRoomFee(List <int> ProjectIDList, List <int> RoomIDList, string StartTime, string EndTime, int ContractID, decimal UnitPrice, string Remark, ViewChargeSummary summary, string NewEndTime, decimal TotalCost = 0, decimal UseCount = 0, int Contract_RoomChargeID = 0, bool includenopeople = true, int IsCycleFee = 0)
{
string sqltext = string.Empty;
List <SqlParameter> parameters = new List <SqlParameter>();
if (string.IsNullOrEmpty(StartTime))
{
StartTime = summary.SummaryStartTime == DateTime.MinValue ? "NULL" : "'" + summary.SummaryStartTime.ToString("yyyy-MM-dd") + "'";
}
else
{
StartTime = "'" + StartTime + "'";
}
if (string.IsNullOrEmpty(EndTime))
{
EndTime = "NULL";
}
else
{
EndTime = "'" + EndTime + "'";
}
if (string.IsNullOrEmpty(NewEndTime))
{
NewEndTime = "NULL";
}
else
{
NewEndTime = "'" + NewEndTime + "'";
}
TotalCost = TotalCost > 0 ? TotalCost : 0;
UseCount = UseCount > 0 ? UseCount : 0;
string AddUserName = "******" + User.GetCurrentUserName() + "'";
if (RoomIDList.Contains(0))
{
sqltext += @"INSERT INTO [dbo].[RoomFee]
([RoomID]
,[UseCount]
,[StartTime]
,[EndTime]
,[NewEndTime]
,[Cost]
,[Remark]
,[AddTime]
,[IsCharged]
,[ChargeFeeID]
,[ChargeID]
,[IsStart]
,[UnitPrice]
,[ChargeFee]
,[ContractID]
,[Contract_RoomChargeID]
,[IsCycleFee]
,[AddUserName])
values(
0," + UseCount + "," + StartTime + "," + EndTime + "," + NewEndTime + "," + TotalCost + ",'" + Remark + @"',getdate(),0,0," + summary.ID + ",1,'" + UnitPrice + "',0," + ContractID + "," + Contract_RoomChargeID + "," + IsCycleFee + "," + AddUserName + ");";
RoomIDList.Remove(0);
}
List <string> conditions = new List <string>();
conditions.Add("[isParent]=0");
conditions.Add("isnull([IsLocked],0)=0");
if (!includenopeople && new Utility.SiteConfig().CheckOwnerInStatus)
{
List <string> cmdlist = new List <string>();
cmdlist.Add("[ID] in (select [RoomID] from [RoomPhoneRelation])");
cmdlist.Add("[ID] in (select [RoomID] from [RoomRelation] where [GUID] in (select [GUID] from [RoomRelation] where [RoomID] in (select [RoomID] from [RoomPhoneRelation])))");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
cmdlist.Add("[ID] in (" + string.Join(",", ProjectIDList.ToArray()) + ")");
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("[AllParentID] like '%," + ProjectID + ",%'");
}
conditions.Add("((" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
conditions.Add("[ID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
}
string cmdcolumn = ",(select [IsLocked] from [RoomBasic] where [RoomID]=[Project].ID) as IsLocked";
if (ContractID > 0)
{
cmdcolumn += ",(select top 1 ID from [RoomPhoneRelation] where [RoomID]=[Project].ID and [ContractID]=@ContractID) as RelationID,(select top 1 RelationName from [RoomPhoneRelation] where [RoomID]=[Project].ID and [ContractID]=@ContractID) as RelationName";
parameters.Add(new SqlParameter("@ContractID", ContractID));
}
else
{
cmdcolumn += ",(select top 1 ID from [RoomPhoneRelation] where [RoomID]=[Project].ID and IsDefault = 1) as RelationID,(select top 1 RelationName from [RoomPhoneRelation] where [RoomID]=[Project].ID and IsDefault = 1) as RelationName";
}
if (RoomIDList.Count > 0 || ProjectIDList.Count > 0)
{
sqltext += @"INSERT INTO [dbo].[RoomFee]
([RoomID]
,[UseCount]
,[StartTime]
,[EndTime]
,[NewEndTime]
,[Cost]
,[Remark]
,[AddTime]
,[IsCharged]
,[ChargeFeeID]
,[ChargeID]
,[IsStart]
,[UnitPrice]
,[ChargeFee]
,[ContractID]
,[Contract_RoomChargeID]
,[IsCycleFee]
,[AddUserName]
,[DefaultChargeManID]
,[DefaultChargeManName])
select [ID]," + UseCount + "," + StartTime + "," + EndTime + "," + NewEndTime + "," + TotalCost + ",'" + Remark + @"',getdate(),0,0," + summary.ID + ",1,'" + UnitPrice + "',0," + ContractID + "," + Contract_RoomChargeID + "," + IsCycleFee + "," + AddUserName + ",A.RelationID,A.RelationName from (select [ID],[AllParentID],[isParent]" + cmdcolumn + " from [Project])A where " + string.Join(" and ", conditions.ToArray()) + ";";
}
if (!string.IsNullOrEmpty(sqltext))
{
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
helper.Execute(sqltext, CommandType.Text, parameters);
helper.Commit();
return(true);
}
catch (Exception)
{
helper.Rollback();
return(false);
}
}
}
return(false);
}