/// <summary>
/// 更新组信息
/// </summary>
/// <param name="groups"></param>
/// <returns></returns>
public static bool UpdateGroups(List <DeviceGroup> groups)
{
bool result = false;
if (groups != null)
{
var list = new List <SqlTextModel>();
foreach (DeviceGroup group in groups)
{
string sql = "update ipvt_groupinfotable set ParentID=?pid,GroupName=?name,GroupLevel=?level" +
" where GroupID=?id";
var ps = new[]
{
new MySqlParameter("?pid", group.ParentId),
new MySqlParameter("?name", group.Name),
new MySqlParameter("?level", group.Level),
new MySqlParameter("?id", group.Id)
};
list.Add(new SqlTextModel()
{
SqlString = sql, MySqlParams = ps
});
}
result = CustomMySqlHelper.ExecuteSqlList(list);
}
return(result);
}
/// <summary>
/// 改变区号：调用存储过程清理旧分机/设备表，写新的分机号数据
/// </summary>
/// <param name="areacode">区号</param>
/// <param name="oldareacode">区号</param>
/// <returns></returns>
public static bool UpdateAreacode(int areacode, int oldareacode)
{
List <SqlTextModel> list = new List <SqlTextModel>();
int oldmin = oldareacode * 1000;
int oldmax = oldareacode * 1000 + 1000;
int min = areacode * 1000;
if (IsTh)
{
//6位分机号，写3000个
oldmin = oldareacode * 10000;
oldmax = oldmin + 3000;
min = areacode * 10000;
}
for (int i = oldmin; i < oldmax; i++)
{
string sql = "UPDATE ipvt_extensionmessagetable set ExtensionNO=?extno where ExtensionNO=?old";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?extno", min);
ps[1] = new MySqlParameter("?old", i);
min++;
list.Add(new SqlTextModel()
{
SqlString = sql, MySqlParams = ps
});
}
//list.Add(new SqlTextModel { SqlString = "UPDATE ipvt_transetuptable SET StartTime=NULL,EndTime=NULL,StairTran=NULL,SecondTran=NULL,ThreeTran=NULL,IsEnable=0" });
//list.Add(new SqlTextModel { SqlString = "DELETE FROM ipvt_transferinfotable" });
return(CustomMySqlHelper.ExecuteSqlList(list));
}
/// <summary>
/// 删除设备信息
/// </summary>
/// <param name="device"></param>
/// <returns></returns>
public static bool DelDeviceInfo(Device device)
{
if (device != null)
{
List <SqlTextModel> list = new List <SqlTextModel>();
string s1 = "DELETE FROM ipvt_panelinfotable WHERE ExtensionID=?id;";
string s2 = "UPDATE ipvt_extensionmessagetable SET CurrentState = 0,DeviceIP = NULL,StateID = NULL,Date = NULL,Time = NULL,PhoneState=0 WHERE ExtensionID=?id;";
string s3 = "DELETE FROM ipvt_deviceinfotable WHERE DeviceID=?id;";
list.Add(new SqlTextModel()
{
SqlString = s1, MySqlParams = new[] { new MySqlParameter("?id", device.Extension.Id) }
});
list.Add(new SqlTextModel()
{
SqlString = s2, MySqlParams = new[] { new MySqlParameter("?id", device.Extension.Id) }
});
list.Add(new SqlTextModel()
{
SqlString = s3, MySqlParams = new[] { new MySqlParameter("?id", device.Id) }
});
return(CustomMySqlHelper.ExecuteSqlList(list));
}
return(false);
}
/// <summary>
/// 保存语音文件名到数据库
/// </summary>
/// <param name="ss"></param>
public static bool SavaVoiceFiles(List <string> ss)
{
if (ss != null)
{
List <string> list;
GetVoiceList(out list);
var sqlmodels = new List <SqlTextModel>();
for (int i = 0; i < ss.Count; i++)
{
string name = ss[i].Split(';').ToList()[0];
string time = ss[i].Split(';').ToList()[1];
if (!list.Contains(name))
{
string sql = "insert into ipvt_voicefiletable(VoiceFileName,Description) values(?name,?filetime)";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?name", name);
ps[1] = new MySqlParameter("?filetime", time);
var model = new SqlTextModel {
SqlString = sql, MySqlParams = ps
};
sqlmodels.Add(model);
}
}
return(CustomMySqlHelper.ExecuteSqlList(sqlmodels));
}
return(false);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="models"></param>
/// <returns></returns>
public static bool SavePortAlarmModels(ObservableCollection <CommonModel> models)
{
List <SqlTextModel> list = new List <SqlTextModel>();
if (models != null)
{
for (int i = 0; i < models.Count; i++)
{
SqlTextModel model = new SqlTextModel();
model.SqlString = "update ipvt_alarmtypetable set AlarmType=?type where AlarmTypeID=?id";
model.MySqlParams = new[]
{
new MySqlParameter("?type", models[i].Content),
new MySqlParameter("?id", models[i].Id)
};
list.Add(model);
}
}
if (list.Count > 0)
{
return(CustomMySqlHelper.ExecuteSqlList(list));
}
return(false);
}
/// <summary>
/// 清除异常残留数据
/// </summary>
/// <returns></returns>
public static bool ClearOldData()
{
var sqlList = new List <SqlTextModel>();
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_talkinginfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_channelsinfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "UPDATE ipvt_extensionmessagetable SET PhoneState = 0"
});
return(CustomMySqlHelper.ExecuteSqlList(sqlList));
}
/// <summary>
/// 更新面板信息
/// </summary>
/// <param name="panels"></param>
private static bool UpdatePanels(IEnumerable <PanelDevice> panels)
{
var list = new List <SqlTextModel>();
foreach (var panel in panels)
{
string sqlStr = "update ipvt_panelinfotable set PanelName=?name where PanelID=?id";
var parameters = new MySqlParameter[2];
parameters[0] = new MySqlParameter("?name", panel.Name);
parameters[1] = new MySqlParameter("?id", panel.Id);
list.Add(new SqlTextModel {
SqlString = sqlStr, MySqlParams = parameters
});
}
return(CustomMySqlHelper.ExecuteSqlList(list));
}
/// <summary>
/// 更新权限组
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public static bool UpdateGroup(JurisdictionGroup group)
{
var models = new List <SqlTextModel>();
if (group != null && group.GroupId > 0 && !string.IsNullOrEmpty(group.GroupName))
{
//更新权限组表组信息
string gsql = "update ipvt_jurgrouptable set GroupName=?name,Description=?des where GroupID=?id";
var ps = new MySqlParameter[3];
ps[0] = new MySqlParameter("?name", group.GroupName);
ps[1] = new MySqlParameter("?des", group.Description);
ps[2] = new MySqlParameter("?id", group.GroupId);
models.Add(new SqlTextModel {
SqlString = gsql, MySqlParams = ps
});
//删除权限组-权限表指定组的旧对应数据
string delsql = "delete from ipvt_jurisdiction_grouptable where GroupID=?id";
var delps = new[] { new MySqlParameter("?id", group.GroupId) };
models.Add(new SqlTextModel {
SqlString = delsql, MySqlParams = delps
});
//添加权限组-权限表指定组的新对应数据
foreach (Jurisdiction jur in group.Jurisdictions)
{
string insertsql =
"insert into ipvt_jurisdiction_grouptable(GroupID,JurisdictionID) values(?gid,?jid)";
var inps = new MySqlParameter[2];
inps[0] = new MySqlParameter("?gid", group.GroupId);
inps[1] = new MySqlParameter("?jid", jur.Id);
models.Add(new SqlTextModel {
SqlString = insertsql, MySqlParams = inps
});
}
return(CustomMySqlHelper.ExecuteSqlList(models));//事务批量执行
}
return(false);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool DeleteFiles(List <string> list)
{
if (list != null)
{
var sqlmodels = new List <SqlTextModel>();
for (int i = 0; i < list.Count; i++)
{
string sql = "delete from ipvt_voicefiletable where VoiceFileName=?name and VoiceType=0";
var ps = new MySqlParameter("?name", list[i]);
var model = new SqlTextModel {
SqlString = sql, MySqlParams = new[] { ps }
};
sqlmodels.Add(model);
}
return(CustomMySqlHelper.ExecuteSqlList(sqlmodels));
}
return(false);
}
/// <summary>
/// 存入数据库
/// </summary>
/// <param name="transfers">转移数据对象集合</param>
/// <returns>true：成功 falase：失败</returns>
private static bool SaveToDb(IEnumerable <Transfer> transfers)
{
var sqlList = new List <SqlTextModel>();
foreach (Transfer tran in transfers)
{
string sqlStr =
"Update ipvt_transferinfotable SET " +
"TransferExtensionNo=?teno WHERE ExtensionNO=?no and TransferLevel=?level";
var parameteres = new MySqlParameter[3];
parameteres[0] = new MySqlParameter("?teno", tran.TransferExtensionNo);
parameteres[1] = new MySqlParameter("?no", tran.ExtensionNo);
parameteres[2] = new MySqlParameter("?level", tran.Level);
sqlList.Add(new SqlTextModel {
SqlString = sqlStr, MySqlParams = parameteres
});
}
return(CustomMySqlHelper.ExecuteSqlList(sqlList));
}
/// <summary>
/// 初始区号，写分机号
/// </summary>
/// <param name="areacode">区号</param>
/// <returns></returns>
public static bool WriteAreacode(int areacode)
{
int min = areacode * 1000;
int max = areacode * 1000 + 1000;
if (IsTh)
{
//6位分机号，写3000个
min = areacode * 10000;
max = min + 3000;
}
//var result = CustomMySqlHelper.ExecuteNonQuery(CustomMySqlHelper.ConnectionString,
// CommandType.StoredProcedure,"pro_ipvt_extensionmessagetable_do",
// new[] {new MySqlParameter("min_no", min), new MySqlParameter("max_no", max)});
List <SqlTextModel> list = new List <SqlTextModel>();
list.Add(new SqlTextModel()
{
SqlString = "DELETE FROM ipvt_deviceinfotable;"
});
list.Add(new SqlTextModel()
{
SqlString = "DELETE FROM ipvt_extensionmessagetable;"
});
for (int i = min; i < max; i++)
{
list.Add(new SqlTextModel()
{
SqlString = string.Format("INSERT INTO ipvt_extensionmessagetable(ExtensionNO) VALUES({0});", i)
});
}
return(CustomMySqlHelper.ExecuteSqlList(list));
}
/// <summary>
/// 插入权限组
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public static bool InsertGroup(JurisdictionGroup group)
{
var models = new List <SqlTextModel>();
if (group != null && !string.IsNullOrEmpty(group.GroupName))
{
//插入组信息
string gsql =
"insert into ipvt_jurgrouptable(GroupName,Description) values(?name,?des);SELECT @@IDENTITY";
var ps = new MySqlParameter[2];
ps[0] = new MySqlParameter("?name", group.GroupName);
ps[1] = new MySqlParameter("?des", group.Description);
int newid = EvaluationHelper.ObjectToInt(CustomMySqlHelper.ExecuteScalar(gsql, ps));
if (newid > 0)
{
//添加权限组-权限表指定组的新对应数据
foreach (Jurisdiction jur in group.Jurisdictions)
{
string insertsql =
"insert into ipvt_jurisdiction_grouptable(GroupID,JurisdictionID) values(?gid,?jid)";
var inps = new MySqlParameter[2];
inps[0] = new MySqlParameter("?gid", newid);
inps[1] = new MySqlParameter("?jid", jur.Id);
models.Add(new SqlTextModel {
SqlString = insertsql, MySqlParams = inps
});
}
return(CustomMySqlHelper.ExecuteSqlList(models));//事务批量执行
}
}
return(false);
}
/// <summary>
/// 保存广播语音文件名到数据库
/// </summary>
/// <param name="ss"></param>
/// <param name="phoneId">中心分机id</param>
public static bool SavaBroadcastVoices(List <string> ss, int phoneId)
{
if (ss != null && ss.Count > 0)
{
Dictionary <int, string> dictionary = GetVoices(phoneId);
var sqlmodels = new List <SqlTextModel>();
foreach (int id in dictionary.Keys)
{
if (!ss.Contains(dictionary[id]))
{
DeleteVoice(id);
}
}
for (int i = 0; i < ss.Count; i++)
{
if (!dictionary.Values.Contains(ss[i]))
{
string sql =
"insert into ipvt_voicefiletable(VoiceFileName,VoiceType,PhoneNumber) values(?name,?type,?num)";
var ps = new MySqlParameter[3];
ps[0] = new MySqlParameter("?name", ss[i]);
ps[1] = new MySqlParameter("?type", 1);
ps[2] = new MySqlParameter("?num", phoneId);
var model = new SqlTextModel {
SqlString = sql, MySqlParams = ps
};
sqlmodels.Add(model);
}
}
return(CustomMySqlHelper.ExecuteSqlList(sqlmodels));
}
return(false);
}
/// <summary>
/// 删除权限组
/// </summary>
/// <param name="gid"></param>
/// <param name="commongid"></param>
/// <returns></returns>
public static bool DeleteGroup(int gid, int commongid)
{
var models = new List <SqlTextModel>();
if (gid > 0)
{
//删除组信息
string gsql = "delete from ipvt_jurgrouptable where GroupID=?id";
var ps = new[] { new MySqlParameter("?id", gid) };
models.Add(new SqlTextModel {
SqlString = gsql, MySqlParams = ps
});
//删除权限组-权限表指定组的旧对应数据
string delsql = "delete from ipvt_jurisdiction_grouptable where GroupID=?id";
var delps = new[] { new MySqlParameter("?id", gid) };
models.Add(new SqlTextModel {
SqlString = delsql, MySqlParams = delps
});
string updatesql = "update ipvt_userregmessagetable set RoleID=?rid where RoleID=?gid";
var inps = new MySqlParameter[2];
inps[0] = new MySqlParameter("?rid", commongid);
inps[1] = new MySqlParameter("?gid", gid);
models.Add(new SqlTextModel {
SqlString = updatesql, MySqlParams = inps
});
return(CustomMySqlHelper.ExecuteSqlList(models));//事务批量执行
}
return(false);
}
/// <summary>
/// 清理数据库
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static bool ClearDateBase(User user)
{
if (UserManager.IsUserExist(user.Name, user.Password))
{
var sqlList = new List <SqlTextModel>();
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_deviceinfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "UPDATE ipvt_extensionmessagetable SET CurrentState = 0,DeviceIP = NULL,StateID = NULL,Date = NULL,Time = NULL,PhoneState=0"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_panelinfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_transferinfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_logtable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_talkinginfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_channelsinfotable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_callrecordtable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_userregmessagetable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_staffmessagetable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_cascadingtable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_groupinfotable WHERE GroupID>0"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_jurgrouptable WHERE GroupID>3"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_jurisdiction_grouptable WHERE GroupID>3"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_voicefiletable"
});
sqlList.Add(new SqlTextModel {
SqlString = "DELETE FROM ipvt_videolinkagetable"
});
sqlList.Add(new SqlTextModel {
SqlString = "UPDATE ipvt_transetuptable SET StartTime=NULL,EndTime=NULL,StairTran=NULL,SecondTran=NULL,ThreeTran=NULL,IsEnable=0"
});
return(CustomMySqlHelper.ExecuteSqlList(sqlList));
}
return(false);
}
/// <summary>
/// 修改分时段转移时更新数据库
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool SavePartingTran(List <PartingModel> list)
{
var sqlList = new List <SqlTextModel>();
int index = 0;
if (list.Count <= 0)
{
string sql = "UPDATE ipvt_transetuptable set IsEnable=0 where ID>=1003";
sqlList.Add(new SqlTextModel()
{
SqlString = sql
});
}
else if (list.Count <= 1)
{
string sql = "UPDATE ipvt_transetuptable set IsEnable=0 where ID>=1005";
sqlList.Add(new SqlTextModel()
{
SqlString = sql
});
}
else if (list.Count <= 2)
{
string sql = "UPDATE ipvt_transetuptable set IsEnable=0 where ID>=1007";
sqlList.Add(new SqlTextModel()
{
SqlString = sql
});
}
foreach (PartingModel model in list)
{
if (index >= 3)
{
break;
}
int start = model.StartTime;
int end = model.EndTime;
if (model.BusinessList.Count >= 3)
{
string sql = "UPDATE ipvt_transetuptable set StartTime=?start,EndTime=?end," +
"StairTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?one)," +
"SecondTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?two)," +
"ThreeTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?three)," +
"IsEnable=1 where ID=?id";
var ps = new MySqlParameter[6];
ps[0] = new MySqlParameter("?one", string.IsNullOrEmpty(model.BusinessList[0].TransferExtensionNo) ? 0 : Convert.ToInt32(model.BusinessList[0].TransferExtensionNo));
ps[1] = new MySqlParameter("?two", string.IsNullOrEmpty(model.BusinessList[1].TransferExtensionNo) ? 0 : Convert.ToInt32(model.BusinessList[1].TransferExtensionNo));
ps[2] = new MySqlParameter("?three", string.IsNullOrEmpty(model.BusinessList[2].TransferExtensionNo) ? 0 : Convert.ToInt32(model.BusinessList[2].TransferExtensionNo));
ps[3] = new MySqlParameter("?start", start);
ps[4] = new MySqlParameter("?end", end);
ps[5] = new MySqlParameter("?id", 1003 + index * 2);
sqlList.Add(new SqlTextModel()
{
SqlString = sql, MySqlParams = ps
});
}
if (model.UrgencyList.Count >= 3)
{
string sql = "UPDATE ipvt_transetuptable set StartTime=?start,EndTime=?end," +
"StairTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?one)," +
"SecondTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?two)," +
"ThreeTran=(select ExtensionID from ipvt_extensionmessagetable where ExtensionNO=?three)," +
"IsEnable=1 where ID=?id";
var ps = new MySqlParameter[6];
ps[0] = new MySqlParameter("?one", string.IsNullOrEmpty(model.UrgencyList[0].TransferExtensionNo) ? 0 : Convert.ToInt32(model.UrgencyList[0].TransferExtensionNo));
ps[1] = new MySqlParameter("?two", string.IsNullOrEmpty(model.UrgencyList[1].TransferExtensionNo) ? 0 : Convert.ToInt32(model.UrgencyList[1].TransferExtensionNo));
ps[2] = new MySqlParameter("?three", string.IsNullOrEmpty(model.UrgencyList[2].TransferExtensionNo) ? 0 : Convert.ToInt32(model.UrgencyList[2].TransferExtensionNo));
ps[3] = new MySqlParameter("?start", start);
ps[4] = new MySqlParameter("?end", end);
ps[5] = new MySqlParameter("?id", 1004 + index * 2);
sqlList.Add(new SqlTextModel()
{
SqlString = sql, MySqlParams = ps
});
}
index++;
}
return(CustomMySqlHelper.ExecuteSqlList(sqlList));
}