public string GetData(string userid)
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(@"SELECT
DeviceID ,A.MeetingRoomID ,DeviceName ,A.Status ,A.Remark,
B.MeetingRoomName,(CASE A.Status WHEN 0 THEN '正常' ELSE '损坏' END) AS StatusText
FROM
B_OA_Device A
INNER JOIN B_OA_MeetingRoom B ON B.MeetingRoomID = A.MeetingRoomID
ORDER BY B.MeetingRoomName");
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString());
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<B_OA_Device>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Device>));
return JsonConvert.SerializeObject( dataModel);
}
public string GetData(string userid)
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(@"SELECT MeetingRoomID ,
Layer ,
MeetingRoomName ,
Number ,
(CASE Status WHEN 0 THEN '启用' ELSE '停用' END) AS StatusText,ISNULL(STUFF((SELECT '、' + B.DeviceName FROM
(SELECT MeetingRoomID,DeviceName
FROM B_OA_Device WHERE MeetingRoomID = A.MeetingRoomID)B
FOR XML PATH ('')),1,1,''),'无') AS Device,Remark,Status
FROM B_OA_MeetingRoom A ORDER BY MeetingRoomName
");
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString());
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<B_OA_MeetingRoom>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_MeetingRoom>));
return JsonConvert.SerializeObject( dataModel);
}
public string GetLawRegulationsData(string content)
{
try
{
var data = new GetDataModel();
Para_LawRegulations en = new Para_LawRegulations();
if (!string.IsNullOrWhiteSpace(content))
{
en.Condition.Add("mc=" + content);
}
data.sourceList = Utility.Database.QueryList<Para_LawRegulations>(en);// 实体
//JsonConvert.DeserializeObject()
data.sourceListEdit = new Para_LawRegulations();
//data.sourceListEdit.ID = data.baseInfo.PK;
return Utility.JsonResult(true, null, data);
}
catch (Exception ex)
{
ComBase.Logger(ex);
return Utility.JsonResult(false, ex.Message);
}
}
public string LoadData(string userid)
{
GetDataModel data = new GetDataModel();
B_OA_MeetingRoom list = new B_OA_MeetingRoom();
data.dataList = Utility.Database.QueryList(list);
return JsonConvert.SerializeObject( data);
}
public string LoadData(string userid)
{
//DataSet dataSet = null;
//SkyLandDeveloper developer = SkyLandDeveloper.FromJson("{}");//获取业务流相关参数
try
{
GetDataModel data = new GetDataModel();
Para_OA_CarInfo list = new Para_OA_CarInfo();
data.dataList = Utility.Database.QueryList(list);
return JsonConvert.SerializeObject( data);
}
catch (Exception ex)
{
return Utility.JsonMsg(false, ex.Message);
}
}
public string GetData(string userid)
{
StringBuilder strSql = new StringBuilder();
try
{
GetDataModel dataModel = new GetDataModel();
strSql.Append(@"SELECT
id, cph, zdlc, sjlc, whlc, sfky,(CASE sfky WHEN 0 THEN '正常' ELSE '停用' END) AS sfkyText, zt, carType, carBrand, carModel, carEngine, chassis,
color, loadWeight, seatCount, price,CONVERT(VARCHAR(10),proDate,120) AS proDate, CONVERT(VARCHAR(10),buyDate,120) AS buyDate, belongTo, remark, recordDate, recordMan, Driver,
B.CnName AS driverName
FROM
Para_OA_CarInfo A
LEFT JOIN FX_UserInfo B ON A.Driver = B.UserID
ORDER BY sfky ASC");
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString());
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<Para_OA_CarInfo>)JsonConvert.DeserializeObject(jsonData, typeof(List<Para_OA_CarInfo>));
return JsonConvert.SerializeObject( dataModel);
}
catch (Exception ex)
{
return Utility.JsonMsg(false, ex.Message);
}
}
public string LoadData(string userid)
{
GetDataModel data = new GetDataModel();
B_OA_MeetingRoom list = new B_OA_MeetingRoom();
data.dataList = Utility.Database.QueryList(list);
return Utility.JsonResult(true, null, data);
}
public string SearchDate(string startTime, string endTime, string userid)
{
IDbTransaction tran = Utility.Database.BeginDbTransaction();
try
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(String.Format(@"
select userInfor.CnName as OrganizerName,dp.DPName,room.MeetingRoomName,* from B_OA_Meeting as meeting
LEFT JOIN FX_UserInfo as userInfor on meeting.OrganizID = userInfor.UserID
LEFT JOIN FX_Department as dp on meeting.DpnameID = dp.DPID
LEFT JOIN B_OA_MeetingRoom as room on room.MeetingRoomID = meeting.MeetingRoomID
WHERE
meeting.StartTime >= '{0}'
AND meeting.EndTime <= '{1}'
ORDER BY meeting.MeetingID DESC
", startTime, endTime));
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString(), tran);
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<B_OA_Meeting>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Meeting>));
Utility.Database.Commit(tran);
return JsonConvert.SerializeObject(dataModel);
}
catch (Exception ex)
{
ComBase.Logger(ex);
return Utility.JsonMsg(false, "读取失败:" + ex.Message.Replace(":", " "));
}
}
public string QueryMyMeetings(string userid)
{
IDbTransaction tran = Utility.Database.BeginDbTransaction();
try
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(String.Format(@"SELECT
A.MeetingID ,A.MeetingName ,CONVERT(VARCHAR(10),A.MeetingDate,120) AS MeetingDate,CONVERT(VARCHAR(16),A.StartTime,120) AS StartTime ,
CONVERT(VARCHAR(16),A.EndTime,120) AS EndTime ,SUBSTRING(CONVERT(VARCHAR(16),A.StartTime,120),6,11) AS sStartTime ,
SUBSTRING(CONVERT(VARCHAR(16),A.EndTime,120),6,11) AS sEndTime,A.CaseID,A.NeedDevice,A.Purpose,A.Remark,SUBSTRING(CONVERT(VARCHAR(16),ApprovalTime,120),6,11) AS ApprovalTime
FROM
B_OA_Meeting A
LEFT JOIN B_OA_Participant E ON E.MeetingID = A.MeetingID
WHERE
E.UserID = '{0}'
AND A.STATUS = 1
AND A.StartTime > GETDATE()
ORDER BY A.MeetingID DESC
", userid));
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString(), tran);
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<B_OA_Meeting>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Meeting>));
return JsonConvert.SerializeObject(dataModel);
}
catch (Exception ex)
{
Utility.Database.Rollback(tran);
ComBase.Logger(ex);
return Utility.JsonMsg(false, "发送失败:" + ex.Message.Replace(":", " "));
}
}
public B_OA_Meeting GetMeetingByCaseId(string caseId, IDbTransaction tran)
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(String.Format(@"
select userInfor.CnName as OrganizerName,dp.DPName,* from B_OA_Meeting as meeting
LEFT JOIN FX_UserInfo as userInfor on meeting.OrganizID = userInfor.UserID
LEFT JOIN FX_Department as dp on meeting.DpnameID = dp.DPID
where meeting.CaseID = '{0}'
", caseId));
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString(), tran);
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
List<B_OA_Meeting> dataList = (List<B_OA_Meeting>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Meeting>));
return dataList[0];
}
public string GetData(string userid, string caseId, string baid, string actid)
{
//只有待办箱才有设置为已读
if (!String.IsNullOrEmpty(actid))
{
//只有待办箱才有设置为已读
if (!String.IsNullOrEmpty(baid)) engineAPI.SetIsReaded(caseId, baid, userid);
}
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
IDbTransaction tran = Utility.Database.BeginDbTransaction();
try
{
strSql.Append(String.Format(@"
select userInfor.CnName as OrganizerName,dp.DPName,* from B_OA_Meeting as meeting
LEFT JOIN FX_UserInfo as userInfor on meeting.OrganizID = userInfor.UserID
LEFT JOIN FX_Department as dp on meeting.DpnameID = dp.DPID
where meeting.CaseID = '{0}'
", caseId));
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString(), tran);
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<B_OA_Meeting>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Meeting>));
mPrintTable = dataSet.Tables[0];
if (caseId == "")
{
dataModel.baseInfo = new B_OA_Meeting();
dataModel.baseInfo.OrganizID = userid;
dataModel.baseInfo.OrganizerName = ComClass.GetUserInfo(userid).CnName;
dataModel.baseInfo.DpnameID = ComClass.GetUserInfo(userid).DPID;
dataModel.baseInfo.Dpname = ComClass.GetDeptByUserId(userid).DPName;
}
else
dataModel.baseInfo = dataModel.dataList[0];
Utility.Database.Commit(tran);
return JsonConvert.SerializeObject(dataModel);
}
catch (Exception ex)
{
ComBase.Logger(ex);
return Utility.JsonMsg(false, "读取失败:" + ex.Message.Replace(":", " "));
}
}
public string SearchDate(string startTime, string endTime, string userid)
{
IDbTransaction tran = Utility.Database.BeginDbTransaction();
try
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(String.Format(@"SELECT
A.MeetingID ,A.MeetingRoomID ,OrganizID ,A.Phone ,MeetingName ,Presenter ,CONVERT(VARCHAR(10),MeetingDate,120) AS MeetingDate,CONVERT(VARCHAR(16),StartTime,120) AS StartTime ,
CONVERT(VARCHAR(16),EndTime,120) AS EndTime ,SUBSTRING(CONVERT(VARCHAR(16),StartTime,120),6,11) AS sStartTime ,SUBSTRING(CONVERT(VARCHAR(16),EndTime,120),6,11) AS sEndTime,
A.Number ,(CASE A.STATUS WHEN 0 THEN '待审批' WHEN 1 THEN '已审批' END) AS StatusText,A.STATUS,
A.Remark ,Applicant ,CreatTime ,Approver ,ApprovalTime,B.MeetingRoomName,ISNULL(STUFF((SELECT '、' + B.DeviceName FROM
(SELECT MeetingRoomID,DeviceName
FROM B_OA_Device WHERE MeetingRoomID = A.MeetingRoomID)B
FOR XML PATH ('')),1,1,''),'无') AS Device,A.ParticipantName,B.Number AS MaxNumber,CaseID,NeedDevice,Purpose,C.CnName AS OrganizerName,D.FullName AS Dpname,DpnameID,
ISNULL(STUFF((SELECT ';' + E.UserID FROM
(SELECT MeetingID,UserID
FROM B_OA_Participant WHERE MeetingID = A.MeetingID)E
FOR XML PATH ('')),1,1,''),'') AS ParticipantNameid,
ISNULL(STUFF((SELECT ';' + E.CnName FROM
(SELECT MeetingID,CnName
FROM B_OA_Participant F
LEFT JOIN FX_UserInfo G ON F.UserID = G.UserID
WHERE MeetingID = A.MeetingID)E
FOR XML PATH ('')),1,1,''),'') AS ParticipantNames
FROM
B_OA_Meeting A
LEFT JOIN B_OA_MeetingRoom B ON B.MeetingRoomID = A.MeetingRoomID
LEFT JOIN FX_UserInfo C ON A.OrganizID = C. UserID
LEFT JOIN FX_Department D ON A.DpnameID = D.DPID
WHERE
StartTime >= '{0}'
AND EndTime <= '{1}'
ORDER BY A.MeetingID DESC
", startTime, endTime));
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString(), tran);
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
dataModel.dataList = (List<B_OA_Meeting>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Meeting>));
Utility.Database.Commit(tran);
return JsonConvert.SerializeObject( dataModel);
}
catch (Exception ex)
{
ComBase.Logger(ex);
return Utility.JsonMsg(false, "读取失败:" + ex.Message.Replace(":", " "));
}
}
public B_OA_Meeting GetMeetingByCaseId(string caseId, IDbTransaction tran)
{
StringBuilder strSql = new StringBuilder();
GetDataModel dataModel = new GetDataModel();
strSql.Append(String.Format(@"SELECT
A.MeetingID ,A.MeetingRoomID ,OrganizID ,A.Phone ,MeetingName ,Presenter ,CONVERT(VARCHAR(10),A.StartTime,120) AS MeetingDate,CONVERT(VARCHAR(16),StartTime,120) AS StartTime ,
CONVERT(VARCHAR(16),EndTime,120) AS EndTime ,SUBSTRING(CONVERT(VARCHAR(16),StartTime,120),6,11) AS sStartTime ,SUBSTRING(CONVERT(VARCHAR(16),EndTime,120),6,11) AS sEndTime,
A.Number ,(CASE A.STATUS WHEN 0 THEN '待审批' WHEN 1 THEN '已审批' END) AS StatusText,A.STATUS,
A.Remark ,Applicant ,CreatTime ,Approver ,ApprovalTime,B.MeetingRoomName,ISNULL(STUFF((SELECT '、' + B.DeviceName FROM
(SELECT MeetingRoomID,DeviceName
FROM B_OA_Device WHERE MeetingRoomID = A.MeetingRoomID)B
FOR XML PATH ('')),1,1,''),'无') AS Device,A.ParticipantName,B.Number AS MaxNumber,CaseID,NeedDevice,Purpose,C.CnName AS OrganizerName,D.FullName AS Dpname,DpnameID,
ISNULL(STUFF((SELECT ';' + E.UserID FROM
(SELECT MeetingID,UserID
FROM B_OA_Participant WHERE MeetingID = A.MeetingID)E
FOR XML PATH ('')),1,1,''),'') AS ParticipantNameid,
ISNULL(STUFF((SELECT ';' + E.CnName FROM
(SELECT MeetingID,CnName
FROM B_OA_Participant F
LEFT JOIN FX_UserInfo G ON F.UserID = G.UserID
WHERE MeetingID = A.MeetingID)E
FOR XML PATH ('')),1,1,''),'') AS ParticipantNames
FROM
B_OA_Meeting A
LEFT JOIN B_OA_MeetingRoom B ON B.MeetingRoomID = A.MeetingRoomID
LEFT JOIN FX_UserInfo C ON A.OrganizID = C. UserID
LEFT JOIN FX_Department D ON A.DpnameID = D.DPID
WHERE A.CaseID = '{0}'
ORDER BY A.MeetingID DESC
", caseId));
DataSet dataSet = Utility.Database.ExcuteDataSet(strSql.ToString(), tran);
string jsonData = JsonConvert.SerializeObject(dataSet.Tables[0]);
List<B_OA_Meeting> dataList = (List<B_OA_Meeting>)JsonConvert.DeserializeObject(jsonData, typeof(List<B_OA_Meeting>));
return dataList[0];
}
public string LoadData(string cph, string sfky, string userid)
{
GetDataModel data = new GetDataModel();
B_OA_Device list = new B_OA_Device();
data.dataList = Utility.Database.QueryList(list);
return JsonConvert.SerializeObject( data);
}