public static StatusReport GetHouseDetail(string roomNumber)
{
string sqlString = "select 房号,层数,建筑面积,所属楼宇,性质,房产类型,户型,部门,所属单元 " +
" from 资源资料_房产单元 " +
" where 编号 = @编号 ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString, new SqlParameter("@编号", roomNumber));
if (dt.Rows.Count == 0)
{
return(new StatusReport().SetFail());
}
DataRow dr = dt.Rows[0];
House house = new House();
house.roomNum = DataTypeHelper.GetStringValue(dr["房号"]);
house.floor = DataTypeHelper.GetStringValue(dr["层数"]);
house.area = DataTypeHelper.GetDecimalValue(dr["建筑面积"]);
house.building = DataTypeHelper.GetStringValue(dr["所属楼宇"]);
house.property = DataTypeHelper.GetStringValue(dr["性质"]);
house.type = DataTypeHelper.GetStringValue(dr["房产类型"]);
house.houseType = DataTypeHelper.GetStringValue(dr["户型"]);
house.department = DataTypeHelper.GetStringValue(dr["部门"]);
house.unit = DataTypeHelper.GetStringValue(dr["所属单元"]);
return(new StatusReport(house));
}
private static ChargeStatisticsBase[] GetCsbs(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return(null);
}
List <ChargeStatisticsBase> csbList = new List <ChargeStatisticsBase>();
foreach (DataRow dr in dt.Rows)
{
ChargeStatisticsBase csb = new ChargeStatisticsBase()
{
chargeType = DataTypeHelper.GetStringValue(dr["费用种类"]),
ztCode = DataTypeHelper.GetStringValue(dr["帐套代码"]),
ztName = DataTypeHelper.GetStringValue(dr["帐套名称"]),
group = DataTypeHelper.GetStringValue(dr["所属组团"]),
building = DataTypeHelper.GetStringValue(dr["所属楼宇"]),
unit = DataTypeHelper.GetStringValue(dr["所属单元"]),
receivedNow = DataTypeHelper.GetDecimalValue(dr["实收当期合计"]),
nowShouldReceived = DataTypeHelper.GetDecimalValue(dr["当期应收"]),
addupShouldReceived = DataTypeHelper.GetDecimalValue(dr["累计应收"]),
addupReceived = DataTypeHelper.GetDecimalValue(dr["累计实收"]),
receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(dr["实收后期应收"]),
month = DataTypeHelper.GetStringValue(dr["统计月份"]),
date = DataTypeHelper.GetDateStringValue(dr["报送日期"])
};
csbList.Add(csb);
}
return(csbList.ToArray());
}
public static StatusReport GetSignupChargeList(string kindergartenName, string openid)
{
/**
* SELECT dbo.基础_小程序报名.姓名, dbo.基础_小程序报名.书包电话, dbo.基础_小程序报名.openid,
* dbo.基础_小程序报名.保教费, dbo.基础_小程序报名.伙食费, dbo.基础_小程序报名.入园杂费,
* dbo.基础_小程序报名.其他费用, dbo.基础_小程序报名.是否已交费, dbo.基础_小程序报名.交费金额,
* dbo.基础_小程序报名.交费时间, dbo.基础_小程序报名.订单编号, dbo.基础_看园管理.交费金额 AS 入园定金
* FROM dbo.基础_小程序报名 LEFT OUTER JOIN
* dbo.基础_看园管理 ON dbo.基础_小程序报名.看园ID = dbo.基础_看园管理.ID
*
* */
StatusReport sr = new StatusReport();
string dbName = kindergartenName == "松园幼儿园" ? "localsy" : "localyd";
string sqlString = " select top 1 基础_小程序报名.ID,基础_小程序报名.姓名, 基础_小程序报名.保教费, 基础_小程序报名.伙食费, 基础_小程序报名.入园杂费, 基础_小程序报名.其他费用, 基础_看园管理.交费金额 as 看园定金" +
" from 基础_小程序报名 " +
" LEFT OUTER JOIN dbo.基础_看园管理 ON 基础_小程序报名.看园ID = 基础_看园管理.ID " +
" where 基础_小程序报名.openid = @openid and (是否已交费 is null or 是否已交费 = '否')" +
" order by 基础_小程序报名.ID desc ";
DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString, new SqlParameter("@openid", openid));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
return(sr);
}
DataRow dr = dt.Rows[0];
SignupCharge sc = new SignupCharge()
{
signupId = DataTypeHelper.GetIntValue(dr["ID"]),
name = DataTypeHelper.GetStringValue(dr["姓名"]),
careFee = DataTypeHelper.GetDecimalValue(dr["保教费"]),
mealFee = DataTypeHelper.GetDecimalValue(dr["伙食费"]),
pettyFee = DataTypeHelper.GetDecimalValue(dr["入园杂费"]),
otherFee = DataTypeHelper.GetDecimalValue(dr["其他费用"]),
kanyuanFee = DataTypeHelper.GetDecimalValue(dr["看园定金"])
};
sc.careFee = sc.careFee == null ? 0 : sc.careFee;
sc.mealFee = sc.mealFee == null ? 0 : sc.mealFee;
sc.pettyFee = sc.pettyFee == null ? 0 : sc.pettyFee;
sc.otherFee = sc.otherFee == null ? 0 : sc.otherFee;
sc.kanyuanFee = sc.kanyuanFee == null ? 0 : sc.kanyuanFee;
sc.totalFee = sc.careFee + sc.mealFee + sc.pettyFee + sc.otherFee;
sr.status = "Success";
sr.result = "成功";
sr.data = sc;
return(sr);
}
public static StatusReport GetChargeStatistics(string ztCode, string level, string userCode, string month)
{
StatusReport sr = new StatusReport();
sr.status = "Fail";
sr.result = "未查询到任何数据";
switch (level)
{
case "一线":
{
string sqlString = " SELECT 所属组团, 实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_项目所有区 " +
" WHERE (帐套代码 = @帐套代码) AND (所属组团 = @所属组团) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团 ";
DataTable dtGroup = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@所属组团", userCode),
new SqlParameter("@统计月份", month));
if (dtGroup.Rows.Count == 0)
{
return(sr);
}
DataRow dr = dtGroup.Rows[0];
ChargeStatisticsGroup csg = new ChargeStatisticsGroup();
csg.group = DataTypeHelper.GetStringValue(dr["所属组团"]);
csg.receivedNow = DataTypeHelper.GetDecimalValue(dr["实收当期合计"]);
csg.nowShouldReceived = DataTypeHelper.GetDecimalValue(dr["当期应收"]);
csg.addupShouldReceived = DataTypeHelper.GetDecimalValue(dr["累计应收"]);
csg.addupReceived = DataTypeHelper.GetDecimalValue(dr["累计实收"]);
csg.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(dr["实收后期应收"]);
csg.addupNotReceived = (csg.addupShouldReceived.HasValue ? csg.addupShouldReceived.Value : 0) - (csg.addupReceived.HasValue ? csg.addupReceived.Value : 0);
csg.rateNowReceived = GetPercent(csg.receivedNow, csg.nowShouldReceived);
csg.rateAddupReceived = GetPercent(csg.addupReceived, csg.addupShouldReceived);
csg.rateBeforeReceived = GetPercent(csg.receivedAfterShouldReceived, csg.nowShouldReceived);
sqlString = " SELECT 所属组团,所属楼宇,实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_区中所有楼宇 " +
" WHERE (帐套代码 = @帐套代码) AND (所属组团 = @所属组团) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团,所属楼宇 ";
DataTable dtbd = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@所属组团", userCode),
new SqlParameter("@统计月份", month));
if (dtbd.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsBuilding> csbdList = new List <ChargeStatisticsBuilding>();
for (int i = 0; i < dtbd.Rows.Count; i++)
{
DataRow drbd = dtbd.Rows[i];
ChargeStatisticsBuilding csbd = new ChargeStatisticsBuilding();
csbd.group = DataTypeHelper.GetStringValue(drbd["所属组团"]);
csbd.building = DataTypeHelper.GetStringValue(drbd["所属楼宇"]);
csbd.receivedNow = DataTypeHelper.GetDecimalValue(drbd["实收当期合计"]);
csbd.nowShouldReceived = DataTypeHelper.GetDecimalValue(drbd["当期应收"]);
csbd.addupShouldReceived = DataTypeHelper.GetDecimalValue(drbd["累计应收"]);
csbd.addupReceived = DataTypeHelper.GetDecimalValue(drbd["累计实收"]);
csbd.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drbd["实收后期应收"]);
csbd.addupNotReceived = (csbd.addupShouldReceived.HasValue ? csbd.addupShouldReceived.Value : 0) - (csbd.addupReceived.HasValue ? csbd.addupReceived.Value : 0);
csbd.rateNowReceived = GetPercent(csbd.receivedNow, csbd.nowShouldReceived);
csbd.rateAddupReceived = GetPercent(csbd.addupReceived, csbd.addupShouldReceived);
csbd.rateBeforeReceived = GetPercent(csbd.receivedAfterShouldReceived, csbd.nowShouldReceived);
sqlString = " SELECT 所属组团,所属楼宇,所属单元,实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_楼宇中所有单元 " +
" WHERE (帐套代码 = @帐套代码) AND (所属组团 = @所属组团) AND (所属楼宇 = @所属楼宇) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团,所属楼宇,所属单元 ";
DataTable dtUnit = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@所属组团", userCode),
new SqlParameter("@所属楼宇", csbd.building),
new SqlParameter("@统计月份", month));
if (dtUnit.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsUnit> csuList = new List <ChargeStatisticsUnit>();
for (int j = 0; j < dtUnit.Rows.Count; j++)
{
DataRow drUnit = dtUnit.Rows[j];
ChargeStatisticsUnit csu = new ChargeStatisticsUnit();
csu.group = DataTypeHelper.GetStringValue(drUnit["所属组团"]);
csu.building = DataTypeHelper.GetStringValue(drUnit["所属楼宇"]);
csu.unit = DataTypeHelper.GetStringValue(drUnit["所属单元"]);
csu.receivedNow = DataTypeHelper.GetDecimalValue(drUnit["实收当期合计"]);
csu.nowShouldReceived = DataTypeHelper.GetDecimalValue(drUnit["当期应收"]);
csu.addupShouldReceived = DataTypeHelper.GetDecimalValue(drUnit["累计应收"]);
csu.addupReceived = DataTypeHelper.GetDecimalValue(drUnit["累计实收"]);
csu.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drUnit["实收后期应收"]);
csu.addupNotReceived = (csu.addupShouldReceived.HasValue ? csu.addupShouldReceived.Value : 0) - (csu.addupReceived.HasValue ? csu.addupReceived.Value : 0);
csu.rateNowReceived = GetPercent(csu.receivedNow, csu.nowShouldReceived);
csu.rateAddupReceived = GetPercent(csu.addupReceived, csu.addupShouldReceived);
csu.rateBeforeReceived = GetPercent(csu.receivedAfterShouldReceived, csu.nowShouldReceived);
csuList.Add(csu);
}
csbd.csUnits = csuList.ToArray();
csbdList.Add(csbd);
}
csg.csBuildings = csbdList.ToArray();
sr.status = "Success";
sr.result = "成功";
sr.data = csg;
}
break;
case "助理":
case "项目经理":
{
string sqlString = " SELECT 帐套名称, 实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_所有项目 " +
" WHERE (帐套代码 = @帐套代码) AND (统计月份 = @统计月份) " +
" ORDER BY 帐套代码 ";
DataTable dtzt = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@统计月份", month));
if (dtzt.Rows.Count == 0)
{
return(sr);
}
DataRow dr = dtzt.Rows[0];
ChargeStatisticsProject csp = new ChargeStatisticsProject();
csp.ztName = DataTypeHelper.GetStringValue(dr["帐套名称"]);
csp.receivedNow = DataTypeHelper.GetDecimalValue(dr["实收当期合计"]);
csp.nowShouldReceived = DataTypeHelper.GetDecimalValue(dr["当期应收"]);
csp.addupShouldReceived = DataTypeHelper.GetDecimalValue(dr["累计应收"]);
csp.addupReceived = DataTypeHelper.GetDecimalValue(dr["累计实收"]);
csp.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(dr["实收后期应收"]);
csp.addupNotReceived = (csp.addupShouldReceived.HasValue ? csp.addupShouldReceived.Value : 0) - (csp.addupReceived.HasValue ? csp.addupReceived.Value : 0);
csp.rateNowReceived = GetPercent(csp.receivedNow, csp.nowShouldReceived);
csp.rateAddupReceived = GetPercent(csp.addupReceived, csp.addupShouldReceived);
csp.rateBeforeReceived = GetPercent(csp.receivedAfterShouldReceived, csp.nowShouldReceived);
sqlString = " SELECT 所属组团, 实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_项目所有区 " +
" WHERE (帐套代码 = @帐套代码) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团 ";
DataTable dtGroup = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@统计月份", month));
if (dtGroup.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsGroup> csgList = new List <ChargeStatisticsGroup>();
for (int k = 0; k < dtGroup.Rows.Count; k++)
{
DataRow drGroup = dtGroup.Rows[k];
ChargeStatisticsGroup csg = new ChargeStatisticsGroup();
csg.group = DataTypeHelper.GetStringValue(drGroup["所属组团"]);
csg.receivedNow = DataTypeHelper.GetDecimalValue(drGroup["实收当期合计"]);
csg.nowShouldReceived = DataTypeHelper.GetDecimalValue(drGroup["当期应收"]);
csg.addupShouldReceived = DataTypeHelper.GetDecimalValue(drGroup["累计应收"]);
csg.addupReceived = DataTypeHelper.GetDecimalValue(drGroup["累计实收"]);
csg.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drGroup["实收后期应收"]);
csg.addupNotReceived = (csg.addupShouldReceived.HasValue ? csg.addupShouldReceived.Value : 0) - (csg.addupReceived.HasValue ? csg.addupReceived.Value : 0);
csg.rateNowReceived = GetPercent(csg.receivedNow, csg.nowShouldReceived);
csg.rateAddupReceived = GetPercent(csg.addupReceived, csg.addupShouldReceived);
csg.rateBeforeReceived = GetPercent(csg.receivedAfterShouldReceived, csg.nowShouldReceived);
sqlString = " SELECT 所属组团,所属楼宇,实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_区中所有楼宇 " +
" WHERE (帐套代码 = @帐套代码) AND (所属组团 = @所属组团) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团,所属楼宇 ";
DataTable dtbd = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@所属组团", csg.group),
new SqlParameter("@统计月份", month));
if (dtbd.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsBuilding> csbdList = new List <ChargeStatisticsBuilding>();
for (int i = 0; i < dtbd.Rows.Count; i++)
{
DataRow drbd = dtbd.Rows[i];
ChargeStatisticsBuilding csbd = new ChargeStatisticsBuilding();
csbd.group = DataTypeHelper.GetStringValue(drbd["所属组团"]);
csbd.building = DataTypeHelper.GetStringValue(drbd["所属楼宇"]);
csbd.receivedNow = DataTypeHelper.GetDecimalValue(drbd["实收当期合计"]);
csbd.nowShouldReceived = DataTypeHelper.GetDecimalValue(drbd["当期应收"]);
csbd.addupShouldReceived = DataTypeHelper.GetDecimalValue(drbd["累计应收"]);
csbd.addupReceived = DataTypeHelper.GetDecimalValue(drbd["累计实收"]);
csbd.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drbd["实收后期应收"]);
csbd.addupNotReceived = (csbd.addupShouldReceived.HasValue ? csbd.addupShouldReceived.Value : 0) - (csbd.addupReceived.HasValue ? csbd.addupReceived.Value : 0);
csbd.rateNowReceived = GetPercent(csbd.receivedNow, csbd.nowShouldReceived);
csbd.rateAddupReceived = GetPercent(csbd.addupReceived, csbd.addupShouldReceived);
csbd.rateBeforeReceived = GetPercent(csbd.receivedAfterShouldReceived, csbd.nowShouldReceived);
sqlString = " SELECT 所属组团,所属楼宇,isnull(所属单元,'1') as 所属单元,实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_楼宇中所有单元 " +
" WHERE (帐套代码 = @帐套代码) AND (所属组团 = @所属组团) AND (所属楼宇 = @所属楼宇) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团,所属楼宇,所属单元 ";
DataTable dtUnit = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@所属组团", csbd.group),
new SqlParameter("@所属楼宇", csbd.building),
new SqlParameter("@统计月份", month));
if (dtUnit.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsUnit> csuList = new List <ChargeStatisticsUnit>();
for (int j = 0; j < dtUnit.Rows.Count; j++)
{
DataRow drUnit = dtUnit.Rows[j];
ChargeStatisticsUnit csu = new ChargeStatisticsUnit();
csu.group = DataTypeHelper.GetStringValue(drUnit["所属组团"]);
csu.building = DataTypeHelper.GetStringValue(drUnit["所属楼宇"]);
csu.unit = DataTypeHelper.GetStringValue(drUnit["所属单元"]);
csu.receivedNow = DataTypeHelper.GetDecimalValue(drUnit["实收当期合计"]);
csu.nowShouldReceived = DataTypeHelper.GetDecimalValue(drUnit["当期应收"]);
csu.addupShouldReceived = DataTypeHelper.GetDecimalValue(drUnit["累计应收"]);
csu.addupReceived = DataTypeHelper.GetDecimalValue(drUnit["累计实收"]);
csu.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drUnit["实收后期应收"]);
csu.addupNotReceived = (csu.addupShouldReceived.HasValue ? csu.addupShouldReceived.Value : 0) - (csu.addupReceived.HasValue ? csu.addupReceived.Value : 0);
csu.rateNowReceived = GetPercent(csu.receivedNow, csu.nowShouldReceived);
csu.rateAddupReceived = GetPercent(csu.addupReceived, csu.addupShouldReceived);
csu.rateBeforeReceived = GetPercent(csu.receivedAfterShouldReceived, csu.nowShouldReceived);
csuList.Add(csu);
}
csbd.csUnits = csuList.ToArray();
csbdList.Add(csbd);
}
csg.csBuildings = csbdList.ToArray();
csgList.Add(csg);
}
csp.csGroups = csgList.ToArray();
sr.status = "Success";
sr.result = "成功";
sr.data = csp;
}
break;
case "公司":
{
string sqlString = " SELECT 实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_公司 " +
" WHERE (统计月份 = @统计月份) ";
DataTable dtCompany = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@统计月份", month));
if (dtCompany.Rows.Count == 0)
{
return(sr);
}
DataRow dr = dtCompany.Rows[0];
ChargeStatisticsCompany csc = new ChargeStatisticsCompany();
csc.receivedNow = DataTypeHelper.GetDecimalValue(dr["实收当期合计"]);
csc.nowShouldReceived = DataTypeHelper.GetDecimalValue(dr["当期应收"]);
csc.addupShouldReceived = DataTypeHelper.GetDecimalValue(dr["累计应收"]);
csc.addupReceived = DataTypeHelper.GetDecimalValue(dr["累计实收"]);
csc.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(dr["实收后期应收"]);
csc.addupNotReceived = (csc.addupShouldReceived.HasValue ? csc.addupShouldReceived.Value : 0) - (csc.addupReceived.HasValue ? csc.addupReceived.Value : 0);
csc.rateNowReceived = GetPercent(csc.receivedNow, csc.nowShouldReceived);
csc.rateAddupReceived = GetPercent(csc.addupReceived, csc.addupShouldReceived);
csc.rateBeforeReceived = GetPercent(csc.receivedAfterShouldReceived, csc.nowShouldReceived);
sqlString = " SELECT 帐套代码,帐套名称,实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_所有项目 " +
" WHERE (统计月份 = @统计月份) " +
" ORDER BY 帐套代码";
DataTable dtProject = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@统计月份", month));
if (dtProject.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsProject> cspList = new List <ChargeStatisticsProject>();
for (int i = 0; i < dtProject.Rows.Count; i++)
{
DataRow drProject = dtProject.Rows[i];
ChargeStatisticsProject csp = new ChargeStatisticsProject();
csp.ztCode = DataTypeHelper.GetStringValue(drProject["帐套代码"]);
csp.ztName = DataTypeHelper.GetStringValue(drProject["帐套名称"]);
csp.receivedNow = DataTypeHelper.GetDecimalValue(drProject["实收当期合计"]);
csp.nowShouldReceived = DataTypeHelper.GetDecimalValue(drProject["当期应收"]);
csp.addupShouldReceived = DataTypeHelper.GetDecimalValue(drProject["累计应收"]);
csp.addupReceived = DataTypeHelper.GetDecimalValue(drProject["累计实收"]);
csp.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drProject["实收后期应收"]);
csp.addupNotReceived = (csp.addupShouldReceived.HasValue ? csp.addupShouldReceived.Value : 0) - (csp.addupReceived.HasValue ? csp.addupReceived.Value : 0);
csp.rateNowReceived = GetPercent(csp.receivedNow, csp.nowShouldReceived);
csp.rateAddupReceived = GetPercent(csp.addupReceived, csp.addupShouldReceived);
csp.rateBeforeReceived = GetPercent(csp.receivedAfterShouldReceived, csp.nowShouldReceived);
sqlString = " SELECT 帐套名称,所属组团,实收当期合计," +
" 当期应收,累计应收,累计实收,实收后期应收,统计月份 " +
" FROM dbo.视图_物业管理费绩效考核统计表_项目所有区 " +
" WHERE (帐套代码 = @帐套代码) AND (统计月份 = @统计月份) " +
" ORDER BY 所属组团 ";
DataTable dtGroup = SQLHelper.ExecuteQuery("weixin", sqlString,
new SqlParameter("@帐套代码", csp.ztCode),
new SqlParameter("@统计月份", month));
if (dtGroup.Rows.Count == 0)
{
return(sr);
}
List <ChargeStatisticsGroup> csgList = new List <ChargeStatisticsGroup>();
for (int j = 0; j < dtGroup.Rows.Count; j++)
{
DataRow drGroup = dtGroup.Rows[j];
ChargeStatisticsGroup csg = new ChargeStatisticsGroup();
csg.group = DataTypeHelper.GetStringValue(drGroup["所属组团"]);
csg.ztName = DataTypeHelper.GetStringValue(drGroup["帐套名称"]);
csg.receivedNow = DataTypeHelper.GetDecimalValue(drGroup["实收当期合计"]);
csg.nowShouldReceived = DataTypeHelper.GetDecimalValue(drGroup["当期应收"]);
csg.addupShouldReceived = DataTypeHelper.GetDecimalValue(drGroup["累计应收"]);
csg.addupReceived = DataTypeHelper.GetDecimalValue(drGroup["累计实收"]);
csg.receivedAfterShouldReceived = DataTypeHelper.GetDecimalValue(drGroup["实收后期应收"]);
csg.addupNotReceived = (csg.addupShouldReceived.HasValue ? csg.addupShouldReceived.Value : 0) - (csg.addupReceived.HasValue ? csg.addupReceived.Value : 0);
csg.rateNowReceived = GetPercent(csg.receivedNow, csg.nowShouldReceived);
csg.rateAddupReceived = GetPercent(csg.addupReceived, csg.addupShouldReceived);
csg.rateBeforeReceived = GetPercent(csg.receivedAfterShouldReceived, csg.nowShouldReceived);
csgList.Add(csg);
}
csp.csGroups = csgList.ToArray();
cspList.Add(csp);
}
csc.csProjects = cspList.ToArray();
sr.status = "Success";
sr.result = "成功";
sr.data = csc;
}
break;
}
return(sr);
}
public static StatusReport BusinessTask(string userId)
{
if (string.IsNullOrEmpty(userId))
{
return(new StatusReport().SetFail("userId不能为空"));
}
string sqlString = "[sp_extcall_业务任务]";
SqlParameter sp = new SqlParameter("@用户ID", SqlDbType.Int);
//sp.Value = 514;
sp.Value = Convert.ToInt32(userId);
DataSet ds = SQLHelper.ExecuteProcedure("wyt", sqlString, sp);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
return(new StatusReport().SetFail("未发现需要处理的流程业务"));
}
List <object> list = new List <object>();
foreach (DataRow dr in dt.Rows)
{
if (DataTypeHelper.GetIntValue(dr["文档表ID"]) == 0)//在返回的行集中，请删除 文档表ID=0 的记录，这类任务表示正在发起，但还没创建表单记录
{
continue;
}
var item = new
{
id = DataTypeHelper.GetIntValue(dr["ID"]),
registId = DataTypeHelper.GetIntValue(dr["登记ID"]),
businessId = DataTypeHelper.GetIntValue(dr["业务ID"]),
linkId = DataTypeHelper.GetIntValue(dr["环节ID"]),
tableNubmer = DataTypeHelper.GetStringValue(dr["表单编号"]),
taskRole = DataTypeHelper.GetStringValue(dr["任务角色"]),
taskExplain = DataTypeHelper.GetStringValue(dr["任务说明"]),
//remark = DataTypeHelper.GetStringValue(dr["备注"]),
summary = DataTypeHelper.GetStringValue(dr["摘要"]),
sender = DataTypeHelper.GetStringValue(dr["送件人"]),
lastInstanceDepartment = DataTypeHelper.GetStringValue(dr["上一实例人员部门"]),
reachTime = DataTypeHelper.GetDateStringValue(dr["送达时间"]),
initiator = DataTypeHelper.GetStringValue(dr["发起人"]),
initiateDepartment = DataTypeHelper.GetStringValue(dr["发起人部门"]),
initiateSecondDepartment = DataTypeHelper.GetStringValue(dr["发起人二级部门"]),
initiateThirdDepartment = DataTypeHelper.GetStringValue(dr["发起人三级部门"]),
initiateTime = DataTypeHelper.GetDateStringValue(dr["发起时间"]),
businessName = DataTypeHelper.GetStringValue(dr["业务名称"]),
docTableName = DataTypeHelper.GetStringValue(dr["文档表名称"]),
docTableId = DataTypeHelper.GetIntValue(dr["文档表ID"]),
task = DataTypeHelper.GetIntValue(dr["任务"]),
remainTime = DataTypeHelper.GetDecimalValue(dr["停留时间"]),
senderLeaveMessage = DataTypeHelper.GetStringValue(dr["送件人留言"]),
leaveMessage = DataTypeHelper.GetStringValue(dr["留言"]),
isRead = DataTypeHelper.GetBooleanValue(dr["阅读"]),
lastInstanceId = DataTypeHelper.GetIntValue(dr["上一实例ID"]),
usedTime = DataTypeHelper.GetDecimalValue(dr["已历时间"]),
objectType = DataTypeHelper.GetStringValue(dr["对象类型"]),
transferObjectId = DataTypeHelper.GetIntValue(dr["传递对象ID"]),
transferObjectType = DataTypeHelper.GetStringValue(dr["传递对象类型"]),
receiveLinkId = DataTypeHelper.GetIntValue(dr["接收环节ID"]),
receiveLinkIds = DataTypeHelper.GetStringValue(dr["接收环节IDs"]),
allowRemainTime = DataTypeHelper.GetDecimalValue(dr["允许停留时间"]),
isImportant = DataTypeHelper.GetBooleanValue(dr["重要"]),
workTip = DataTypeHelper.GetStringValue(dr["工作指引"]),
fieldSpreadOut = DataTypeHelper.GetBooleanValue(dr["表单字段分组展开"]),
transmitConditionAndExplain = DataTypeHelper.GetStringValue(dr["传递条件及说明"]),
staffId = DataTypeHelper.GetIntValue(dr["人员ID"]),
registerId = DataTypeHelper.GetIntValue(dr["登记人ID"]),
linkStaffId = DataTypeHelper.GetIntValue(dr["环节人员ID"]),
tableChangeMarkup = DataTypeHelper.GetStringValue(dr["表单变化标识"]),
objectExplain = DataTypeHelper.GetStringValue(dr["对象描述"])
};
list.Add(item);
}
StatusReport sr = new StatusReport();
sr.status = "Success";
sr.result = "Success";
sr.data = list.ToArray();
return(sr);
}
public static StatusReport GetDecorationList(string classify, string isDone)
{
StatusReport sr = new StatusReport();
string done = "";
if (isDone == "0")
{
//done = " where (分类 = @分类) AND (客服专员 = @客服专员) AND (验收时间 is null) ORDER BY ID DESC";
done = " where (left(分类,2) = @分类) AND (状态 is null or 状态 = '正在装修') ORDER BY ID DESC";
}
else
{
//done = " where (分类 = @分类) AND (客服专员 = @客服专员) AND (验收时间 is not null) ORDER BY ID DESC ";
done = " where (left(分类,2) = @分类) AND (状态 is null or 状态 = '正在装修') ORDER BY ID DESC ";
}
string sqlstring = " SELECT top 100 ID, 分类, 房产编号, 业主姓名, 业主电话, 客服专员, 装修负责人, 装修负责人电话, 装修类型, " +
" 装修公司押金交纳人, 装修押金金额, 财务收款人, 收款日期, 装修施工证编号, 办理时间, 开工时间, 状态, 装修内容, 是否封阳台, " +
" 第一次验收人, 第一次验收时间, 第一次验收结果, 第一次验收结果说明, 第二次验收人, 第二次验收时间, 第二次验收结果, 第二次验收结果说明, " +
" 安装单位名称, 执照号码, 负责人姓名, 负责人电话, 押金交纳人, 封阳台押金金额, 收款人, 退款人, 退款日期, 业主确认, 工程指导人员签字, " +
" 工程指导人员签字日期, 门岗进场控制签字, 门岗进场控制签字日期, 工程确认进场签字, 工程确认进场签字日期, 工程封装巡查签字, 工程封装巡查签字日期, " +
" 封装完毕验收签字, 封装完毕验收签字日期, 工程主管审核, 工程主管审核日期 " +
" FROM dbo.基础资料_装修管理 ";
sqlstring += done;
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring,
new SqlParameter("@分类", classify));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
sr.parameters = sqlstring;
return(sr);
}
List <Decoration> decorationList = new List <Decoration>();
foreach (DataRow dr in dt.Rows)
{
Decoration decoration = new Decoration();
decoration.id = DataTypeHelper.GetIntValue(dr["ID"]);
decoration.classify = DataTypeHelper.GetStringValue(dr["分类"]);
decoration.roomNumber = DataTypeHelper.GetStringValue(dr["房产编号"]);
decoration.name = DataTypeHelper.GetStringValue(dr["业主姓名"]);
decoration.phone = DataTypeHelper.GetStringValue(dr["业主电话"]);
decoration.attache = DataTypeHelper.GetStringValue(dr["客服专员"]);
decoration.decorationMan = DataTypeHelper.GetStringValue(dr["装修负责人"]);
decoration.decorationPhone = DataTypeHelper.GetStringValue(dr["装修负责人电话"]);
decoration.type = DataTypeHelper.GetStringValue(dr["装修类型"]);
decoration.decorationCompanyChargeMan = DataTypeHelper.GetStringValue(dr["装修公司押金交纳人"]);
decoration.decorationCharge = DataTypeHelper.GetDecimalValue(dr["装修押金金额"]);
decoration.decorationChargeReceiver = DataTypeHelper.GetStringValue(dr["财务收款人"]);
decoration.decorationChargeReceiveDate = DataTypeHelper.GetStringValue(dr["收款日期"]);
//decoration.teamMembers = GetTeamMember(decoration.id);
decoration.certificateNumber = DataTypeHelper.GetStringValue(dr["装修施工证编号"]);
decoration.transactTime = DataTypeHelper.GetDateStringValue(dr["办理时间"]);
decoration.startTime = DataTypeHelper.GetDateStringValue(dr["开工时间"]);
decoration.status = DataTypeHelper.GetStringValue(dr["状态"]);
decoration.contents = GetDecorationContent(decoration.id);
decoration.needSealingBalcony = DataTypeHelper.GetStringValue(dr["是否封阳台"]);
decoration.checkMan1 = DataTypeHelper.GetStringValue(dr["第一次验收人"]);
decoration.checkTime1 = DataTypeHelper.GetDateStringValue(dr["第一次验收时间"]);
decoration.checkResult1 = DataTypeHelper.GetStringValue(dr["第一次验收结果"]);
decoration.checkResultExplain1 = DataTypeHelper.GetStringValue(dr["第一次验收结果说明"]);
decoration.checkMan2 = DataTypeHelper.GetStringValue(dr["第二次验收人"]);
decoration.checkTime2 = DataTypeHelper.GetDateStringValue(dr["第二次验收时间"]);
decoration.checkResult2 = DataTypeHelper.GetStringValue(dr["第二次验收结果"]);
decoration.checkResultExplain2 = DataTypeHelper.GetStringValue(dr["第二次验收结果说明"]);
decoration.installDepartment = DataTypeHelper.GetStringValue(dr["安装单位名称"]);
decoration.licenseNumber = DataTypeHelper.GetStringValue(dr["执照号码"]);
decoration.leaderName = DataTypeHelper.GetStringValue(dr["负责人姓名"]);
decoration.leaderPhone = DataTypeHelper.GetStringValue(dr["负责人电话"]);
decoration.chargeMan = DataTypeHelper.GetStringValue(dr["押金交纳人"]);
decoration.charge = DataTypeHelper.GetDecimalValue(dr["封阳台押金金额"]);
decoration.receiver = DataTypeHelper.GetStringValue(dr["收款人"]);
decoration.refundMan = DataTypeHelper.GetStringValue(dr["退款人"]);
decoration.refundDate = DataTypeHelper.GetDateStringValue(dr["退款日期"]);
decoration.proprietorCheck = DataTypeHelper.GetStringValue(dr["业主确认"]);
decoration.checkEngineer = DataTypeHelper.GetStringValue(dr["工程指导人员签字"]);
decoration.checkEngineerSignDate = DataTypeHelper.GetDateStringValue(dr["工程指导人员签字日期"]);
decoration.accessController = DataTypeHelper.GetStringValue(dr["门岗进场控制签字"]);
decoration.accessControllerSignDate = DataTypeHelper.GetDateStringValue(dr["门岗进场控制签字日期"]);
decoration.engineeringCheckAccessMan = DataTypeHelper.GetStringValue(dr["工程确认进场签字"]);
decoration.engineeringCheckAccessManSignDate = DataTypeHelper.GetDateStringValue(dr["工程确认进场签字日期"]);
decoration.engineeringPatrolMan = DataTypeHelper.GetStringValue(dr["工程封装巡查签字"]);
decoration.engineeringPatrolManSignDate = DataTypeHelper.GetDateStringValue(dr["工程封装巡查签字日期"]);
decoration.engineeringCheckMan = DataTypeHelper.GetStringValue(dr["封装完毕验收签字"]);
decoration.engineeringCheckManSignDate = DataTypeHelper.GetDateStringValue(dr["封装完毕验收签字日期"]);
decoration.engineeringManager = DataTypeHelper.GetStringValue(dr["工程主管审核"]);
decoration.engineeringManagerSignDate = DataTypeHelper.GetDateStringValue(dr["工程主管审核日期"]);
decorationList.Add(decoration);
}
sr.status = "Success";
sr.result = "成功";
sr.data = decorationList.ToArray();
sr.parameters = sqlstring;
return(sr);
}
public static StatusReport GetEquipmentTrouble(string classify, string isDone, string name)
{
StatusReport sr = new StatusReport();
string done = "";
if (isDone == "0")
{
done = " where (分类 = @分类) AND 状态 = @状态 and 维修人 = @维修人 ORDER BY ID DESC ";
}
else
{
done = " where (分类 = @分类) AND 状态 = @状态 and 维修人 = @维修人 ORDER BY ID DESC ";
}
string sqlstring = " SELECT * FROM dbo.基础资料_设备故障管理 ";
sqlstring += done;
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring,
new SqlParameter("@分类", DataTypeHelper.GetDBValue(classify)),
new SqlParameter("@状态", isDone == "0" ? "已派单" : "已完成"),
new SqlParameter("@维修人", DataTypeHelper.GetDBValue(name)));
if (dt.Rows.Count == 0)
{
return(sr.SetFail("未查询到任何数据"));
}
List <object> equipmentList = new List <object>();
foreach (DataRow dr in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
for (int i = 1; i <= 3; i++)
{
string beforeImage = DataTypeHelper.GetStringValue(dr["维修前照片" + i.ToString()]);
string afterImage = DataTypeHelper.GetStringValue(dr["处理后照片" + i.ToString()]);
beforeImage = beforeImage.Split('|')[beforeImage.Split('|').Length - 1];
afterImage = afterImage.Split('|')[afterImage.Split('|').Length - 1];
beforeList.Add(beforeImage);
afterList.Add(afterImage);
}
var equipment = new
{
id = DataTypeHelper.GetIntValue(dr["ID"]),
classify = DataTypeHelper.GetStringValue(dr["分类"]),
project = DataTypeHelper.GetStringValue(dr["管理处"]),
registrant = DataTypeHelper.GetStringValue(dr["登记人"]),
registerTime = DataTypeHelper.GetDateStringValue(dr["登记时间"]),
name = DataTypeHelper.GetStringValue(dr["设备名称"]),
number = DataTypeHelper.GetStringValue(dr["设备编号"]),
useAddress = DataTypeHelper.GetStringValue(dr["安装地点"]),
brokenTime = DataTypeHelper.GetDateStringValue(dr["发生时间"]),
brokenInfo = DataTypeHelper.GetStringValue(dr["故障描述"]),
status = DataTypeHelper.GetStringValue(dr["状态"]),
repairMan = DataTypeHelper.GetStringValue(dr["维修人"]),
repairTimeLimit = DataTypeHelper.GetStringValue(dr["维修时限"]),
receiveTime = DataTypeHelper.GetDateStringValue(dr["派工时间"]),
repairInfo = DataTypeHelper.GetStringValue(dr["维修说明"]),
finishTime = DataTypeHelper.GetDateStringValue(dr["完成时间"]),
fee = DataTypeHelper.GetDecimalValue(dr["费用"]),
beforeImage = beforeList.ToArray(),
afterImage = afterList.ToArray(),
};
equipmentList.Add(equipment);
}
sr.status = "Success";
sr.result = "成功";
sr.data = equipmentList.ToArray();
sr.parameters = sqlstring;
return(sr);
}