public static StatusReport GetChargeInfo(string kindergartenName, string feeName)
{
StatusReport sr = new StatusReport();
//string databaseName = "";
string dbName = kindergartenName == "松园幼儿园" ? "localsy" : "localyd";
string sqlString = "select 费用种类,费用名称,应收金额 from 基础_小程序收费设置 where 费用种类 = @费用种类";
DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString, new SqlParameter("@费用种类", feeName));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
return(sr);
}
DataRow dr = dt.Rows[0];
ChargeInfo info = new ChargeInfo()
{
chareType = DataTypeHelper.GetStringValue(dr["费用种类"]),
chargeName = DataTypeHelper.GetStringValue(dr["费用名称"]),
charge = DataTypeHelper.GetDoubleValue(dr["应收金额"])
};
sr.status = "Success";
sr.result = "成功";
sr.data = info;
return(sr);
}
public static StatusReport GetAllPoints(string officeId)
{
StatusReport sr = new StatusReport();
string sqlString = string.Format("select ID,位置名称,经度,纬度,排序编号 from 基础_巡更设置_巡更点 where pid in (select MIN(ID) as ID from 基础_巡更设置 where 组织ID = {0}) order by 排序编号", officeId);
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString);
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(null);
}
List <Point> points = new List <Point>();
foreach (DataRow row in dt.Rows)
{
Point p = new Point();
p.pointId = DataTypeHelper.GetIntValue(row["ID"]);
p.locationName = DataTypeHelper.GetStringValue(row["位置名称"]);
p.longitude = DataTypeHelper.GetDoubleValue(row["经度"]);
p.latitude = DataTypeHelper.GetDoubleValue(row["纬度"]);
p.no = DataTypeHelper.GetIntValue(row["排序编号"]);
p.isScan = false;
points.Add(p);
}
Point[] pointArray = points.ToArray();
//Point[] sortedArray = SortPoints(pointArray);
sr.status = "Success";
sr.result = "成功";
sr.data = pointArray;
return(sr);
}
public static StatusReport SearchEquipment(string operationNumber)
{
StatusReport sr = new StatusReport();
string sqlstring = "SELECT ID, 系统名称, 设备运行编号, 卡号, 设备名称, 设备型号, 设备编号, 安装地点," +
" 产地, 出厂日期, 使用日期, 设备价格, 出厂序号, 设计寿命, 预计残值, 使用年限 " +
"FROM 小程序_设备管理 WHERE 设备编号 = @设备编号";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring, new SqlParameter("@设备编号", operationNumber));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
return(sr);
}
DataRow dr = dt.Rows[0];
Equipment equipment = new Equipment();
equipment.OperationNumber = DataTypeHelper.GetStringValue(dr["设备运行编号"]);
equipment.Number = DataTypeHelper.GetStringValue(dr["设备编号"]);
equipment.Name = DataTypeHelper.GetStringValue(dr["设备名称"]);
equipment.SystemName = DataTypeHelper.GetStringValue(dr["系统名称"]);
equipment.ProductionDate = DataTypeHelper.GetDateStringValue(dr["出厂日期"]);
equipment.UseDate = DataTypeHelper.GetDateStringValue(dr["使用日期"]);
equipment.price = DataTypeHelper.GetDoubleValue(dr["设备价格"]);
equipment.ProductionNumber = DataTypeHelper.GetStringValue(dr["出厂序号"]);
equipment.DesignedLife = DataTypeHelper.GetStringValue(dr["设计寿命"]);
equipment.CardNumber = DataTypeHelper.GetStringValue(dr["卡号"]);
equipment.UseAddress = DataTypeHelper.GetStringValue(dr["安装地点"]);
equipment.ProductionAddress = DataTypeHelper.GetStringValue(dr["产地"]);
sr.status = "Success";
sr.result = "成功";
sr.data = equipment;
return(sr);
}
public static StatusReport GetProprietorList(string ztCode, string homeNumber, string name, string licensePlateNumber)
{
string sqlString =
" SELECT ID, 房产单元编号, 占用者名称, 占用者身份, 联系电话, 联系地址, 紧急联系人, 紧急联系人电话," +
" 紧急联系人地址, 建筑面积, 当前欠款,所属楼宇,房产类型,户型, 车牌号码,车辆颜色,机动车型 " +
" FROM dbo.小程序_现场查询 " +
" WHERE(占用情况 = '正在占用') " +
" and (帐套代码 = @帐套代码) " +
(string.IsNullOrEmpty(homeNumber) ? "" : "and (房产单元编号 like '%" + homeNumber + "%') ") +
(string.IsNullOrEmpty(name) ? "" : "and (占用者名称 like '%" + name + "%') ") +
(string.IsNullOrEmpty(licensePlateNumber) ? "" : "and (车牌号码 like '%" + licensePlateNumber + "%') ") +
"ORDER BY ID ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@资源表编号", homeNumber),
new SqlParameter("@占用者名称", name));
if (dt.Rows.Count <= 0)
{
return(new StatusReport().SetFail("未发现任何数据"));
}
List <object> proprietorList = new List <object>();
foreach (DataRow row in dt.Rows)
{
var p = new
{
Id = DataTypeHelper.GetIntValue(row["ID"]),
RoomNumber = DataTypeHelper.GetStringValue(row["房产单元编号"]),
Name = DataTypeHelper.GetStringValue(row["占用者名称"]),
Identity = DataTypeHelper.GetStringValue(row["占用者身份"]),
Phone = DataTypeHelper.GetStringValue(row["联系电话"]),
Address = DataTypeHelper.GetStringValue(row["联系地址"]),
EmergencyContact = DataTypeHelper.GetStringValue(row["紧急联系人"]),
EmergencyPhone = DataTypeHelper.GetStringValue(row["紧急联系人电话"]),
EmergencyAddress = DataTypeHelper.GetStringValue(row["紧急联系人地址"]),
Area = DataTypeHelper.GetDoubleValue(row["建筑面积"]),
Type = DataTypeHelper.GetStringValue(row["房产类型"]),
Building = DataTypeHelper.GetStringValue(row["所属楼宇"]),
HouseType = DataTypeHelper.GetStringValue(row["户型"]),
TotalArrearage = DataTypeHelper.GetDoubleValue(row["当前欠款"]),
LicensePlateNumber = DataTypeHelper.GetStringValue(row["车牌号码"]),
CarColor = DataTypeHelper.GetStringValue(row["车辆颜色"]),
CarType = DataTypeHelper.GetStringValue(row["机动车型"])
};
proprietorList.Add(p);
}
StatusReport sr = new StatusReport();
sr.status = "Success";
sr.result = "Success";
sr.data = proprietorList.ToArray();
return(sr);
}
public static Proprietor[] GetProprietorList(string ztCode, string homeNumber, string name, string licensePlateNumber)
{
string sqlString =
" SELECT ID, 房产单元编号, 占用者名称, 占用者身份, 联系电话, 联系地址, 紧急联系人, 紧急联系人电话," +
" 紧急联系人地址, 建筑面积, 当前欠款, 房号, 层数, 所属楼宇, 部门,车牌号码,车辆颜色,车辆品牌,车辆型号 " +
" FROM dbo.小程序_现场查询 " +
" WHERE(占用情况 = '正在占用') " +
" and (帐套代码 = @帐套代码) " +
(string.IsNullOrEmpty(homeNumber) ? "" : "and (房产单元编号 like '%" + homeNumber + "%') ") +
(string.IsNullOrEmpty(name) ? "" : "and (占用者名称 like '%" + name + "%') ") +
(string.IsNullOrEmpty(licensePlateNumber) ? "" : "and (车牌号码 like '%" + licensePlateNumber + "%') ") +
"ORDER BY ID ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@帐套代码", ztCode),
new SqlParameter("@资源表编号", homeNumber),
new SqlParameter("@占用者名称", name));
List <Proprietor> proprietorList = new List <Proprietor>();
foreach (DataRow row in dt.Rows)
{
Proprietor p = new Proprietor()
{
Id = DataTypeHelper.GetIntValue(row["ID"]),
RoomNumber = DataTypeHelper.GetStringValue(row["房产单元编号"]),
RoomAddress = SetRoomAddress(DataTypeHelper.GetStringValue(row["部门"]), DataTypeHelper.GetStringValue(row["所属楼宇"]), DataTypeHelper.GetIntValue(row["层数"]), DataTypeHelper.GetStringValue(row["房号"])),
Name = DataTypeHelper.GetStringValue(row["占用者名称"]),
Identity = DataTypeHelper.GetStringValue(row["占用者身份"]),
Phone = DataTypeHelper.GetStringValue(row["联系电话"]),
Address = DataTypeHelper.GetStringValue(row["联系地址"]),
EmergencyContact = DataTypeHelper.GetStringValue(row["紧急联系人"]),
EmergencyPhone = DataTypeHelper.GetStringValue(row["紧急联系人电话"]),
EmergencyAddress = DataTypeHelper.GetStringValue(row["紧急联系人地址"]),
Area = DataTypeHelper.GetDoubleValue(row["建筑面积"]),
TotalArrearage = DataTypeHelper.GetDoubleValue(row["当前欠款"]),
LicensePlateNumber = DataTypeHelper.GetStringValue(row["车牌号码"]),
CarBrand = DataTypeHelper.GetStringValue(row["车辆品牌"]),
CarColor = DataTypeHelper.GetStringValue(row["车辆颜色"]),
CarType = DataTypeHelper.GetStringValue(row["车辆型号"])
};
proprietorList.Add(p);
}
return(proprietorList.ToArray());
}
public static StatusReport GetChargeList(string homeNumber, string name, string ztcode, string buildingNumber)
{
StatusReport sr = new StatusReport();
string sqlString = "SELECT 资源编号, 占用者名称, SUM(应收金额) AS 已收总额, 帐套代码 " +
"FROM 小程序_未收查询 " +
"WHERE 帐套代码 = " + ztcode +
" and 收费状态 is null " +
(string.IsNullOrEmpty(homeNumber) ? "" : "and (资源编号 like '%" + homeNumber + "%') ") +
(string.IsNullOrEmpty(buildingNumber) ? "" : "and (所属楼宇 like '%" + buildingNumber + "%') ") +
(string.IsNullOrEmpty(name) ? "" : "and (占用者名称 like '%" + name + "%') ") +
"GROUP BY 资源编号, 占用者名称, 帐套代码 " +
"ORDER BY 占用者名称";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString);
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
sr.parameters = sqlString;
return(sr);
}
List <Charged> chargedList = new List <Charged>();
foreach (DataRow row in dt.Rows)
{
Charged c = new Charged()
{
RoomNumber = DataTypeHelper.GetStringValue(row["资源编号"]),
Name = DataTypeHelper.GetStringValue(row["占用者名称"]),
Total = DataTypeHelper.GetDoubleValue(row["已收总额"]),
ZTCode = DataTypeHelper.GetStringValue(row["帐套代码"]),
};
chargedList.Add(c);
}
sr.status = "Success";
sr.result = "成功";
sr.data = chargedList.ToArray();
return(sr);
}
public static StatusReport GetPayInfo(string name, string phoneNumber, string kindergartenName)
{
StatusReport sr = new StatusReport();
string dbName = kindergartenName == "松园幼儿园" ? "localsy" : "localyd";
string sqlString = "select 交费金额 from 基础_看园管理 where 姓名 = @姓名 and 联系电话 = @联系电话";
DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString, new SqlParameter("@姓名", name), new SqlParameter("@联系电话", phoneNumber));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "无匹配的看园定金数据";
return(sr);
}
else
{
DataRow dr = dt.Rows[0];
double? totalFee = DataTypeHelper.GetDoubleValue(dr["交费金额"]);
sr.status = "Success";
sr.result = "成功";
sr.data = totalFee;
return(sr);
}
}
/// <summary>
/// 获取满足条件的工单的列表
/// </summary>
/// <param name="openId"></param>
/// <param name="ztcode"></param>
/// <returns></returns>
public static StatusReport GetRepairOrder(string userCode, string ztcode, string status, string orderType)
{
//string receivePerson = UserHelper.GetUser(openId);
// select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)
string orderStatusCondition = status == "未完成" ? " and not(isnull(状态,'') = '已完成') " : " and 状态 = '已完成' ";
StatusReport sr = new StatusReport();
string sqlString =
" select top 100 " +
" ID,序号,部门,地址,报修人,联系电话,服务项目,服务类别," +
" 紧急程度,报修说明,报修时间,预约服务时间,谈好上门时间,发单人,接单人,派工时间," +
" 到场时间,操作人,完成时间,收费类别,材料费,人工费,是否已收,是否阅读,状态,完成情况及所耗物料,报修前照片1," +
" 报修前照片2,报修前照片3,处理后照片1,处理后照片2,处理后照片3,延期原因,预计延期到,回访人,回访意见,回访时间, " +
" 是否满意,业主确认完成,业主确认完成时间,是否满意,业主评价,是否入户,接单人, case when 所属组团 = @接单人 then '客服专员' else '维修工' end as 身份 " +
" from 小程序_工单管理 " +
" where 接单人 is not null and (接单人 = @接单人 or 所属组团 = @接单人) and left(分类,2) = @分类";
sqlString += orderStatusCondition;
sqlString += (" order by " + orderType + " desc");
//sqlString += orderType == "已完成" ? " order by 完成时间 desc " : " order by ID desc ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@接单人", userCode),
new SqlParameter("@分类", ztcode),
new SqlParameter("@状态", status));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
//string sqlStr = " select 序号,内容 from 基础资料_服务任务管理设置_入户维修注意事项 where left(DefClass,2) = @分类 ";
//DataTable dtCaution = SQLHelper.ExecuteQuery("wyt", sqlStr, new SqlParameter("@分类", ztcode));
//List<RepairCaution> rcList = new List<RepairCaution>();
//if (dtCaution.Rows.Count != 0)
//{
// foreach (DataRow drCaution in dtCaution.Rows)
// {
// RepairCaution rc = new RepairCaution();
// rc.number = DataTypeHelper.GetStringValue(drCaution["序号"]);
// rc.content = DataTypeHelper.GetStringValue(drCaution["内容"]);
// rcList.Add(rc);
// }
//}
List <Repair> repairList = new List <Repair>();
foreach (DataRow row in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
Repair r = new Repair();
r.Id = DataTypeHelper.GetIntValue(row["ID"]);
r.SerialNumber = DataTypeHelper.GetStringValue(row["序号"]);
r.Department = DataTypeHelper.GetStringValue(row["部门"]);
r.Address = DataTypeHelper.GetStringValue(row["地址"]);
r.RepairPerson = DataTypeHelper.GetStringValue(row["报修人"]);
r.PhoneNumber = DataTypeHelper.GetStringValue(row["联系电话"]);
r.ServiceProject = DataTypeHelper.GetStringValue(row["服务项目"]);
r.ServiceCategory = DataTypeHelper.GetStringValue(row["服务类别"]);
r.Level = DataTypeHelper.GetStringValue(row["紧急程度"]);
r.Identity = DataTypeHelper.GetStringValue(row["身份"]);
r.NeedIn = DataTypeHelper.GetStringValue(row["是否入户"]);
r.RepairExplain = DataTypeHelper.GetStringValue(row["报修说明"]);
r.RepairTime = DataTypeHelper.GetDateStringValue(row["报修时间"]);
r.OrderTime = DataTypeHelper.GetDateStringValue(row["预约服务时间"]);
r.VisitTime = DataTypeHelper.GetDateStringValue(row["谈好上门时间"]);
r.SendPerson = DataTypeHelper.GetStringValue(row["发单人"]);
r.ReceivePerson = DataTypeHelper.GetStringValue(row["接单人"]);
r.DispatchTime = DataTypeHelper.GetDateStringValue(row["派工时间"]);
r.ArriveTime = DataTypeHelper.GetDateStringValue(row["到场时间"]);
r.OperatePerson = DataTypeHelper.GetStringValue(row["操作人"]);
r.CompleteTime = DataTypeHelper.GetDateStringValue(row["完成时间"]);
r.ChargeType = DataTypeHelper.GetStringValue(row["收费类别"]);
r.MaterialExpense = DataTypeHelper.GetDoubleValue(row["材料费"]);
r.LaborExpense = DataTypeHelper.GetDoubleValue(row["人工费"]);
r.IsPaid = DataTypeHelper.GetStringValue(row["是否已收"]);
r.IsRead = DataTypeHelper.GetIntValue(row["是否阅读"]);
r.AffirmComplete = DataTypeHelper.GetStringValue(row["业主确认完成"]);
r.AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(row["业主评价"]);
r.AffirmCompleteTime = DataTypeHelper.GetDateStringValue(row["业主确认完成时间"]);
r.IsSatisfying = DataTypeHelper.GetStringValue(row["是否满意"]);
r.CallBackEvaluation = DataTypeHelper.GetStringValue(row["回访意见"]);
r.CallBackPerson = DataTypeHelper.GetStringValue(row["回访人"]);
r.CallBackTime = DataTypeHelper.GetDateStringValue(row["回访时间"]);
r.status = DataTypeHelper.GetStringValue(row["状态"]);
r.status = string.IsNullOrEmpty(r.AffirmComplete) ? r.status : "业主已确认";
r.status = string.IsNullOrEmpty(r.CallBackPerson) ? r.status : "已回访";
r.CompleteStatus = DataTypeHelper.GetStringValue(row["完成情况及所耗物料"]);
r.LateTime = DataTypeHelper.GetDateStringValue(row["预计延期到"]);
r.LateReason = DataTypeHelper.GetStringValue(row["延期原因"]);
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片3"]));
r.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片3"]));
r.AfterImage = afterList.ToArray();
//r.Cautions = rcList.ToArray();
repairList.Add(r);
}
sr.status = "Success";
sr.result = "成功";
sr.data = repairList.ToArray();
return(sr);
}
public static StatusReport GetCharges(string ztCode, string roomNumber, string userName)
{
//string sqlString =
// " SELECT dbo.应收款.ID, dbo.应收款.计费年月, dbo.费用项目.费用名称, dbo.应收款.费用说明," +
// " dbo.应收款.应收金额,dbo.应收款.收费状态, dbo.应收款.收款ID, dbo.应收款.收据ID, dbo.资源帐套表.帐套名称 " +
// " FROM dbo.应收款 " +
// " LEFT OUTER JOIN dbo.资源帐套表 ON dbo.应收款.帐套代码 = dbo.资源帐套表.帐套代码 " +
// " LEFT OUTER JOIN dbo.费用项目 ON dbo.应收款.费用项目ID = dbo.费用项目.ID " +
// " WHERE (dbo.应收款.占用者ID = @占用者ID) " +
// " AND (dbo.应收款.房产单元ID = @房产单元ID) " +
// " AND (dbo.应收款.帐套代码 = @帐套代码) " +
// " AND (dbo.应收款.收费状态 IS NULL)";
StatusReport sr = new StatusReport();
string sqlString = " select 应收款ID as ID,计费年月,费用名称,费用说明,应收金额,收费状态,帐套名称 " +
" from 应收款APP" +
" where 帐套代码 = @帐套代码 " +
" and 房号 = @房号 " +
" and 占用者名称 = @占用者名称 " +
" and 收费状态 IS NULL " +
" order by 费用名称";
DataTable dt = SQLHelper.ExecuteQuery("wx", sqlString,
new SqlParameter("@占用者名称", userName),
new SqlParameter("@房号", roomNumber),
new SqlParameter("@帐套代码", ztCode));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
return(sr);
}
List <ChargeDetail> cdList = new List <ChargeDetail>();
foreach (DataRow row in dt.Rows)
{
ChargeDetail cd = new ChargeDetail()
{
Id = DataTypeHelper.GetIntValue(row["ID"]),
ChargeTime = DataTypeHelper.GetStringValue(row["计费年月"]),
ChargeName = DataTypeHelper.GetStringValue(row["费用名称"]),
ChargeInfo = DataTypeHelper.GetStringValue(row["费用说明"]),
Charge = DataTypeHelper.GetDoubleValue(row["应收金额"]),
ChargeStatus = DataTypeHelper.GetStringValue(row["收费状态"])
};
cdList.Add(cd);
}
ChargeDetail[] cdArray = cdList.ToArray();
if (cdArray.Length == 0)
{
return(null);
}
/////////////////////////////////////////////////
string chargeName = cdArray[0].ChargeName;
List <Charge> chargeList = new List <Charge>();
int i = 0;
while (i < cdArray.Length)
{
//string chargeTime = cdArray[0].ChargeTime;
//int k = 0;
//while (k < cdArray.Length)
//{
// ChargeResult chargeResult = new ChargeResult();
//}
Charge charge = new Charge();
charge.ChargeName = chargeName;
List <ChargeDetail> list = new List <ChargeDetail>();
for (int j = i; j < cdArray.Length; j++)
{
if (cdArray[j].ChargeName == chargeName)
{
list.Add(cdArray[j]);
if (j == cdArray.Length - 1)
{
charge.ChargeDetails = list.ToArray();
i = cdArray.Length;
break;
}
}
else
{
charge.ChargeDetails = list.ToArray();
chargeName = cdArray[j].ChargeName;
i = j;
break;
}
}
chargeList.Add(charge);
}
//ChargeResult chargeResult = new ChargeResult();
//chargeResult.Charges = chargeList.ToArray();
sr.status = "Success";
sr.result = "成功";
sr.data = chargeList.ToArray();
return(sr);
///////////////////////////////////////////////////
}
public static StatusReport GetChargedList(string homeNumber, string buildingNumber, string name, string ztcode, string startMonth, string endMonth)
{
StatusReport sr = new StatusReport();
string homeNumberCondition = string.IsNullOrEmpty(homeNumber) ? "" : " and (房产单元编号 like @房产单元编号) ";
string buildingNumberCondition = string.IsNullOrEmpty(buildingNumber) ? "" : " and (所属楼宇 like @所属楼宇) ";
string nameCondition = string.IsNullOrEmpty(name) ? "" : " and (占用者名称 like @占用者名称) ";
string sqlString = "" +
"SELECT 房产单元编号, 占用者名称, SUM(应收金额) AS 已收总额, 帐套代码 " +
"FROM dbo.小程序_已收查询 " +
"WHERE 帐套代码 = @帐套代码" +
homeNumberCondition +
buildingNumberCondition +
nameCondition +
" and 计费开始年月 >= @开始年月 " +
" and 计费开始年月 <= @结束年月 " +
" GROUP BY 房产单元编号, 占用者名称, 帐套代码 " +
" ORDER BY 占用者名称 ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@占用者名称", "%" + name + "%"),
new SqlParameter("@房产单元编号", "%" + homeNumber + "%"),
new SqlParameter("@所属楼宇", "%" + buildingNumber + "%"),
new SqlParameter("@帐套代码", ztcode),
new SqlParameter("@开始年月", startMonth),
new SqlParameter("@结束年月", endMonth));
//string sqlString = "SELECT 房产单元编号, 占用者名称, SUM(应收金额) AS 已收总额, 帐套代码 " +
// "FROM dbo.小程序_已收查询 " +
// "WHERE 帐套代码 = " + ztcode +
// (string.IsNullOrEmpty(homeNumber) ? "" : "and (房产单元编号 like '%" + homeNumber + "%') ") +
// (string.IsNullOrEmpty(buildingNumber) ? "" : "and (所属楼宇 like '%" + buildingNumber + "%') ") +
// (string.IsNullOrEmpty(name) ? "" : "and (占用者名称 like '%" + name + "%') ") +
// " and 计费开始年月 >= " + startMonth +
// " and 计费开始年月 <= " + endMonth +
// " GROUP BY 房产单元编号, 占用者名称, 帐套代码 " +
// " ORDER BY 占用者名称 ";
//DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString);
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
return(sr);
}
//List<Charged> chargedList = new List<Charged>();
//foreach (DataRow row in dt.Rows)
//{
// Charged c = new Charged()
// {
// RoomNumber = DataTypeHelper.GetStringValue(row["房产单元编号"]),
// Name = DataTypeHelper.GetStringValue(row["占用者名称"]),
// Total = DataTypeHelper.GetDoubleValue(row["已收总额"]),
// ZTCode = DataTypeHelper.GetStringValue(row["帐套代码"]),
// };
// chargedList.Add(c);
//}
List <object> chargedList = new List <object>();
foreach (DataRow row in dt.Rows)
{
var c = new
{
RoomNumber = DataTypeHelper.GetStringValue(row["房产单元编号"]),
Name = DataTypeHelper.GetStringValue(row["占用者名称"]),
Total = DataTypeHelper.GetDoubleValue(row["已收总额"]),
ZTCode = DataTypeHelper.GetStringValue(row["帐套代码"]),
};
chargedList.Add(c);
}
sr.status = "Success";
sr.result = "成功";
sr.data = chargedList.ToArray();
return(sr);
}
public static StatusReport GetChargedDetail(string homeNumber, string name, string ztcode, string startMonth, string endMonth)
{
StatusReport sr = new StatusReport();
string sqlString = "SELECT 应收金额, 计费年月, 付款方式, 费用名称, 计费开始年月, 计费截至年月, 收款人, 收费日期 " +
"FROM dbo.小程序_已收查询 " +
"WHERE 房产单元编号 = @房产单元编号 and 占用者名称 = @占用者名称 and 帐套代码 = @帐套代码 " +
"and 计费开始年月 >= @计费开始年月 and 计费开始年月 <= @计费截止年月 " +
"ORDER BY 计费年月,费用名称 ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@房产单元编号", homeNumber),
new SqlParameter("@占用者名称", name),
new SqlParameter("@帐套代码", ztcode),
new SqlParameter("@计费开始年月", startMonth),
new SqlParameter("@计费截止年月", endMonth));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何记录";
return(sr);
}
List <ChargedDetail> cdList = new List <ChargedDetail>();
foreach (DataRow row in dt.Rows)
{
ChargedDetail cd = new ChargedDetail()
{
AmountReceivable = DataTypeHelper.GetDoubleValue(row["应收金额"]),
AmountMonth = string.IsNullOrEmpty(DataTypeHelper.GetStringValue(row["计费年月"])) ? "其他费用" : DataTypeHelper.GetStringValue(row["计费年月"]),
ChargeName = DataTypeHelper.GetStringValue(row["费用名称"]),
ChargeDate = FormatDate(DataTypeHelper.GetDateStringValue(row["收费日期"])),
StartMonth = DataTypeHelper.GetStringValue(row["计费开始年月"]),
EndMonth = DataTypeHelper.GetStringValue(row["计费截至年月"]),
Cashier = DataTypeHelper.GetStringValue(row["收款人"]),
ChargeWay = DataTypeHelper.GetStringValue(row["付款方式"])
};
cdList.Add(cd);
}
ChargedDetail[] cdArray = cdList.ToArray();
if (cdArray.Length == 0)
{
return(null);
}
string month = cdArray[0].AmountMonth;
List <ChargedResult> resList = new List <ChargedResult>();
int i = 0;
while (i < cdArray.Length)
{
ChargedResult cr = new ChargedResult();
cr.AmountMonth = month;
List <ChargedDetail> list = new List <ChargedDetail>();
for (int j = i; j < cdArray.Length; j++)
{
if (cdArray[j].AmountMonth == month)
{
list.Add(cdArray[j]);
if (j == cdArray.Length - 1)
{
cr.Detail = list.ToArray();
i = cdArray.Length;
break;
}
}
else
{
cr.Detail = list.ToArray();
month = cdArray[j].AmountMonth;
i = j;
break;
}
}
resList.Add(cr);
}
sr.status = "Success";
sr.result = "成功";
sr.data = resList.ToArray();
return(sr);
}
public static StatusReport GetEquipmentReport(string ztcode)
{
StatusReport sr = new StatusReport();
string done = " where (帐套代码 = @帐套代码) AND 是否完成 is null " +
" and (datediff(day,工作日期,GETDATE())> 宽限上延天数) and datediff(day,工作日期,GETDATE())>= 0 " +
" ORDER BY ID DESC";
string sqlstring = " SELECT ID, 分类, 设备运行编号, 设备编号, 设备型号, 设备名称, 系统名称, 出厂日期, " +
" 使用日期, 设备价格, 出厂序号, 设计寿命, 卡号, 安装地点, 产地, 设备保养管理代号, 设备保养管理内容, " +
" 设备保养管理日期, 工作名称, 工作日期, 是否完成, 录入日期, 录入人, 完成说明, 序次, 保养前照片, 保养中照片, 保养后照片, " +
" 宽限上延天数,宽限下延天数 " +
" FROM dbo.小程序_设备管理 ";
sqlstring += done;
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring,
new SqlParameter("@帐套代码", ztcode));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
sr.parameters = sqlstring;
return(sr);
}
List <Equipment> equipmentList = new List <Equipment>();
foreach (DataRow dr in dt.Rows)
{
Equipment equipment = new Equipment();
equipment.ID = DataTypeHelper.GetIntValue(dr["ID"]);
equipment.Classify = DataTypeHelper.GetStringValue(dr["分类"]);
equipment.OperationNumber = DataTypeHelper.GetStringValue(dr["设备运行编号"]);
equipment.Number = DataTypeHelper.GetStringValue(dr["设备编号"]);
equipment.Name = DataTypeHelper.GetStringValue(dr["设备名称"]);
equipment.SystemName = DataTypeHelper.GetStringValue(dr["系统名称"]);
equipment.ProductionDate = DataTypeHelper.GetStringValue(dr["出厂日期"]);
equipment.UseDate = DataTypeHelper.GetStringValue(dr["使用日期"]);
equipment.price = DataTypeHelper.GetDoubleValue(dr["设备价格"]);
equipment.ProductionNumber = DataTypeHelper.GetStringValue(dr["出厂序号"]);
equipment.DesignedLife = DataTypeHelper.GetStringValue(dr["设计寿命"]);
equipment.CardNumber = DataTypeHelper.GetStringValue(dr["卡号"]);
equipment.UseAddress = DataTypeHelper.GetStringValue(dr["安装地点"]);
equipment.ProductionAddress = DataTypeHelper.GetStringValue(dr["产地"]);
equipment.MaintainNumber = DataTypeHelper.GetStringValue(dr["设备保养管理代号"]);
equipment.MaintainContent = DataTypeHelper.GetStringValue(dr["设备保养管理内容"]);
equipment.MaintainDate = DataTypeHelper.GetDateStringValue(dr["设备保养管理日期"]);
equipment.WorkDate = DataTypeHelper.GetDateStringValue(dr["工作日期"]);
equipment.IsDone = DataTypeHelper.GetBooleanValue(dr["是否完成"]) == true ? 1 : 0;
equipment.InputDate = DataTypeHelper.GetDateStringValue(dr["录入日期"]);
equipment.InputMan = DataTypeHelper.GetStringValue(dr["录入人"]);
equipment.DoneInfo = DataTypeHelper.GetStringValue(dr["完成说明"]);
equipment.BeforeImage = DataTypeHelper.GetStringValue(dr["保养前照片"]);
equipment.MiddleImage = DataTypeHelper.GetStringValue(dr["保养中照片"]);
equipment.AfterImage = DataTypeHelper.GetStringValue(dr["保养后照片"]);
equipment.Order = DataTypeHelper.GetBooleanValue(dr["序次"]) == true ? "1" : "0";
equipment.BeforeDays = DataTypeHelper.GetIntValue(dr["宽限上延天数"]);
equipment.AfterDays = DataTypeHelper.GetIntValue(dr["宽限下延天数"]);
equipmentList.Add(equipment);
}
sr.status = "Success";
sr.result = "成功";
sr.data = equipmentList.ToArray();
sr.parameters = sqlstring;
return(sr);
}
/// <summary>
/// 获取满足条件的工单的列表
/// </summary>
/// <param name="openId"></param>
/// <param name="ztcode"></param>
/// <returns></returns>
public static StatusReport GetRepairOrder(string userCode, string ztcode, string status, string orderType)
{
string orderStatusCondition = status == "未完成" ? " and not(isnull(状态,'') = '已完成') " : " and 状态 = '已完成' ";
StatusReport sr = new StatusReport();
string sqlString =
" SELECT TOP 100 " +
" ID,部门,序号,地址,报修人,联系电话,服务项目,发单人,接单人,报修时间,派工时间,预约服务时间, " +
" 完成情况及所耗物料,操作人,完成时间,材料费,人工费,合计,收费类别,主管意见,服务台签字,客户意见, " +
" 目录显示,回访时间,回访意见,回访人,到场时间,状态,是否阅读,报修前照片1,报修前照片2,报修前照片3, " +
" 处理后照片1,处理后照片2,处理后照片3,报修来源,报修处理时间,报修处理ID,网上报修时间,服务类别,紧急程度, " +
" 报修说明,谈好上门时间,帐套代码,帐套名称 " +
" FROM 小程序_工单管理 where 帐套代码 = @帐套代码 and year(报修时间) > 2017 and 接单人 = @接单人 ";
//" FROM 小程序_工单管理 where 接单人 = @接单人 and 帐套代码 = @帐套代码 ";
sqlString += orderStatusCondition;
sqlString += (" order by " + orderType + " desc");
//sqlString += orderType == "已完成" ? " order by 完成时间 desc " : " order by ID desc ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@接单人", userCode),
new SqlParameter("@帐套代码", ztcode),
new SqlParameter("@状态", status));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
//string sqlStr = " select 序号,内容 from 基础资料_服务任务管理设置_入户维修注意事项 where left(DefClass,2) = @分类 ";
//DataTable dtCaution = SQLHelper.ExecuteQuery("wyt", sqlStr, new SqlParameter("@分类", ztcode));
//List<RepairCaution> rcList = new List<RepairCaution>();
//if (dtCaution.Rows.Count != 0)
//{
// foreach (DataRow drCaution in dtCaution.Rows)
// {
// RepairCaution rc = new RepairCaution();
// rc.number = DataTypeHelper.GetStringValue(drCaution["序号"]);
// rc.content = DataTypeHelper.GetStringValue(drCaution["内容"]);
// rcList.Add(rc);
// }
//}
List <Repair> repairList = new List <Repair>();
foreach (DataRow row in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
Repair r = new Repair
{
Id = DataTypeHelper.GetIntValue(row["ID"]),
SerialNumber = DataTypeHelper.GetStringValue(row["序号"]),
Department = DataTypeHelper.GetStringValue(row["部门"]),
Address = DataTypeHelper.GetStringValue(row["地址"]),
RepairPerson = DataTypeHelper.GetStringValue(row["报修人"]),
PhoneNumber = DataTypeHelper.GetStringValue(row["联系电话"]),
ServiceProject = DataTypeHelper.GetStringValue(row["服务项目"]),
ServiceCategory = DataTypeHelper.GetStringValue(row["服务类别"]),
Level = DataTypeHelper.GetStringValue(row["紧急程度"]),
//Identity = DataTypeHelper.GetStringValue(row["身份"]),
//NeedIn = DataTypeHelper.GetStringValue(row["是否入户"]),
RepairExplain = DataTypeHelper.GetStringValue(row["报修说明"]),
RepairTime = DataTypeHelper.GetDateStringValue(row["报修时间"]),
OrderTime = DataTypeHelper.GetDateStringValue(row["预约服务时间"]),
VisitTime = DataTypeHelper.GetDateStringValue(row["谈好上门时间"]),
SendPerson = DataTypeHelper.GetStringValue(row["发单人"]),
ReceivePerson = DataTypeHelper.GetStringValue(row["接单人"]),
DispatchTime = DataTypeHelper.GetDateStringValue(row["派工时间"]),
ArriveTime = DataTypeHelper.GetDateStringValue(row["到场时间"]),
OperatePerson = DataTypeHelper.GetStringValue(row["操作人"]),
CompleteTime = DataTypeHelper.GetDateStringValue(row["完成时间"]),
ChargeType = DataTypeHelper.GetStringValue(row["收费类别"]),
MaterialExpense = DataTypeHelper.GetDoubleValue(row["材料费"]),
LaborExpense = DataTypeHelper.GetDoubleValue(row["人工费"]),
//r.IsPaid = DataTypeHelper.GetStringValue(row["是否已收"]);
IsRead = DataTypeHelper.GetIntValue(row["是否阅读"]),
//r.AffirmComplete = DataTypeHelper.GetStringValue(row["业主确认完成"]);
//r.AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(row["业主评价"]);
//r.AffirmCompleteTime = DataTypeHelper.GetDateStringValue(row["业主确认完成时间"]);
//r.IsSatisfying = DataTypeHelper.GetStringValue(row["是否满意"]);
CallBackEvaluation = DataTypeHelper.GetStringValue(row["回访意见"]),
CallBackPerson = DataTypeHelper.GetStringValue(row["回访人"]),
CallBackTime = DataTypeHelper.GetDateStringValue(row["回访时间"]),
status = DataTypeHelper.GetStringValue(row["状态"])
};
//r.status = string.IsNullOrEmpty(r.AffirmComplete) ? r.status : "业主已确认";
//r.status = string.IsNullOrEmpty(r.CallBackPerson) ? r.status : "已回访";
r.CompleteStatus = DataTypeHelper.GetStringValue(row["完成情况及所耗物料"]);
//r.LateTime = DataTypeHelper.GetDateStringValue(row["预计延期到"]);
//r.LateReason = DataTypeHelper.GetStringValue(row["延期原因"]);
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片3"]));
r.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片3"]));
r.AfterImage = afterList.ToArray();
//r.Cautions = rcList.ToArray();
repairList.Add(r);
}
sr.status = "Success";
sr.result = "成功";
sr.data = repairList.ToArray();
return(sr);
}
//public static StatusReport GetRepairReport1(string ztcode, string level)
//{
// StatusReport sr = new StatusReport();
// string sqlStr =
// " select " +
// " ID,序号,部门,地址,报修人,联系电话,服务项目,服务类别," +
// " 紧急程度,报修说明,报修时间,预约服务时间,谈好上门时间,发单人,接单人,派工时间," +
// " 到场时间,操作人,完成时间,收费类别,材料费,人工费,是否已收,是否阅读,状态,完成情况及所耗物料,报修前照片1," +
// " 报修前照片2,报修前照片3,处理后照片1,处理后照片2,处理后照片3,延期原因,预计延期到,回访人,回访意见,回访时间, " +
// " 是否满意,业主确认完成,业主确认完成时间,是否满意,业主评价,是否入户 " +
// " from 小程序_工单管理 ";
// string sqlString = "";
// string condition = "";
// if (level == "助理")
// {
// DataTable dt = null;
// List<Repair> repairList = null;
// List<RepairReportProject> rrpList = new List<RepairReportProject>();
// RepairReportProject rrp = null;
// condition = " where 帐套代码 = @帐套代码 and isnull(完成时间,'') = '' " +
// " and left(CONVERT(varchar(10),getdate(),112),8) = left(CONVERT(varchar(10),报修时间,112),8) " +
// " and cast(left(CONVERT(varchar(20),getdate(),114),2) as int) >= 17 ";//当日未完工
// sqlString = sqlStr + condition;
// dt = SQLHelper.ExecuteQuery("wyt", sqlString,
// new SqlParameter("@帐套代码", ztcode));
// rrp = new RepairReportProject();
// rrp.type = "当日未完成";
// if (dt.Rows.Count != 0)
// {
// rrp = new RepairReportProject();
// rrp.type = "当日未完成";
// repairList = GetRepair(dt);
// rrp.repairs = repairList.ToArray();
// }
// rrpList.Add(rrp);
// condition = " where " +
// " 帐套代码 = @帐套代码 and ((预估完成时间 is not null " +
// " and isnull(完成时间,'') = '' " +
// " and DATEDIFF (hour, 预估完成时间, getdate()) >= 0) " +
// " or (预估完成时间 is not null " +
// " and DATEDIFF (hour, 预估完成时间, 完成时间) > 0" +
// " and DATEDIFF (hour, 预估完成时间, getdate()) >= 0)) ";//超过预估完成时间
// sqlString = sqlStr + condition;
// dt = SQLHelper.ExecuteQuery("wyt", sqlString,
// new SqlParameter("@帐套代码", ztcode));
// rrp = new RepairReportProject();
// rrp.type = "超过预估完成时间";
// if (dt.Rows.Count != 0)
// {
// repairList = GetRepair(dt);
// rrp.repairs = repairList.ToArray();
// }
// rrpList.Add(rrp);
// sr.status = "success";
// sr.result = "成功";
// sr.data = rrpList.ToArray();
// return sr;
// }
// else
// {
// condition = " where 帐套代码 = @帐套代码 and 是否满意 = '不满意' ";
// RepairReportCompany rrc = new RepairReportCompany();
// rrc.type = "业主不满意";
// sqlString = sqlStr + condition;
// DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
// new SqlParameter("@帐套代码", ztcode));
// if (dt.Rows.Count == 0)
// {
// sr.status = "Fail";
// sr.result = "未查询到任何数据";
// return sr;
// }
// List<Repair> repairList = GetRepair(dt);
// rrc.repairs = repairList.ToArray();
// sr.status = "Success";
// sr.result = "成功";
// sr.data = rrc;
// return sr;
// }
//}
private static List <RepairReport> GetRepairReport(DataTable dt)
{
List <RepairReport> repairList = new List <RepairReport>();
foreach (DataRow row in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
RepairReport r = new RepairReport();
r.Id = DataTypeHelper.GetIntValue(row["ID"]);
r.SerialNumber = DataTypeHelper.GetStringValue(row["序号"]);
r.Department = DataTypeHelper.GetStringValue(row["部门"]);
r.Address = DataTypeHelper.GetStringValue(row["地址"]);
r.RepairPerson = DataTypeHelper.GetStringValue(row["报修人"]);
r.PhoneNumber = DataTypeHelper.GetStringValue(row["联系电话"]);
r.ServiceProject = DataTypeHelper.GetStringValue(row["服务项目"]);
r.ServiceCategory = DataTypeHelper.GetStringValue(row["服务类别"]);
r.Level = DataTypeHelper.GetStringValue(row["紧急程度"]);
//r.Identity = DataTypeHelper.GetStringValue(row["身份"]);
r.NeedIn = DataTypeHelper.GetStringValue(row["是否入户"]);
r.RepairExplain = DataTypeHelper.GetStringValue(row["报修说明"]);
r.RepairTime = DataTypeHelper.GetDateStringValue(row["报修时间"]);
r.OrderTime = DataTypeHelper.GetDateStringValue(row["预约服务时间"]);
r.VisitTime = DataTypeHelper.GetDateStringValue(row["谈好上门时间"]);
r.SendPerson = DataTypeHelper.GetStringValue(row["发单人"]);
r.ReceivePerson = DataTypeHelper.GetStringValue(row["接单人"]);
r.DispatchTime = DataTypeHelper.GetDateStringValue(row["派工时间"]);
r.ArriveTime = DataTypeHelper.GetDateStringValue(row["到场时间"]);
r.OperatePerson = DataTypeHelper.GetStringValue(row["操作人"]);
r.CompleteTime = DataTypeHelper.GetDateStringValue(row["完成时间"]);
r.ChargeType = DataTypeHelper.GetStringValue(row["收费类别"]);
r.MaterialExpense = DataTypeHelper.GetDoubleValue(row["材料费"]);
r.LaborExpense = DataTypeHelper.GetDoubleValue(row["人工费"]);
r.IsPaid = DataTypeHelper.GetStringValue(row["是否已收"]);
r.IsRead = DataTypeHelper.GetIntValue(row["是否阅读"]);
r.AffirmComplete = DataTypeHelper.GetStringValue(row["业主确认完成"]);
r.AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(row["业主评价"]);
r.AffirmCompleteTime = DataTypeHelper.GetDateStringValue(row["业主确认完成时间"]);
r.IsSatisfying = DataTypeHelper.GetStringValue(row["是否满意"]);
r.CallBackEvaluation = DataTypeHelper.GetStringValue(row["回访意见"]);
r.CallBackPerson = DataTypeHelper.GetStringValue(row["回访人"]);
r.CallBackTime = DataTypeHelper.GetDateStringValue(row["回访时间"]);
r.status = DataTypeHelper.GetStringValue(row["状态"]);
r.status = string.IsNullOrEmpty(r.AffirmComplete) ? r.status : "业主已确认";
r.status = string.IsNullOrEmpty(r.CallBackPerson) ? r.status : "已回访";
r.CompleteStatus = DataTypeHelper.GetStringValue(row["完成情况及所耗物料"]);
r.LateTime = DataTypeHelper.GetDateStringValue(row["预计延期到"]);
r.type = DataTypeHelper.GetStringValue(row["上报原因"]);
r.LateReason = DataTypeHelper.GetStringValue(row["延期原因"]);
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片3"]));
r.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片3"]));
r.AfterImage = afterList.ToArray();
repairList.Add(r);
}
return(repairList);
}
/// <summary>
/// 获取报事信息
/// </summary>
/// <param name="name"></param>
/// <param name="classify"></param>
/// <returns></returns>
public static StatusReport GetPatrol(string name, string classify)
{
StatusReport sr = new StatusReport();
string sqlString =
" select top 10 " +
" ID,序号,部门,地址,报修人,联系电话,服务项目,服务类别," +
" 紧急程度,报修说明,报修时间,预约服务时间,谈好上门时间,发单人,接单人,派工时间," +
" 到场时间,操作人,完成时间,收费类别,材料费,人工费,是否已收,是否阅读,状态,完成情况及所耗物料,报修前照片1," +
" 报修前照片2,报修前照片3,处理后照片1,处理后照片2,处理后照片3,延期原因,预计延期到,回访人,回访意见,回访时间, " +
" 是否满意,业主确认完成,业主确认完成时间,是否满意,业主评价 " +
" from 小程序_工单管理 " +
" where 报修人 = @报修人 and left(分类,2) = @分类 and 报修来源 = @报修来源 " +
" order by ID desc ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@报修人", name),
new SqlParameter("@分类", classify),
new SqlParameter("@报修来源", "小程序报事"));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
List <Repair> repairList = new List <Repair>();
foreach (DataRow row in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
Repair r = new Repair();
r.Id = DataTypeHelper.GetIntValue(row["ID"]);
r.SerialNumber = DataTypeHelper.GetStringValue(row["序号"]);
r.Department = DataTypeHelper.GetStringValue(row["部门"]);
r.Address = DataTypeHelper.GetStringValue(row["地址"]);
r.RepairPerson = DataTypeHelper.GetStringValue(row["报修人"]);
r.PhoneNumber = DataTypeHelper.GetStringValue(row["联系电话"]);
r.ServiceProject = DataTypeHelper.GetStringValue(row["服务项目"]);
r.ServiceCategory = DataTypeHelper.GetStringValue(row["服务类别"]);
r.Level = DataTypeHelper.GetStringValue(row["紧急程度"]);
r.RepairExplain = DataTypeHelper.GetStringValue(row["报修说明"]);
r.RepairTime = DataTypeHelper.GetDateStringValue(row["报修时间"]);
r.OrderTime = DataTypeHelper.GetDateStringValue(row["预约服务时间"]);
r.VisitTime = DataTypeHelper.GetDateStringValue(row["谈好上门时间"]);
r.SendPerson = DataTypeHelper.GetStringValue(row["发单人"]);
r.ReceivePerson = DataTypeHelper.GetStringValue(row["接单人"]);
r.DispatchTime = DataTypeHelper.GetDateStringValue(row["派工时间"]);
r.ArriveTime = DataTypeHelper.GetDateStringValue(row["到场时间"]);
r.OperatePerson = DataTypeHelper.GetStringValue(row["操作人"]);
r.CompleteTime = DataTypeHelper.GetDateStringValue(row["完成时间"]);
r.ChargeType = DataTypeHelper.GetStringValue(row["收费类别"]);
r.MaterialExpense = DataTypeHelper.GetDoubleValue(row["材料费"]);
r.LaborExpense = DataTypeHelper.GetDoubleValue(row["人工费"]);
r.IsPaid = DataTypeHelper.GetStringValue(row["是否已收"]);
r.IsRead = DataTypeHelper.GetIntValue(row["是否阅读"]);
r.AffirmComplete = DataTypeHelper.GetStringValue(row["业主确认完成"]);
r.AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(row["是否满意"]);
r.AffirmCompleteTime = DataTypeHelper.GetDateStringValue(row["业主确认完成时间"]);
r.IsSatisfying = DataTypeHelper.GetStringValue(row["是否满意"]);
r.CallBackEvaluation = DataTypeHelper.GetStringValue(row["回访意见"]);
r.CallBackPerson = DataTypeHelper.GetStringValue(row["回访人"]);
r.CallBackTime = DataTypeHelper.GetDateStringValue(row["回访时间"]);
r.status = DataTypeHelper.GetStringValue(row["状态"]);
r.status = string.IsNullOrEmpty(r.AffirmComplete) ? r.status : "业主已确认";
r.status = string.IsNullOrEmpty(r.CallBackPerson) ? r.status : "已回访";
r.CompleteStatus = DataTypeHelper.GetStringValue(row["完成情况及所耗物料"]);
r.LateTime = DataTypeHelper.GetDateStringValue(row["预计延期到"]);
r.LateReason = DataTypeHelper.GetStringValue(row["延期原因"]);
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片3"]));
r.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片3"]));
r.AfterImage = afterList.ToArray();
repairList.Add(r);
}
sr.status = "Success";
sr.result = "成功";
sr.data = repairList.ToArray();
return(sr);
}
public static StatusReport GetCharges(string ztCode, string roomNumber, string userName)
{
StatusReport sr = new StatusReport()
{
status = "Success",
result = "成功"
};
Charge charge = new Charge();
string sqlString = " select 应收款ID as ID " +
" from weixin.dbo.应收款APP" +
" where 帐套代码 = @帐套代码 " +
" and 房号 = @房号 " +
" and 占用者名称 = @占用者名称 " +
" and 收费状态 IS NULL";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@占用者名称", userName),
new SqlParameter("@房号", roomNumber),
new SqlParameter("@帐套代码", ztCode));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到符合条件的记录";
return(sr);
}
List <int?> idList = new List <int?>();
foreach (DataRow row in dt.Rows)
{
int?id = DataTypeHelper.GetIntValue(row["ID"]);
idList.Add(id);
}
charge.Ids = idList.ToArray();
sqlString = "SELECT 资源编号, 占用者名称, SUM(应收金额) AS 应收总额, MIN(计费年月) + '-' + MAX(计费年月) + 费用名称 AS 费用名称 " +
"FROM weixin.dbo.应收款APP " +
" where 帐套代码 = @帐套代码 " +
" and 房号 = @房号 " +
" and 占用者名称 = @占用者名称 " +
" and 收费状态 IS NULL " +
"GROUP BY 资源编号, 占用者名称, 费用名称 ";
dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@占用者名称", userName),
new SqlParameter("@房号", roomNumber),
new SqlParameter("@帐套代码", ztCode));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到符合条件的记录";
return(sr);
}
List <ChargeDetail> chargeList = new List <ChargeDetail>();
foreach (DataRow dr in dt.Rows)
{
ChargeDetail chargeDetail = new ChargeDetail()
{
ChargeName = DataTypeHelper.GetStringValue(dr["费用名称"]),
Charge = DataTypeHelper.GetDoubleValue(dr["应收总额"])
};
chargeList.Add(chargeDetail);
}
charge.ChargeDetail = chargeList.ToArray();
sr.data = charge;
return(sr);
}
{ /// <summary>
/// 获取满足条件的工单的列表
/// </summary>
/// <param name="name"></param>
/// <param name="phone"></param>
/// <returns></returns>
public static StatusReport GetRepairOrder(string name, string phone)
{
StatusReport sr = new StatusReport();
string sqlString =
" select top 100 " +
" ID,序号,部门,地址,报修人,联系电话,服务项目,服务类别, " +
" 紧急程度,报修说明,报修时间,预约服务时间,谈好上门时间,发单人,接单人,派工时间, " +
" 到场时间,操作人,完成时间,材料费,人工费,是否阅读,状态,完成情况及所耗物料, " +
" 报修前照片1,报修前照片2,报修前照片3,处理后照片1,处理后照片2,处理后照片3 " +
//"业主确认完成,业主确认完成时间,业主评价,是否满意 " +
" from 基础资料_服务任务管理 " +
" where 报修人 = @报修人 and 联系电话 = @联系电话 " +
" order by ID desc ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@报修人", name),
new SqlParameter("@联系电话", phone));
if (dt.Rows.Count == 0)
{
sr.SetFail("未查询到报修记录");
}
else
{
List <Repair> repairList = new List <Repair>();
foreach (DataRow row in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
Repair r = new Repair();
r.Id = DataTypeHelper.GetIntValue(row["ID"]);
r.SerialNumber = DataTypeHelper.GetStringValue(row["序号"]);
r.Department = DataTypeHelper.GetStringValue(row["部门"]);
r.Address = DataTypeHelper.GetStringValue(row["地址"]);
r.RepairPerson = DataTypeHelper.GetStringValue(row["报修人"]);
r.PhoneNumber = DataTypeHelper.GetStringValue(row["联系电话"]);
r.ServiceProject = DataTypeHelper.GetStringValue(row["服务项目"]);
r.ServiceCategory = DataTypeHelper.GetStringValue(row["服务类别"]);
r.Level = DataTypeHelper.GetStringValue(row["紧急程度"]);
r.RepairExplain = DataTypeHelper.GetStringValue(row["报修说明"]);
r.RepairTime = DataTypeHelper.GetDateStringValue(row["报修时间"]);
r.OrderTime = DataTypeHelper.GetDateStringValue(row["预约服务时间"]);
r.VisitTime = DataTypeHelper.GetDateStringValue(row["谈好上门时间"]);
r.SendPerson = DataTypeHelper.GetStringValue(row["发单人"]);
r.ReceivePerson = DataTypeHelper.GetStringValue(row["接单人"]);
r.DispatchTime = DataTypeHelper.GetDateStringValue(row["派工时间"]);
r.ArriveTime = DataTypeHelper.GetDateStringValue(row["到场时间"]);
r.OperatePerson = DataTypeHelper.GetStringValue(row["操作人"]);
r.CompleteTime = DataTypeHelper.GetDateStringValue(row["完成时间"]);
r.MaterialExpense = DataTypeHelper.GetDoubleValue(row["材料费"]);
r.LaborExpense = DataTypeHelper.GetDoubleValue(row["人工费"]);
r.IsRead = DataTypeHelper.GetIntValue(row["是否阅读"]);
r.status = DataTypeHelper.GetStringValue(row["状态"]);
r.CompleteStatus = DataTypeHelper.GetStringValue(row["完成情况及所耗物料"]);
//r.IsSatisfying = DataTypeHelper.GetStringValue(row["是否满意"]);
//r.AffirmComplete = DataTypeHelper.GetStringValue(row["业主确认完成"]);
//r.AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(row["业主评价"]);
//r.AffirmCompleteTime = DataTypeHelper.GetDateStringValue(row["业主确认完成时间"]);
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(row["报修前照片3"]));
r.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(row["处理后照片3"]));
r.AfterImage = afterList.ToArray();
repairList.Add(r);
}
sr.status = "Success";
sr.result = "成功";
sr.data = repairList.ToArray();
}
return(sr);
}