public static StatusReport GetWatchInfo(string officeId, string date)
{
StatusReport sr = new StatusReport();
string sqlString = "select 班次ID,巡更点ID,巡更序次,开始时间 from 基础_微信巡更 " +
" where 当前组织ID = @当前组织ID and 工作日期 = @工作日期 order by ID desc";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString, new SqlParameter("@当前组织ID", officeId),
new SqlParameter("@工作日期", date));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
List <WatchInfo> watchInfoList = new List <WatchInfo>();
foreach (DataRow dr in dt.Rows)
{
WatchInfo watchInfo = new WatchInfo();
watchInfo.frequencyId = DataTypeHelper.GetIntValue(dr["班次ID"]);
watchInfo.pointId = DataTypeHelper.GetIntValue(dr["巡更点ID"]);
watchInfo.watchTimes = DataTypeHelper.GetIntValue(dr["巡更序次"]);
watchInfo.startTime = DataTypeHelper.GetDateStringValue(dr["开始时间"]);
watchInfoList.Add(watchInfo);
}
sr.status = "Success";
sr.result = "成功";
sr.data = watchInfoList.ToArray();
return(sr);
}
public static StatusReport GetLookOverInfo(string ztCode, string func, string period)
{
string condition = period == "day" ?
" where (分类 = @分类 and 巡检业务 = @巡检业务 and convert(varchar(8),工作日期,112) = convert(varchar(8),getDate(),112) and 巡检周期 = '日') " :
" where (分类 = @分类 and 巡检业务 = @巡检业务 and datediff(day,工作日期 ,getDate()) < 7 and 巡检周期 = '周')";
string sqlString =
" SELECT ID, 巡检业务, 巡检对象, 巡检项目, 巡检周期, 巡检时间 " +
" FROM dbo.基础资料_巡检记录 " +
condition +
" order by 巡检对象, 巡检项目, 巡检周期 ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString, new SqlParameter("@分类", ztCode), new SqlParameter("@巡检业务", func));
if (dt.Rows.Count == 0)
{
return(new StatusReport().SetFail());
}
List <LookOver> loList = new List <LookOver>();
foreach (DataRow dr in dt.Rows)
{
LookOver lo = new LookOver();
lo.id = DataTypeHelper.GetStringValue(dr["ID"]);
lo.business = DataTypeHelper.GetStringValue(dr["巡检业务"]);
lo.objectName = DataTypeHelper.GetStringValue(dr["巡检对象"]);
lo.item = DataTypeHelper.GetStringValue(dr["巡检项目"]);
lo.period = DataTypeHelper.GetStringValue(dr["巡检周期"]);
string time = DataTypeHelper.GetDateStringValue(dr["巡检时间"]);
lo.isLook = string.IsNullOrEmpty(time) ? false : true;
loList.Add(lo);
}
return(new StatusReport(loList.ToArray()));
}
public static StatusReport GetLookOverInfo(string ztCode, string func, string route, string name)
{
string sqlString =
" SELECT ID, 巡检业务, 巡检点, 巡检项目, 巡检时间 " +
" FROM dbo.基础资料_巡检记录 where 分类 = @分类 and 巡检业务 = @巡检业务 and 巡检路线 = @巡检路线 and 巡检人 like '%' + @巡检人 + '%' and 工作日期 = @工作日期 ";
//condition +
//" order by 巡检对象, 巡检项目, 巡检周期 ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@分类", ztCode),
new SqlParameter("@巡检业务", func),
new SqlParameter("@巡检路线", route),
new SqlParameter("@工作日期", DateTime.Now.Date),
new SqlParameter("@巡检人", name));
if (dt.Rows.Count == 0)
{
return(new StatusReport().SetFail());
}
List <LookOver> loList = new List <LookOver>();
foreach (DataRow dr in dt.Rows)
{
LookOver lo = new LookOver();
lo.id = DataTypeHelper.GetStringValue(dr["ID"]);
lo.business = DataTypeHelper.GetStringValue(dr["巡检业务"]);
lo.objectName = DataTypeHelper.GetStringValue(dr["巡检点"]);
lo.item = DataTypeHelper.GetStringValue(dr["巡检项目"]);
//lo.period = DataTypeHelper.GetStringValue(dr["巡检周期"]);
string time = DataTypeHelper.GetDateStringValue(dr["巡检时间"]);
lo.isLook = string.IsNullOrEmpty(time) ? false : true;
loList.Add(lo);
}
return(new StatusReport(loList.ToArray()));
}
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);
}
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 GetEquipmentTroubleReport(string ztcode)
{
StatusReport sr = new StatusReport();
string done = " where (left(分类,2) = @分类) " +
" AND (完成时间 is not null) " +
" AND ( DATEDIFF(hh, 接单时间, ISNULL(完成时间, '1990/1/1')) > CONVERT(int, LEFT(维修时限, LEN(维修时限) - 2))) " +
" ORDER BY ID DESC ";
string sqlstring = " SELECT ID, 设备名称, 分类, 设备编号, 发生时间, 故障描述, 状态, 维修人, 维修时限, 接单时间, " +
" 维修说明, 完成时间, 维修前照片1, 维修前照片2, 维修前照片3, 处理后照片1, 处理后照片2, 处理后照片3 " +
" 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 <EquipmentTrouble> equipmentList = new List <EquipmentTrouble>();
foreach (DataRow dr in dt.Rows)
{
EquipmentTrouble equipment = new EquipmentTrouble();
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
equipment.id = DataTypeHelper.GetIntValue(dr["ID"]);
equipment.classify = DataTypeHelper.GetStringValue(dr["分类"]);
equipment.name = DataTypeHelper.GetStringValue(dr["设备名称"]);
equipment.number = DataTypeHelper.GetStringValue(dr["设备编号"]);
equipment.brokenTime = DataTypeHelper.GetDateStringValue(dr["发生时间"]);
equipment.brokenInfo = DataTypeHelper.GetStringValue(dr["故障描述"]);
equipment.status = DataTypeHelper.GetStringValue(dr["状态"]);
equipment.repairMan = DataTypeHelper.GetStringValue(dr["维修人"]);
equipment.repairTimeLimit = DataTypeHelper.GetStringValue(dr["维修时限"]);
equipment.receiveTime = DataTypeHelper.GetDateStringValue(dr["接单时间"]);
equipment.repairInfo = DataTypeHelper.GetStringValue(dr["维修说明"]);
equipment.finishTime = DataTypeHelper.GetDateStringValue(dr["完成时间"]);
beforeList.Add(DataTypeHelper.GetStringValue(dr["维修前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["维修前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["维修前照片3"]));
equipment.beforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片3"]));
equipment.afterImage = afterList.ToArray();
equipmentList.Add(equipment);
}
sr.status = "Success";
sr.result = "成功";
sr.data = equipmentList.ToArray();
sr.parameters = sqlstring;
return(sr);
}
public static StatusReport GetPeriodsInfo()
{
StatusReport sr = new StatusReport();
string sqlstring = "SELECT TOP 1 ID, 期数, 主题, 主题描述, 开始时间, 结束时间,主办单位,参赛规则,评奖方式,注意事项 " +
" FROM dbo.基础资料_摄影比赛设置 " +
" WHERE (状态 = N'进行中') " +
" ORDER BY ID DESC";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring);
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
DataRow dr = dt.Rows[0];
PeriodsInfo pi = new PeriodsInfo();
pi.id = DataTypeHelper.GetIntValue(dr["ID"]);
pi.periods = DataTypeHelper.GetStringValue(dr["期数"]);
pi.theme = DataTypeHelper.GetStringValue(dr["主题"]);
pi.themeContent = DataTypeHelper.GetStringValue(dr["主题描述"]);
pi.startTime = DataTypeHelper.GetDateStringValue(dr["开始时间"]);
pi.endTime = DataTypeHelper.GetDateStringValue(dr["结束时间"]);
pi.sponsor = DataTypeHelper.GetStringValue(dr["主办单位"]);
pi.roles = DataTypeHelper.GetStringValue(dr["参赛规则"]);
pi.appraiseWay = DataTypeHelper.GetStringValue(dr["评奖方式"]);
pi.mattersNeedAttention = DataTypeHelper.GetStringValue(dr["注意事项"]);
sqlstring = "select 奖项,奖品,人数 from 基础资料_摄影比赛设置_奖项设置 where PID = @PID";
dt = SQLHelper.ExecuteQuery("wyt", sqlstring, new SqlParameter("@PID", pi.id));
if (dt.Rows.Count != 0)
{
List <Awards> awardList = new List <Awards>();
foreach (DataRow row in dt.Rows)
{
Awards awards = new Awards();
awards.award = DataTypeHelper.GetStringValue(row["奖项"]);
awards.prize = DataTypeHelper.GetStringValue(row["奖品"]);
awards.number = DataTypeHelper.GetIntValue(row["人数"]);
awardList.Add(awards);
}
pi.awardSetting = awardList.ToArray();
}
sr.status = "Success";
sr.result = "成功";
sr.data = pi;
return(sr);
}
public static StatusReport GetDelivery(string ztcode, string phone)
{
StatusReport sr = new StatusReport();
string sqlString = " SELECT ID, 分类, 收件人地址, 收件人, 联系电话, 物品描述, 送件人, 送件时间, 领取人, 领取人电话,领取时间, 登记人, 登记时间, 状态 " +
" FROM dbo.基础资料_代收管理 " +
" WHERE (分类 = @分类) AND (联系电话 = @联系电话) " +
" ORDER BY ID DESC";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@分类", ztcode),
new SqlParameter("@联系电话", phone));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "无数据";
}
else
{
List <Delivery> deliveryList = new List <Delivery>();
foreach (DataRow dr in dt.Rows)
{
Delivery delivery = new Delivery()
{
Classify = DataTypeHelper.GetStringValue(dr["分类"]),
Address = DataTypeHelper.GetStringValue(dr["收件人地址"]),
GoodsInfo = DataTypeHelper.GetStringValue(dr["物品描述"]),
Addressee = DataTypeHelper.GetStringValue(dr["收件人"]),
AddresseePhone = DataTypeHelper.GetStringValue(dr["联系电话"]),
Deliver = DataTypeHelper.GetStringValue(dr["送件人"]),
DeliveryTime = DataTypeHelper.GetDateStringValue(dr["送件时间"]),
Receiver = DataTypeHelper.GetStringValue(dr["领取人"]),
ReceiverPhone = DataTypeHelper.GetStringValue(dr["领取人电话"]),
ReceiveTime = DataTypeHelper.GetDateStringValue(dr["领取时间"]),
Registrant = DataTypeHelper.GetStringValue(dr["登记人"]),
RegisterTime = DataTypeHelper.GetDateStringValue(dr["登记时间"]),
Status = DataTypeHelper.GetStringValue(dr["状态"]),
};
deliveryList.Add(delivery);
}
sr.status = "Success";
sr.result = "成功";
sr.data = deliveryList.ToArray();
}
return(sr);
}
private static ComplainReport[] GetComplains(DataTable dt)
{
List <ComplainReport> complainList = new List <ComplainReport>();
foreach (DataRow dr in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
ComplainReport complaint = new ComplainReport()
{
Id = DataTypeHelper.GetIntValue(dr["ID"]),
ReceptionDate = DataTypeHelper.GetDateStringValue(dr["投诉接待时间"]),
Way = DataTypeHelper.GetStringValue(dr["投诉方式"]),
Name = DataTypeHelper.GetStringValue(dr["投诉人姓名"]),
Address = DataTypeHelper.GetStringValue(dr["地址"]),
Content = DataTypeHelper.GetStringValue(dr["投诉内容"]),
Phone = DataTypeHelper.GetStringValue(dr["联系电话"]),
Number = DataTypeHelper.GetStringValue(dr["投诉处理单编号"]),
FinishDate = DataTypeHelper.GetDateStringValue(dr["处理完成日期"]),
FinishStatus = DataTypeHelper.GetStringValue(dr["处理完成情况"]),
Registrant = DataTypeHelper.GetStringValue(dr["登记人"]),
Department = DataTypeHelper.GetStringValue(dr["责任部门"]),
Status = DataTypeHelper.GetStringValue(dr["状态"]),
IsSatisfying = DataTypeHelper.GetStringValue(dr["是否满意"]),
AffirmComplete = DataTypeHelper.GetStringValue(dr["业主确认解决"]),
AffirmCompleteTime = DataTypeHelper.GetDateStringValue(dr["确认时间"]),
AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(dr["业主评价"]),
SendPerson = DataTypeHelper.GetStringValue(dr["发单人"]),
ReceivePerson = DataTypeHelper.GetStringValue(dr["接单人"]),
ZTCode = DataTypeHelper.GetStringValue(dr["帐套代码"]),
ZTName = DataTypeHelper.GetStringValue(dr["帐套名称"]),
type = DataTypeHelper.GetStringValue(dr["上报原因"])
};
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉前照片3"]));
complaint.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片3"]));
complaint.AfterImage = afterList.ToArray();
complainList.Add(complaint);
}
return(complainList.ToArray());
}
public static StatusReport GetAdvise(string ztName, string phone)
{
StatusReport sr = new StatusReport();
string sqlstring = " select ID,分类,姓名,联系电话,地址,表扬建议,内容,提交时间,照片1,照片2,照片3 " +
" from 基础资料_表扬建议管理 " +
" where 分类 = @分类 and 联系电话 = @联系电话" +
" order by ID desc ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring,
new SqlParameter("@分类", ztName),
new SqlParameter("@联系电话", phone));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
List <Advise> adviseList = new List <Advise>();
foreach (DataRow dr in dt.Rows)
{
List <string> imageList = new List <string>();
Advise advise = new Advise();
advise.Id = DataTypeHelper.GetIntValue(dr["ID"]);
advise.classify = DataTypeHelper.GetStringValue(dr["分类"]);
advise.Name = DataTypeHelper.GetStringValue(dr["姓名"]);
advise.Phone = DataTypeHelper.GetStringValue(dr["联系电话"]);
advise.RoomNumber = DataTypeHelper.GetStringValue(dr["地址"]);
advise.Type = DataTypeHelper.GetStringValue(dr["表扬建议"]);
advise.Content = DataTypeHelper.GetStringValue(dr["内容"]);
advise.SubmitDate = DataTypeHelper.GetDateStringValue(dr["提交时间"]);
imageList.Add(DataTypeHelper.GetStringValue(dr["照片1"]));
imageList.Add(DataTypeHelper.GetStringValue(dr["照片2"]));
imageList.Add(DataTypeHelper.GetStringValue(dr["照片3"]));
advise.Image = imageList.ToArray();
adviseList.Add(advise);
}
sr.status = "Success";
sr.result = "成功";
sr.data = adviseList.ToArray();
return(sr);
}
public static StatusReport GetFamilyMembers(int id)
{
StatusReport sr = new StatusReport();
string sqlString = " select ID,PID,姓名,性别,出生日期,身份证件名称,身份证件号码, " +
" 国籍或地区,与登记占用者关系,工作单位,联系电话 " +
" from 占用资料_占用人员详情 " +
" where PID = (select ID from 占用资料 where 占用者ID = @占用者ID) ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString, new SqlParameter("@占用者ID", id));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到符合条件的记录";
return(sr);
}
List <FamilyMember> fmList = new List <FamilyMember>();
foreach (DataRow dr in dt.Rows)
{
FamilyMember fm = new FamilyMember()
{
id = DataTypeHelper.GetIntValue(dr["ID"]),
pid = DataTypeHelper.GetIntValue(dr["PID"]),
name = DataTypeHelper.GetStringValue(dr["姓名"]),
gender = DataTypeHelper.GetStringValue(dr["性别"]),
birth = DataTypeHelper.GetDateStringValue(dr["出生日期"]),
idType = DataTypeHelper.GetStringValue(dr["身份证件名称"]),
idNumber = DataTypeHelper.GetStringValue(dr["身份证件号码"]),
nation = DataTypeHelper.GetStringValue(dr["国籍或地区"]),
relation = DataTypeHelper.GetStringValue(dr["与登记占用者关系"]),
company = DataTypeHelper.GetStringValue(dr["工作单位"]),
phone = DataTypeHelper.GetStringValue(dr["联系电话"])
};
fmList.Add(fm);
}
sr.status = "Success";
sr.result = "成功";
sr.data = fmList.ToArray();
return(sr);
}
public static StatusReport GetSignupDetail(string kindergartenName, string shareId, string receiverId)
{
StatusReport sr = new StatusReport();
string dbName = kindergartenName == "松园幼儿园" ? "localsy" : "localyd";
string sqlString = " select 姓名,监护人,与幼儿关系,书包电话,报名日期,是否已交费 " +
" from 小程序_分享情况 " +
" where 姓名 is not null and 发单人ID = " + shareId + "and 接单人ID = " + receiverId;
DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString);
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到分享详情记录";
return(sr);
}
List <signupDetail> tempList = new List <signupDetail>();
foreach (DataRow dr in dt.Rows)
{
signupDetail detailTemp = new signupDetail()
{
name = DataTypeHelper.GetStringValue(dr["姓名"]).ToString(),
phone = DataTypeHelper.GetStringValue(dr["书包电话"]),
relateName = DataTypeHelper.GetStringValue(dr["监护人"]),
relation = DataTypeHelper.GetStringValue(dr["与幼儿关系"]),
signuptime = DataTypeHelper.GetDateStringValue(dr["报名日期"]),
isCharge = DataTypeHelper.GetStringValue(dr["是否已交费"]),
};
tempList.Add(detailTemp);
}
signupDetail[] tempArr = tempList.ToArray();
sr.status = "Success";
sr.result = "成功";
sr.data = tempArr;
return(sr);
}
/// <summary>
/// 通过姓名和联系电话查找看园信息
/// </summary>
/// <param name="kindergartenName">幼儿园名称</param>
/// <param name="name">姓名</param>
/// <param name="phoneNumber">联系电话</param>
/// <returns></returns>
//public static StatusReport GetKanyuanData(string kindergartenName, string name, string phoneNumber)
//{
// StatusReport sr = new StatusReport();
// //根据幼儿园名称选择不同的数据库
// string dbName = kindergartenName == "松园幼儿园" ? "wyt" : "ydal";
// string sqlString = " select ID,姓名,性别,出生年月日,家长姓名,家长与幼儿关系,联系电话,家庭住址,是否上过幼儿园, " +
// " 家长性别,入托意愿,计划入学时间,是否政府摇号,是否预约看园,预约看园时间," +
// " 是否有接待人,接待人姓名,是否校车接送 " +
// " from 基础_看园管理 where 姓名=@姓名 and 联系电话= @联系电话 ";
// //在数据库中查询匹配的看园数据
// DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString, new SqlParameter("@姓名", GetDBValue(name)),
// new SqlParameter("@联系电话", GetDBValue(phoneNumber)));
// //如果未查询到数据，返回错误信息
// if (dt.Rows.Count == 0)
// {
// sr.status = "Fail";
// sr.result = "未查询到任何数据";
// return sr;
// }
// //获取查询到的数据，并用以初始化kyData
// DataRow dr = dt.Rows[0];
// KanyuanData kyData = new KanyuanData();
// kyData.id = DataTypeHelper.GetIntValue(dr["ID"]);
// kyData.name = DataTypeHelper.GetStringValue(dr["姓名"]);
// kyData.gender = DataTypeHelper.GetStringValue(dr["性别"]);
// kyData.birth = DataTypeHelper.GetStringValue(dr["出生年月日"]);
// kyData.relateName = DataTypeHelper.GetStringValue(dr["家长姓名"]);
// kyData.relation = DataTypeHelper.GetStringValue(dr["家长与幼儿关系"]);
// kyData.phoneNumber = DataTypeHelper.GetStringValue(dr["联系电话"]);
// kyData.address = DataTypeHelper.GetStringValue(dr["家庭住址"]);
// kyData.isYoueryuan = DataTypeHelper.GetStringValue(dr["是否上过幼儿园"]);
// kyData.relateGender = DataTypeHelper.GetStringValue(dr["家长性别"]);
// kyData.desire = DataTypeHelper.GetStringValue(dr["入托意愿"]);
// kyData.ruyuanDate = DataTypeHelper.GetStringValue(dr["计划入学时间"]);
// kyData.joinLottery = DataTypeHelper.GetStringValue(dr["是否政府摇号"]);
// kyData.isAppointment = DataTypeHelper.GetStringValue(dr["是否预约看园"]);
// kyData.appointmentDate = DataTypeHelper.GetStringValue(dr["预约看园时间"]);
// kyData.haveReceiver = DataTypeHelper.GetStringValue(dr["是否有接待人"]);
// kyData.receiverName = DataTypeHelper.GetStringValue(dr["接待人姓名"]);
// kyData.needSchoolBus = DataTypeHelper.GetStringValue(dr["是否校车接送"]);
// //返回查询到的数据
// sr.status = "Success";
// sr.result = "成功";
// sr.data = kyData;
// return sr;
//}
public static StatusReport GetKanyuanData(string kindergartenName, string openid)
{
StatusReport sr = new StatusReport();
//根据幼儿园名称选择不同的数据库
string dbName = kindergartenName == "松园幼儿园" ? "localsy" : "localyd";
//string sqlString = " select ID,姓名,性别,出生年月日,家长姓名,家长与幼儿关系,联系电话,家庭住址,是否上过幼儿园, " +
// " 家长性别,入托意愿,计划入学时间,是否政府摇号,是否预约看园,预约看园时间," +
// " 是否有接待人,接待人姓名,是否校车接送 " +
// " from 基础_看园管理 where 姓名=@姓名 and 联系电话= @联系电话 ";
//在数据库中查询匹配的看园数据
//DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString, new SqlParameter("@姓名", GetDBValue(name)),
//new SqlParameter("@联系电话", GetDBValue(phoneNumber)));
string sqlString = " select ID,姓名,性别,出生年月日,家长姓名,家长与幼儿关系,联系电话,家庭住址,是否上过幼儿园, " +
" 家长性别,入托意愿,计划入学时间,是否政府摇号,是否预约看园,预约看园时间," +
" 是否有接待人,接待人姓名,是否校车接送 " +
" from 基础_看园管理 where openid = @openid " +
" order by ID desc ";
//在数据库中查询匹配的看园数据
DataTable dt = SQLHelper.ExecuteQuery(dbName, sqlString, new SqlParameter("@openid", GetDBValue(openid)));
//如果未查询到数据，返回错误信息
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
return(sr);
}
//获取查询到的数据，并用以初始化kyData
DataRow dr = dt.Rows[0];
KanyuanData kyData = new KanyuanData();
kyData.id = DataTypeHelper.GetIntValue(dr["ID"]);
kyData.name = DataTypeHelper.GetStringValue(dr["姓名"]);
kyData.gender = DataTypeHelper.GetStringValue(dr["性别"]);
kyData.birth = DataTypeHelper.GetDateStringValue(dr["出生年月日"]);
kyData.relateName = DataTypeHelper.GetStringValue(dr["家长姓名"]);
kyData.relation = DataTypeHelper.GetStringValue(dr["家长与幼儿关系"]);
kyData.phoneNumber = DataTypeHelper.GetStringValue(dr["联系电话"]);
kyData.address = DataTypeHelper.GetStringValue(dr["家庭住址"]);
kyData.isYoueryuan = DataTypeHelper.GetStringValue(dr["是否上过幼儿园"]);
kyData.relateGender = DataTypeHelper.GetStringValue(dr["家长性别"]);
kyData.desire = DataTypeHelper.GetStringValue(dr["入托意愿"]);
kyData.ruyuanDate = DataTypeHelper.GetDateStringValue(dr["计划入学时间"]);
kyData.joinLottery = DataTypeHelper.GetStringValue(dr["是否政府摇号"]);
kyData.isAppointment = DataTypeHelper.GetStringValue(dr["是否预约看园"]);
kyData.appointmentDate = DataTypeHelper.GetDateStringValue(dr["预约看园时间"]);
kyData.haveReceiver = DataTypeHelper.GetStringValue(dr["是否有接待人"]);
kyData.receiverName = DataTypeHelper.GetStringValue(dr["接待人姓名"]);
kyData.needSchoolBus = DataTypeHelper.GetStringValue(dr["是否校车接送"]);
//返回查询到的数据
sr.status = "Success";
sr.result = "成功";
sr.data = kyData;
return(sr);
}
public static StatusReport GetComplaintList(string classify, string name, string phone)
{
StatusReport sr = new StatusReport()
{
status = "Success",
result = "成功",
};
string sqlString = " select ID,投诉接待时间,投诉方式,投诉人姓名,地址,投诉内容,联系电话,投诉处理单编号,处理完成日期,处理完成情况,登记人,责任部门," +
" 投诉前照片1,投诉前照片2,投诉前照片3,处理后照片1,处理后照片2,处理后照片3,状态,业主确认解决,确认时间,是否满意,业主评价,不受理原因 " +
" from 基础资料_顾客投诉处理登记表 " +
" where 分类 = @分类 and 投诉人姓名 = @投诉人姓名 and 联系电话 = @联系电话 " +
" order by ID desc ";
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@分类", classify),
new SqlParameter("@投诉人姓名", name),
new SqlParameter("@联系电话", phone));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "查询条件错误或没有数据";
return(sr);
}
List <Complaint> complaintList = new List <Complaint>();
foreach (DataRow dr in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
Complaint complaint = new Complaint()
{
Id = DataTypeHelper.GetIntValue(dr["ID"]),
ReceptionDate = DataTypeHelper.GetDateStringValue(dr["投诉接待时间"]),
Way = DataTypeHelper.GetStringValue(dr["投诉方式"]),
Name = DataTypeHelper.GetStringValue(dr["投诉人姓名"]),
Address = DataTypeHelper.GetStringValue(dr["地址"]),
Content = DataTypeHelper.GetStringValue(dr["投诉内容"]),
Phone = DataTypeHelper.GetStringValue(dr["联系电话"]),
Number = DataTypeHelper.GetStringValue(dr["投诉处理单编号"]),
FinishDate = DataTypeHelper.GetDateStringValue(dr["处理完成日期"]),
FinishStatus = DataTypeHelper.GetStringValue(dr["处理完成情况"]),
Registrant = DataTypeHelper.GetStringValue(dr["登记人"]),
Department = DataTypeHelper.GetStringValue(dr["责任部门"]),
Status = DataTypeHelper.GetStringValue(dr["状态"]),
IsSatisfying = DataTypeHelper.GetStringValue(dr["是否满意"]),
AffirmComplete = DataTypeHelper.GetStringValue(dr["业主确认解决"]),
AffirmCompleteTime = DataTypeHelper.GetDateStringValue(dr["确认时间"]),
AffirmCompleteEvaluation = DataTypeHelper.GetStringValue(dr["业主评价"]),
CaseDeclinedReason = DataTypeHelper.GetStringValue(dr["不受理原因"])
};
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉前照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉前照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉前照片3"]));
complaint.BeforeImage = beforeList.ToArray();
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片3"]));
complaint.AfterImage = afterList.ToArray();
complaintList.Add(complaint);
}
sr.data = complaintList.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);
}
/// <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 GetComplaintList(string classify, string status, string name)
{
StatusReport sr = new StatusReport()
{
status = "Success",
result = "成功",
};
string condition = "";
if (status == "未完成")
{
condition = " where 分类 = @分类 and 接单人 = @接单人 and (not(状态 = '已完成') and not(状态 = '无效投诉')) " +
" order by ID desc ";
}
else
{
condition = " where 分类 = @分类 and 接单人 = @接单人 and 状态 = '已完成' " +
" order by ID desc ";
}
string sqlString = " select top 100 ID,投诉接待时间,投诉方式,投诉人姓名,地址,投诉内容,联系电话,投诉处理单编号,处理完成时间,处理完成情况,登记人,责任部门," +
" 投诉照片1,投诉照片2,投诉照片3,处理后照片1,处理后照片2,处理后照片3,状态,发单人,发单时间,接单人 " +
" from 基础资料_顾客投诉处理登记表 ";
sqlString += condition;
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlString,
new SqlParameter("@分类", classify),
new SqlParameter("@接单人", name));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "查询条件错误或没有数据";
return(sr);
}
List <object> complainList = new List <object>();
foreach (DataRow dr in dt.Rows)
{
List <string> beforeList = new List <string>();
List <string> afterList = new List <string>();
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉照片1"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉照片2"]));
beforeList.Add(DataTypeHelper.GetStringValue(dr["投诉照片3"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片1"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片2"]));
afterList.Add(DataTypeHelper.GetStringValue(dr["处理后照片3"]));
var complaint = new
{
Id = DataTypeHelper.GetIntValue(dr["ID"]),
ReceptionDate = DataTypeHelper.GetDateStringValue(dr["投诉接待时间"]),
Way = DataTypeHelper.GetStringValue(dr["投诉方式"]),
Name = DataTypeHelper.GetStringValue(dr["投诉人姓名"]),
Address = DataTypeHelper.GetStringValue(dr["地址"]),
Content = DataTypeHelper.GetStringValue(dr["投诉内容"]),
Phone = DataTypeHelper.GetStringValue(dr["联系电话"]),
Number = DataTypeHelper.GetStringValue(dr["投诉处理单编号"]),
FinishDate = DataTypeHelper.GetDateStringValue(dr["处理完成时间"]),
FinishStatus = DataTypeHelper.GetStringValue(dr["处理完成情况"]),
Registrant = DataTypeHelper.GetStringValue(dr["登记人"]),
Department = DataTypeHelper.GetStringValue(dr["责任部门"]),
Sender = DataTypeHelper.GetStringValue(dr["发单人"]),
SendTime = DataTypeHelper.GetDateStringValue(dr["发单时间"]),
Receiver = DataTypeHelper.GetStringValue(dr["接单人"]),
Status = DataTypeHelper.GetStringValue(dr["状态"]),
BeforeImage = beforeList.ToArray(),
AfterImage = afterList.ToArray()
};
complainList.Add(complaint);
}
sr.data = complainList.ToArray();
return(sr);
}
public static StatusReport GetPictures(string periodId, string sortType)
{
StatusReport sr = new StatusReport();
string sort = "";
if (sortType == "normal")
{
sort = "order by ID";
}
else if (sortType == "new")
{
sort = "order by ID desc";
}
else if (sortType == "best")
{
sort = "order by 得票数 Desc";
}
else
{
sort = "order by 点击率 Desc";
}
string sqlstring = " select ID,openid,昵称,手机号,上传时间,得票数,期数ID,照片,照片名,描述,点击率 " +
" from 基础资料_摄影比赛 " +
" where 期数ID = @期数ID ";
sqlstring += sort;
DataTable dt = SQLHelper.ExecuteQuery("wyt", sqlstring,
new SqlParameter("@期数ID", periodId));
if (dt.Rows.Count == 0)
{
sr.status = "Fail";
sr.result = "未查询到任何数据";
sr.parameters = sqlstring;
return(sr);
}
List <Picture> picList = new List <Picture>();
foreach (DataRow dr in dt.Rows)
{
Picture pic = new Picture();
pic.id = DataTypeHelper.GetIntValue(dr["ID"]);
pic.openid = DataTypeHelper.GetStringValue(dr["openid"]);
pic.nackName = DataTypeHelper.GetStringValue(dr["昵称"]);
pic.phone = DataTypeHelper.GetStringValue(dr["手机号"]);
pic.uploadTime = DataTypeHelper.GetDateStringValue(dr["上传时间"]);
pic.periodId = DataTypeHelper.GetIntValue(dr["期数ID"]);
pic.vote = DataTypeHelper.GetIntValue(dr["得票数"]);
pic.picPath = DataTypeHelper.GetStringValue(dr["照片"]);
pic.picName = DataTypeHelper.GetStringValue(dr["照片名"]);
pic.description = DataTypeHelper.GetStringValue(dr["描述"]);
pic.rate = DataTypeHelper.GetIntValue(dr["点击率"]);
picList.Add(pic);
}
sr.status = "Success";
sr.result = "成功";
sr.data = picList.ToArray();
sr.parameters = sqlstring;
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);
}
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);
}
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);
}
{ /// <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);
}
public static StatusReport BussinessHandler_TableData(string userId, string linkId, string docTableName, string docTableId, string businessId, string transferObjectId, string objectType, string transferObjectType)
{
string sqlString = "[sp_extcall_业务任务处理_表单数据]";
SqlParameter spUserId = new SqlParameter("@用户ID", SqlDbType.Int);
SqlParameter spLinkId = new SqlParameter("@环节ID", SqlDbType.Int);
SqlParameter spDocTableName = new SqlParameter("@文档表名称", SqlDbType.NVarChar);
SqlParameter spDocTableId = new SqlParameter("@文档表ID", SqlDbType.Int);
//spUserId.Value = 514;
//spLinkId.Value = 132;
//spDocTableName.Value = "业务文档_请款单流程表";
//spDocTableId.Value = 619;
spUserId.Value = Convert.ToInt32(userId);
spLinkId.Value = Convert.ToInt32(linkId);
spDocTableName.Value = docTableName;
spDocTableId.Value = Convert.ToInt32(docTableId);
DataSet ds = SQLHelper.ExecuteProcedure("wyt", sqlString, spUserId, spLinkId, spDocTableName, spDocTableId);
DataTable dtItem = ds.Tables[0];
DataTable dtDefine = ds.Tables[1];
List <object> itemList = new List <object>();
foreach (DataRow dr in dtItem.Rows)
{
List <object> columnList = new List <object>();
foreach (DataColumn dc in dtItem.Columns)
{
string columnName = Convert.ToString(dc.ColumnName);
object content = null;
if (columnName.Substring(columnName.Length - 2) == "时间" || columnName.Substring(columnName.Length - 2) == "日期")
{
content = DataTypeHelper.GetDateStringValue(dr[columnName]);
}
//else if (columnName.Contains("附件"))
//{
// string columnValue = DataTypeHelper.GetStringValue(dr[columnName]);//获取对应列的值
// if (columnValue.Contains("-") && columnValue.Contains("|"))//如果值中存在-和|，执行以下操作
// {
// string docValue = columnValue.Split('-')[columnValue.Split('-').Length - 1];//获取最后一个 “-” 后的所有字符
// string[] splitArr = docValue.Split('|');
// string fileName = splitArr[splitArr.Length - 1];
// string recordId = splitArr[splitArr.Length - 2];
// content = docTableName + "\\" + recordId + "\\" + fileName;
// }
// else
// {
// content = "";
// }
//}
else
{
content = dr[columnName];
}
var item = new { title = columnName, content = content };
columnList.Add(item);
}
itemList.Add(columnList.ToArray());
}
List <object> defineList = new List <object>();
foreach (DataRow dr in dtDefine.Rows)
{
//List<object> columnList = new List<object>();
//foreach(DataColumn dc in dtDefine.Columns)
//{
// string columnName = Convert.ToString(dc.ColumnName);
// var item = new { title = columnName, content = dr[columnName] };
// columnList.Add(item);
//}
//defineList.Add(columnList.ToArray());
var item = new
{
fieldName = DataTypeHelper.GetStringValue(dr["字段名称"]),
fieldSourceName = DataTypeHelper.GetStringValue(dr["字段源名称"]),
required = DataTypeHelper.GetBooleanValue(dr["必填列"]),
defaultValue = DataTypeHelper.GetStringValue(dr["默认写入值"]),
allowEdit = DataTypeHelper.GetBooleanValue(dr["允许编辑"]),
isFile = DataTypeHelper.GetBooleanValue(dr["是否文件字段"])
};
defineList.Add(item);
}
StatusReport sr = new StatusReport();
sr.status = "Success";
sr.result = "Success";
if (transferObjectType == "Decision")
{
sr.data = new { items = itemList.ToArray(), defines = defineList.ToArray(), checkDataDefines = BussinessHandler_CheckData(Convert.ToInt32(businessId), Convert.ToInt32(transferObjectId), docTableName, Convert.ToInt32(docTableId)) };
}
if (objectType == "Terminator")
{
sr.data = new { items = itemList.ToArray(), defines = defineList.ToArray() };
}
//var data = new { items = itemList.ToArray(), defines = defineList.ToArray(), checkDataDefines = BussinessHandler_CheckData(Convert.ToInt32(businessId),Convert.ToInt32(transferObjectId), docTableName) };
//StatusReport sr = new StatusReport();
//sr.status = "Success";
//sr.result = "Success";
//sr.data = data;
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 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);
}
/// <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);
}
