/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT(T_IPSFR_USER_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT a.HID, a.HNAME, a.SN, a.PHONE, a.SEX, a.BIRTH, a.ONSTATE, a.BYORGNO,a.MOBILEPARAMLIST, a.ISENABLE,a.PATROLLENGTH, b.ORGNAME");
sb.AppendFormat(" FROM T_IPSFR_USER AS a LEFT OUTER JOIN T_SYS_ORG AS b ON a.BYORGNO = b.ORGNO");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.HID) == false)
{
if (sw.HID.Split(',').Length > 1)
{
sb.AppendFormat(" AND a.HID in({0})", ClsSql.EncodeSql(sw.HID));
}
else
{
sb.AppendFormat(" AND a.HID ='{0}'", ClsSql.EncodeSql(sw.HID));
}
}
if (string.IsNullOrEmpty(sw.HNAME) == false)
{
sb.AppendFormat(" AND a.HNAME like '%{0}%'", ClsSql.EncodeSql(sw.HNAME));
}
if (string.IsNullOrEmpty(sw.SN) == false)
{
sb.AppendFormat(" AND a.SN like '%{0}%'", ClsSql.EncodeSql(sw.SN));
}
if (string.IsNullOrEmpty(sw.PHONE) == false)
{
if (sw.PHONE.Length == 11)//精确查询
{
sb.AppendFormat(" AND a.PHONE= '{0}'", ClsSql.EncodeSql(sw.PHONE));
}
else //模糊查询
{
sb.AppendFormat(" AND a.PHONE like '%{0}%'", ClsSql.EncodeSql(sw.PHONE));
}
}
if (string.IsNullOrEmpty(sw.PHONELIST) == false)
{
if (sw.PHONELIST.Split(',').Length > 1)
{
sb.AppendFormat(" AND PHONE in({0})", ClsSql.SwitchStrToSqlIn(sw.PHONELIST));
}
else
{
sb.AppendFormat(" AND PHONE ='{0}'", ClsSql.EncodeSql(sw.PHONELIST));
}
}
if (string.IsNullOrEmpty(sw.PATROLLENGTH) == false)
{
sb.AppendFormat(" AND a.PATROLLENGTH = '{0}'", ClsSql.EncodeSql(sw.PATROLLENGTH));
}
if (string.IsNullOrEmpty(sw.SEX) == false)
{
sb.AppendFormat(" AND a.SEX = '{0}'", ClsSql.EncodeSql(sw.SEX));
}
if (string.IsNullOrEmpty(sw.ONSTATE) == false)
{
sb.AppendFormat(" AND a.ONSTATE = '{0}'", ClsSql.EncodeSql(sw.ONSTATE));
}
if (string.IsNullOrEmpty(sw.ISENABLE) == false)//默认取有效用户
{
sb.AppendFormat(" AND a.ISENABLE = '{0}'", ClsSql.EncodeSql(sw.ISENABLE));
}
if (string.IsNullOrEmpty(sw.PhoneHname) == false)
{
sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname));
}
if (string.IsNullOrEmpty(sw.BYORGNO) == false)
{
if (PublicCls.OrgIsShi(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsXian(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsZhen(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsCun(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getCunIncOrgNo(sw.BYORGNO));
}
}
if (string.IsNullOrEmpty(sw.Orgs) == false)
{
string[] arr = sw.Orgs.Split(',');
string tmpOrg = "";
for (int i = 0; i < arr.Length; i++)
{
if (arr[i].Length == 15)
{
if (tmpOrg != "")
{
tmpOrg += ",";
}
tmpOrg += arr[i];
}
}
if (tmpOrg != "")
{
string[] arr1 = tmpOrg.Split(',');//循环每个单位
sb.AppendFormat(" and (");
for (int i = 0; i < arr1.Length; i++)
{
if (i > 0)
{
sb.AppendFormat(" or");
}
if (PublicCls.OrgIsShi(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsXian(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsZhen(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(arr1[i]));
}
else if (PublicCls.OrgIsCun(arr1[i]))
{
sb.AppendFormat(" BYORGNO like '{0}%'", PublicCls.getCunIncOrgNo(arr1[i]));
}
}
sb.AppendFormat(" )");
}
}
sb.AppendFormat(" ORDER BY a.BYORGNO,a.HNAME ");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
public EmployeeBAL()
{
this.DB = new DataBaseClass();
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static DataTable getDT(DC_RESOURCE_NEW_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_RESOURCE_NEW");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_RESOURCE_NEW_ID) == false)
{
sb.AppendFormat(" AND DC_RESOURCE_NEW_ID = '{0}'", ClsSql.EncodeSql(sw.DC_RESOURCE_NEW_ID));
}
if (string.IsNullOrEmpty(sw.RESOURCETYPE) == false)
{
sb.AppendFormat(" AND RESOURCETYPE = '{0}'", ClsSql.EncodeSql(sw.RESOURCETYPE));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.KINDTYPE) == false)
{
sb.AppendFormat(" AND KINDTYPE = '{0}'", ClsSql.EncodeSql(sw.KINDTYPE));
}
if (string.IsNullOrEmpty(sw.AGETYPE) == false)
{
sb.AppendFormat(" AND AGETYPE = '{0}'", ClsSql.EncodeSql(sw.AGETYPE));
}
if (string.IsNullOrEmpty(sw.ORIGINTYPE) == false)
{
sb.AppendFormat(" AND ORIGINTYPE = '{0}'", ClsSql.EncodeSql(sw.ORIGINTYPE));
}
if (string.IsNullOrEmpty(sw.BURNTYPE) == false)
{
sb.AppendFormat(" AND BURNTYPE = '{0}'", ClsSql.EncodeSql(sw.BURNTYPE));
}
if (string.IsNullOrEmpty(sw.TREETYPE) == false)
{
sb.AppendFormat(" AND TREETYPE = '{0}'", ClsSql.EncodeSql(sw.TREETYPE));
}
if (!string.IsNullOrEmpty(sw.ORGNOS))
{
if (sw.ORGNOSXZ != "1")
{
if (sw.ORGNOS.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,4) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 4)));
}
else if (sw.ORGNOS.Substring(6, 9) == "000000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,6) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 6)));
}
else if (sw.ORGNOS.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,9) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 9)));
}
else if (sw.ORGNOS.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,12) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND ORGNOS = '{0}'", ClsSql.EncodeSql(sw.ORGNOS));
}
}
else
{
sb.AppendFormat(" AND ORGNOS = '{0}'", ClsSql.EncodeSql(sw.ORGNOS));
}
}
string sql = "SELECT DC_RESOURCE_NEW_ID, RESOURCETYPE, NUMBER, NAME, ORGNOS, KINDTYPE, AGETYPE, ORIGINTYPE, AREA, BURNTYPE, TREETYPE, ASPECT, ANGLE, MARK, JD, WD,POTHOOKLEADER,POTHOOKLEADERJOB,POTHOOKLEADERTLEE,DUTYPERSON,DUTYPERSONTELL,JWDLIST"
+ sb.ToString()
+ " order by ORGNOS,DC_RESOURCE_NEW_ID desc";
DataSet ds = DataBaseClass.FullDataSet(sql);
return(ds.Tables[0]);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Save(WILD_ANIMALDYNAMICPROP_Model m)
{
List <string> sqllist = new List <string>();
string[] arrDYNAMICPROPCODE = m.DYNAMICPROPCODE.Split(',');
string[] arrDYNAMICPROPCONTENT = m.DYNAMICPROPCONTENT.Split(',');
if (arrDYNAMICPROPCODE.Length - 1 > 0)
{
#region 先删除
StringBuilder sbDelete = new StringBuilder();
sbDelete.AppendFormat("delete from WILD_ANIMALDYNAMICPROP where 1=1");
sbDelete.AppendFormat(" and BIOLOGICALTYPECODE='{0}'", m.BIOLOGICALTYPECODE);
DataBaseClass.ExeSql(sbDelete.ToString());
#endregion
#region 再更新
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendFormat("INSERT INTO WILD_ANIMALDYNAMICPROP(BIOLOGICALTYPECODE, DYNAMICPROPCODE, DYNAMICPROPCONTENT)");
for (int i = 0; i < arrDYNAMICPROPCODE.Length - 1; i++)
{
#region 更新
if (isExists(new WILD_ANIMALDYNAMICPROP_SW {
BIOLOGICALTYPECODE = m.BIOLOGICALTYPECODE, DYNAMICPROPCODE = arrDYNAMICPROPCODE[i]
}))
{
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat("UPDATE WILD_ANIMALDYNAMICPROP SET ");
sbUpdate.AppendFormat(" DYNAMICPROPCONTENT={0},", ClsSql.saveNullField(arrDYNAMICPROPCONTENT[i]));
sbUpdate.AppendFormat(" where BIOLOGICALTYPECODE= '{0}'", ClsSql.EncodeSql(m.BIOLOGICALTYPECODE));
sbUpdate.AppendFormat(" and DYNAMICPROPCODE= '{0}'", ClsSql.EncodeSql(arrDYNAMICPROPCODE[i]));
sqllist.Add(sbUpdate.ToString());
}
#endregion
#region 添加
else
{
sbInsert.AppendFormat(" select '{0}'", ClsSql.EncodeSql(m.BIOLOGICALTYPECODE));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrDYNAMICPROPCODE[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrDYNAMICPROPCONTENT[i]));
sbInsert.AppendFormat(" UNION ALL ");
}
#endregion
}
string insertStr = sbInsert.ToString();
if (insertStr.Contains(" UNION ALL "))
{
insertStr = insertStr.Substring(0, insertStr.Length - 10);
sqllist.Add(insertStr);
}
#endregion
}
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y >= 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败，事物回滚机制!", ""));
}
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static DataTable getDT(DC_UTILITY_RELAY_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_UTILITY_RELAY");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_UTILITY_RELAY_ID) == false)
{
sb.AppendFormat(" AND DC_UTILITY_RELAY_ID = '{0}'", ClsSql.EncodeSql(sw.DC_UTILITY_RELAY_ID));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.COMMUNICATIONWAY) == false)
{
sb.AppendFormat(" AND COMMUNICATIONWAY = '{0}'", ClsSql.EncodeSql(sw.COMMUNICATIONWAY));
}
if (string.IsNullOrEmpty(sw.USESTATE) == false)
{
sb.AppendFormat(" AND USESTATE = '{0}'", ClsSql.EncodeSql(sw.USESTATE));
}
if (string.IsNullOrEmpty(sw.MANAGERSTATE) == false)
{
sb.AppendFormat(" AND MANAGERSTATE = '{0}'", ClsSql.EncodeSql(sw.MANAGERSTATE));
}
if (!string.IsNullOrEmpty(sw.BYORGNO))
{
if (sw.ORGNOSXZ != "1")
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(6, 9) == "000000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else if (sw.BYORGNO.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,12) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
string sql = "SELECT DC_UTILITY_RELAY_ID, NUMBER, NAME, ADDRESS, MODEL, BYORGNO, COMMUNICATIONWAY,BUILDDATE, USESTATE, MANAGERSTATE, MARK, JD, WD,WORTH,BUILDDATEBEGIN,BUILDDATEEND"
+ sb.ToString()
+ " order by DC_UTILITY_RELAY_ID desc";
DataSet ds = DataBaseClass.FullDataSet(sql);
return(ds.Tables[0]);
}
/// <summary>
/// 获取OA部门编号
/// </summary>
/// <param name="sysORGNO">防火系统组织机构ID</param>
/// <param name="sysDeptId">防火系统部门ID</param>
/// <returns></returns>
public static string GetDeptID(string sysORGNO, string sysDeptId)
{
string sql = "select OADEPTID from T_SYS_Dept_OADept where SysORGNO='" + sysORGNO + "' and SysDeptID='" + sysDeptId + "'";
return(DataBaseClass.ReturnSqlField(sql));
}
/// <summary>
/// 判断任务有无执行完成
/// </summary>
/// <param name="dr"></param>
public void RunSingleTask(DataRow dr)
{
if (dr == null)
{
return;
}
string constraintValue = new DBConfigHelper().GetLastSyncNo(dr["TaskID"].ToString()); //最新的时间或者序号
object obj = GetMaxConstraintValue(dr, constraintValue); //当前要取数据的最大值
int seqNo = 0;
if (obj == null || string.IsNullOrEmpty(obj.ToString()) || (int.TryParse(constraintValue, out seqNo) && Convert.ToInt32(obj) <= Convert.ToInt32(constraintValue)))
{
Console.WriteLine($"{_jobName}暂时没有需要同步的记录！当前时间{DateTime.Now}");
dr["Status"] = 0;
return;
}
Console.WriteLine($"{_jobName}开始执行，当前时间{DateTime.Now}");
ConstraintColumnType constrainType = (ConstraintColumnType)Convert.ToInt32(dr["ConstraintType"]);
string viewName = dr["ViewName"].ToString(); //源数据库视图名称
string constraintColumnName = dr["ConstraintColumnName"].ToString(); //约束列名称
string conn = dr["SourceConnString"].ToString(); //源数据库连接字符串
string batchCount = dr["BatchCount"].ToString(); //批量处理记录数或者间隔
int reTryCount = Convert.ToInt32(dr["ReTryCount"]); //重试次数
int reTryInterval = Convert.ToInt32(dr["ReTryInterval"]); //重试间隔
DataBaseClass databaseClass = (DataBaseClass)Convert.ToInt32(dr["SourceType"]);
StringBuilder builder = new StringBuilder();
builder.Append("select * from ");
builder.Append(viewName);
switch (constrainType)
{
case ConstraintColumnType.日期时间列:
builder.Append(" where ");
builder.Append(constraintColumnName);
builder.Append(">");
builder.Append(FillStringBuilder(databaseClass, constraintValue));
builder.Append(" and ");
builder.Append(constraintColumnName);
builder.Append("<=");
_endSyncNo = Convert.ToDateTime(obj).ToString("yyyy-MM-dd HH:mm:ss:fff");
builder.Append(FillStringBuilder(databaseClass, _endSyncNo));
break;
case ConstraintColumnType.自增长列:
builder.Append(" where ");
builder.Append(constraintColumnName);
builder.Append(">");
builder.Append(constraintValue);
builder.Append(" and ");
builder.Append(constraintColumnName);
builder.Append("<=");
int count = Convert.ToInt32(constraintValue) + Convert.ToInt32(batchCount);
if (count > Convert.ToInt32(obj))
{
count = Convert.ToInt32(obj);
}
_endSyncNo = count.ToString();
builder.Append(count);
break;
default:
break;
}
string sql = builder.ToString();
SqlHelper helper = new SqlHelper(conn, databaseClass);
DataTable dt = null;
for (int i = 0; i < reTryCount; i++)
{
if (dt == null)
{
try
{
dt = helper.GetData(sql);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Thread.Sleep(reTryInterval);
}
}
}
BulkCopy(dt);
UpdateConfig(dr);
//修改为未执行状态
dr["Status"] = 0;
Console.WriteLine($"{_jobName}执行结束，当前时间{DateTime.Now}");
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT(T_IPSCOL_COLLECT_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT COLLECTID, HID, SYSTYPEVALUE, ADDRESS, COLLECTTIME, COLLECTNAME, MANSTATE, MANRESULT, MANTIME, MANUSERID");
sb.AppendFormat(" FROM T_IPSCOL_COLLECTDATA");
sb.AppendFormat(" WHERE 1=1");
//判断是否是多少，如果是多少，用in，如果只是一个，用= 主要考虑到速度
if (string.IsNullOrEmpty(sw.COLLECTID) == false)
{
if (sw.COLLECTID.Split(',').Length > 1)
{
sb.AppendFormat(" AND COLLECTID in({0})", ClsSql.EncodeSql(sw.COLLECTID));
}
else
{
sb.AppendFormat(" AND COLLECTID ='{0}'", ClsSql.EncodeSql(sw.COLLECTID));
}
}
if (string.IsNullOrEmpty(sw.SYSTYPEVALUE) == false)
{
sb.AppendFormat(" AND SYSTYPEVALUE ='{0}'", ClsSql.EncodeSql(sw.SYSTYPEVALUE));
}
if (string.IsNullOrEmpty(sw.MANSTATE) == false)
{
sb.AppendFormat(" AND MANSTATE ='{0}'", ClsSql.EncodeSql(sw.MANSTATE));
}
//根据护林员ID查询
if (string.IsNullOrEmpty(sw.HID) == false)
{
sb.AppendFormat(" AND HID ='{0}'", ClsSql.EncodeSql(sw.HID));
}
if (string.IsNullOrEmpty(sw.orgNo) == false)
{
if (sw.orgNo.Substring(4, 5) == "00000")//获取所有县的
{
sb.AppendFormat(" AND HID in (SELECT HID FROM T_IPSFR_USER where BYORGNO like '{0}%')", ClsSql.EncodeSql(sw.orgNo.Substring(0, 4)));
}
else if (sw.orgNo.Substring(6, 3) == "000")//获取所有市的
{
sb.AppendFormat(" AND HID in (SELECT HID FROM T_IPSFR_USER where BYORGNO like '{0}%')", ClsSql.EncodeSql(sw.orgNo.Substring(0, 6)));
}
else
{
sb.AppendFormat(" AND HID in (SELECT HID FROM T_IPSFR_USER where BYORGNO = '{0}')", ClsSql.EncodeSql(sw.orgNo));
}
}
if (!string.IsNullOrEmpty(sw.DateBegin))
{
sb.AppendFormat(" AND COLLECTTIME>='{0} 00:00:00'", sw.DateBegin);
}
if (!string.IsNullOrEmpty(sw.DateEnd))
{
sb.AppendFormat(" AND COLLECTTIME<='{0} 23:59:59'", sw.DateEnd);
}
sb.AppendFormat(" ORDER BY COLLECTTIME DESC ");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 根据编号查询权限名称
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns>参见模型</returns>
public static string getNameByID(T_SYSSEC_RIGHT_SW sw)
{
string sql = "SELECT RIGHTNAME FROM T_SYSSEC_RIGHT WHERE RIGHTID='" + sw.RIGHTID + "' and SYSFLAG='" + sw.SYSFLAG + "'";
return(DataBaseClass.ReturnSqlField(sql));
}
/// <summary>
/// 批量添加更新
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public static Message PLAdd(YJ_DANGERCLASS_Model m)
{
List <string> sqllist = new List <string>();
StringBuilder sb = new StringBuilder();
string insertstr = "";
var arrBYORGNAME = m.TOWNNAME.Split(',');
var arrBYORGNO = m.BYORGNO.Split(',');
var arrWEATHER = m.WEATHER.Split(',');
var arrTEMPREATURE = m.TEMPREATURE.Split(',');
var arrWINDYSPEED = m.WINDYSPEED.Split(',');
var arrDANGERCLASS = m.DANGERCLASS.Split(',');
var arrDCDATE = m.DCDATE.Split(',');
for (int i = 0; i < arrBYORGNAME.Length - 1; i++)
{
if (DataBaseClass.JudgeRecordExists("select 1 from YJ_DANGERCLASS where DCDATE='" + arrDCDATE[i] + "' and BYORGNO='" + arrBYORGNO[i] + "' and TOWNNAME='" + arrBYORGNAME[i] + "'"))
{
string orgno = arrBYORGNO[i].ToString().Substring(0, 6) + "%";
sb.AppendFormat("UPDATE YJ_DANGERCLASS SET ");
sb.AppendFormat(" WEATHER= '{0}',", ClsSql.EncodeSql(arrWEATHER[i]));
sb.AppendFormat(" TEMPREATURE='{0}',", ClsSql.EncodeSql(arrTEMPREATURE[i]));
sb.AppendFormat(" WINDYSPEED= '{0}',", ClsSql.EncodeSql(arrWINDYSPEED[i]));
sb.AppendFormat(" DANGERCLASS= '{0}'", ClsSql.EncodeSql(arrDANGERCLASS[i]));
sb.AppendFormat(" where DCDATE= '{0}'", ClsSql.EncodeSql(arrDCDATE[i]));
sb.AppendFormat(" and BYORGNO like '{0}'", ClsSql.EncodeSql(orgno));
sqllist.Add(sb.ToString());
sb.Remove(0, sb.Length);
}
else
{
sb.AppendFormat("INSERT INTO YJ_DANGERCLASS(DCDATE, BYORGNO, TOWNNAME, JD, WD, DANGERCLASS, TOPTOWNNAME, WEATHER, TEMPREATURE, WINDYSPEED)");
var list = T_ALL_AREACls.getListModel(new T_ALL_AREA_SW {
SubAREACODE = arrBYORGNO[i]
}).ToList(); //乡镇
var a = T_ALL_AREACls.getModel(new T_ALL_AREA_SW {
AREACODE = arrBYORGNO[i]
}); //市县
list.Add(a);
foreach (var item in list)
{
sb.AppendFormat(" select '{0}'", ClsSql.EncodeSql(arrDCDATE[i]));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(item.AREACODE));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(item.AREAJC));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.JD));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.WD));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrDANGERCLASS[i]));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrBYORGNAME[i]));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrWEATHER[i]));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrTEMPREATURE[i]));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrWINDYSPEED[i]));
sb.AppendFormat(" UNION ALL ");
}
insertstr += sb.ToString();
string str = insertstr.Substring(0, insertstr.Length - 10);
insertstr = "";
sb.Remove(0, sb.Length);
sqllist.Add(str);
}
}
var j = DataBaseClass.ExecuteSqlTran(sqllist);
if (j > 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败，事物回滚机制!", ""));
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns></returns>
public static DataTable getDT(T_SYS_BIOLOGICALTYPE_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM T_SYS_BIOLOGICALTYPE ");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.BIOLOCODE) == false)
{
string code = "";
if (PublicCls.BioCodeIsJie(sw.BIOLOCODE))
{
code = PublicCls.GetJieBioCode(sw.BIOLOCODE);
sb.AppendFormat(" AND Substring(BIOLOCODE,1,2) = '{0}'", code);
if (sw.IsOnlyGetChild)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,3,2) <> '00' AND Substring(BIOLOCODE,5,10)='0000000000'");
}
}
else if (PublicCls.BioCodeIsMen(sw.BIOLOCODE))
{
code = PublicCls.GetMenBioCode(sw.BIOLOCODE);
sb.AppendFormat(" AND Substring(BIOLOCODE,1,4) = '{0}'", code);
if (sw.IsOnlyGetChild)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,5,2) <> '00' AND Substring(BIOLOCODE,7,8)='00000000'");
}
}
else if (PublicCls.BioCodeIsGang(sw.BIOLOCODE))
{
code = PublicCls.GetGangBioCode(sw.BIOLOCODE);
sb.AppendFormat(" AND Substring(BIOLOCODE,1,6) = '{0}'", code);
if (sw.IsOnlyGetChild)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,7,2) <> '00' AND Substring(BIOLOCODE,9,6)='000000'");
}
}
else if (PublicCls.BioCodeIsMu(sw.BIOLOCODE))
{
code = PublicCls.GetMuBioCode(sw.BIOLOCODE);
sb.AppendFormat(" AND Substring(BIOLOCODE,1,8) = '{0}'", code);
if (sw.IsOnlyGetChild)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,9,2) <> '00' AND Substring(BIOLOCODE,11,4)='0000'");
}
}
else if (PublicCls.BioCodeIsKe(sw.BIOLOCODE))
{
code = PublicCls.GetKeBioCode(sw.BIOLOCODE);
sb.AppendFormat(" AND Substring(BIOLOCODE,1,10) = '{0}'", code);
if (sw.IsOnlyGetChild)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,11,2) <> '00' AND Substring(BIOLOCODE,13,2)='00'");
}
}
else if (PublicCls.BioCodeIsShu(sw.BIOLOCODE))
{
code = PublicCls.GetShuBioCode(sw.BIOLOCODE);
sb.AppendFormat(" AND Substring(BIOLOCODE,1,12) = '{0}'", code);
if (sw.IsOnlyGetChild)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,13,2) <> '00'");
}
}
else
{
code = PublicCls.GetShuBioCode(sw.BIOLOCODE);
if (sw.IsOnlyGetZhong)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,1,12) = '{0}' AND Substring(BIOLOCODE,13,2)<>'00'", code.Substring(0, 12));
}
else
{
sb.AppendFormat(" AND BIOLOCODE = '{0}'", ClsSql.EncodeSql(sw.BIOLOCODE));
}
}
}
if (sw.IsOnlyGetKe)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,1,8) <> '00000000' AND Substring(BIOLOCODE,9,2) <> '00' ");
}
if (sw.IsOnlyGetZhong)
{
sb.AppendFormat(" AND Substring(BIOLOCODE,13,2) <> '00'");
}
if (string.IsNullOrEmpty(sw.BIOLONAME) == false)
{
sb.AppendFormat(" AND BIOLONAME like '%{0}%'", ClsSql.EncodeSql(sw.BIOLONAME));
}
if (string.IsNullOrEmpty(sw.BIOLOENNAME) == false)
{
sb.AppendFormat(" AND BIOLOENNAME like '%{0}%'", ClsSql.EncodeSql(sw.BIOLOENNAME));
}
string sql = "SELECT BIOLOCODE, BIOLONAME, BIOLOENNAME, ORDERBY " + sb.ToString() + " order by BIOLOCODE";
DataSet ds = DataBaseClass.FullDataSet(sql);
return(ds.Tables[0]);
}
/// <summary>
/// 批量添加更新
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public static Message PLAdd2(YJ_DANGERCLASS_Model m)
{
List <string> sqllist = new List <string>();
var arrTOWNNAME = m.TOWNNAME.Split(',');
var arrBYORGNO = m.BYORGNO.Split(',');
var arrWEATHER = m.WEATHER.Split(',');
var arrTEMPREATURE = m.TEMPREATURE.Split(',');
var arrWINDYSPEED = m.WINDYSPEED.Split(',');
var arrDANGERCLASS = m.DANGERCLASS.Split(',');
var arrDCDATE = m.DCDATE.Split(',');
if (arrBYORGNO.Length - 1 > 0)
{
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendFormat("INSERT INTO YJ_DANGERCLASS(DCDATE, BYORGNO, TOWNNAME, JD, WD, DANGERCLASS, TOPTOWNNAME, WEATHER, TEMPREATURE, WINDYSPEED)");
for (int i = 0; i < arrBYORGNO.Length - 1; i++)
{
#region 更新
if (DataBaseClass.JudgeRecordExists("select 1 from YJ_DANGERCLASS where DCDATE='" + arrDCDATE[i] + "' and BYORGNO='" + arrBYORGNO[i] + "' and TOWNNAME='" + arrTOWNNAME[i] + "'"))
{
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat("UPDATE YJ_DANGERCLASS SET ");
sbUpdate.AppendFormat(" WEATHER= {0},", ClsSql.saveNullField(arrWEATHER[i]));
sbUpdate.AppendFormat(" TEMPREATURE={0},", ClsSql.saveNullField(arrTEMPREATURE[i]));
sbUpdate.AppendFormat(" WINDYSPEED= {0},", ClsSql.saveNullField(arrWINDYSPEED[i]));
sbUpdate.AppendFormat(" DANGERCLASS= '{0}'", ClsSql.EncodeSql(arrDANGERCLASS[i]));
sbUpdate.AppendFormat(" where DCDATE= '{0}'", ClsSql.EncodeSql(arrDCDATE[i]));
sbUpdate.AppendFormat(" and BYORGNO= '{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sqllist.Add(sbUpdate.ToString());
}
#endregion
#region 添加
else
{
sbInsert.AppendFormat(" select '{0}'", ClsSql.EncodeSql(arrDCDATE[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrTOWNNAME[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.JD));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.WD));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrDANGERCLASS[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.TOPTOWNNAME));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrWEATHER[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrTEMPREATURE[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrWINDYSPEED[i]));
sbInsert.AppendFormat(" UNION ALL ");
}
#endregion
}
string insertStr = sbInsert.ToString();
if (insertStr.Contains(" UNION ALL "))
{
insertStr = insertStr.Substring(0, insertStr.Length - 10);
sqllist.Add(insertStr);
}
}
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y >= 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败，事物回滚机制!", ""));
}
}
/// <summary>
/// 获取分页
/// </summary>
/// <param name="sw"></param>
/// <param name="total"></param>
/// <returns></returns>
public static DataTable getDT(DC_REPOSITORY_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_REPOSITORY");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.REPTYPEID) == false)
{
sb.AppendFormat("and REPTYPEID = '{0}'", ClsSql.EncodeSql(sw.REPTYPEID));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat("and NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.ADDRESS) == false)
{
sb.AppendFormat("and ADDRESS = '{0}'", ClsSql.EncodeSql(sw.ADDRESS));
}
if (!string.IsNullOrEmpty(sw.BYORGNO))
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000") //获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(4, 11) == "xxxxxxxxxxx") //单独市
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,15) = '{0}')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4) + "00000000000"));
}
else if (sw.BYORGNO.Substring(6, 9) == "xxxxxxxxx") //获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000") //获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else if (sw.BYORGNO.Substring(12, 3) == "000") //获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,12) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
if (string.IsNullOrEmpty(sw.RESPONSIBLEMAN) == false)
{
sb.AppendFormat("and RESPONSIBLEMAN = '{0}'", ClsSql.EncodeSql(sw.RESPONSIBLEMAN));
}
if (string.IsNullOrEmpty(sw.LINKWAY) == false)
{
sb.AppendFormat("and LINKWAY = '{0}'", ClsSql.EncodeSql(sw.LINKWAY));
}
string sql = ("select DCREPOSITORYID,REPTYPEID,NAME,ADDRESS,BYORGNO,RESPONSIBLEMAN,LINKWAY,JD,WD") + sb.ToString() + ("order by BYORGNO,DCREPOSITORYID desc");
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
/// <summary>
/// 根据护林员ID获取姓名
/// </summary>
/// <param name="value">编码</param>
/// <returns></returns>
public static string getName(string value)
{
string sql = " Select HNAME from T_IPSFR_USER where HID='" + value + "'";
return(DataBaseClass.ReturnSqlField(sql));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Add(JC_MONITOR_BASICINFO_Model m)
{
if (PublicCls.OrgIsZhen(m.BYORGNO) == false)
{
return(new Message(false, "添加失败，所属单位必须为乡镇！", ""));
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO JC_MONITOR_BASICINFO(TTBH, EMNAME, BYORGNO, JD, WD, GC, ADDRESS, IP, LOGINUSERNAME, USERPWD, XH, PP, GD, JCJL,OBJID,TEMPLATEDID,PORT,TYPE)");
sb.AppendFormat("output inserted.EMID ");
sb.AppendFormat("VALUES(");
sb.AppendFormat("'{0}'", ClsSql.EncodeSql(m.TTBH));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.EMNAME));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.BYORGNO));
if (string.IsNullOrEmpty(m.JD))
{
sb.AppendFormat(",null");
}
else
{
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.JD));
}
if (string.IsNullOrEmpty(m.WD))
{
sb.AppendFormat(",null");
}
else
{
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.WD));
}
if (string.IsNullOrEmpty(m.GC))
{
sb.AppendFormat(",null");
}
else
{
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.GC));
}
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.ADDRESS));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.IP));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.LOGINUSERNAME));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.USERPWD));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.XH));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.PP));
if (string.IsNullOrEmpty(m.GD))
{
sb.AppendFormat(",null");
}
else
{
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.GD));
}
if (string.IsNullOrEmpty(m.JCJL))
{
sb.AppendFormat(",null");
}
else
{
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.JCJL));
}
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.OBJID));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.TEMPLATEDID));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.PORT));
sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.TYPE));
sb.AppendFormat(")");
string strid = DataBaseClass.ReturnSqlField(sb.ToString());
if (!string.IsNullOrEmpty(strid))
{
return(new Message(true, "添加成功！", strid));
}
else
{
return(new Message(false, "添加失败，请检查各输入框是否正确！", ""));
}
}
/// <summary>
/// 获取记录
/// </summary>
/// <param name="sw"></param>
/// <returns></returns>
public static DataTable getDT(DC_UTILITY_ISOLATIONSTRIP_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_UTILITY_ISOLATIONSTRIP");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_UTILITY_ISOLATIONSTRIP_ID) == false)
{
sb.AppendFormat(" AND DC_UTILITY_ISOLATIONSTRIP_ID = '{0}'", ClsSql.EncodeSql(sw.DC_UTILITY_ISOLATIONSTRIP_ID));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.ISOLATIONTYPE) == false)
{
sb.AppendFormat(" AND ISOLATIONTYPE = '{0}'", ClsSql.EncodeSql(sw.ISOLATIONTYPE));
}
if (string.IsNullOrEmpty(sw.USESTATE) == false)
{
sb.AppendFormat(" AND USESTATE = '{0}'", ClsSql.EncodeSql(sw.USESTATE));
}
if (string.IsNullOrEmpty(sw.MANAGERSTATE) == false)
{
sb.AppendFormat(" AND MANAGERSTATE = '{0}'", ClsSql.EncodeSql(sw.MANAGERSTATE));
}
if (!string.IsNullOrEmpty(sw.BYORGNO))
{
if (sw.ORGNOSXZ != "1")
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(6, 9) == "000000000")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else if (sw.BYORGNO.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,12) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
string sql = "SELECT DC_UTILITY_ISOLATIONSTRIP_ID, ISOLATIONTYPE, NUMBER, NAME, BYORGNO, BUILDDATE, USESTATE,MANAGERSTATE, WIDTH, LENGTH, JDBEGIN, WDBEGIN, JDEND, WDEND,JWDLIST,PLANAREA,REALAREA,WORTH,KINDTYPE,POSITION,PRICE,ALLEYWAYWIDETH,TREETYPE,ENTRYTIME,BUILDDATEBEGIN,BUILDDATEEND"
+ sb.ToString()
+ " order by DC_UTILITY_ISOLATIONSTRIP_ID desc";
DataSet ds = DataBaseClass.FullDataSet(sql);
return(ds.Tables[0]);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Save(PEST_REPORT_RCW_Model m)
{
List <string> sqllist = new List <string>();
string[] arrRCWCODE = m.RCWCODE.Split(',');
string[] arrRCWVALUE = m.RCWVALUE.Split(',');
if (arrRCWCODE.Length - 1 > 0)
{
#region 先删除
string sql = "delete from PEST_REPORT_RCW where BYORGNO='" + m.BYORGNO + "' and RCWYEAR='" + m.RCWYEAR + "'";
DataBaseClass.ExeSql(sql);
#endregion
#region 再更新
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendFormat("INSERT INTO PEST_REPORT_RCW(BYORGNO, RCWYEAR, RCWCODE, RCWVALUE)");
for (int i = 0; i < arrRCWCODE.Length - 1; i++)
{
#region 更新
if (isExists(new PEST_REPORT_RCW_SW {
BYORGNO = m.BYORGNO, RCWYEAR = m.RCWYEAR
}))
{
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat("UPDATE PEST_REPORT_RCW SET ");
sbUpdate.AppendFormat(" RCWVALUE='{0}',", ClsSql.saveNullField(arrRCWVALUE[i]));
sbUpdate.AppendFormat(" where BYORGNO= '{0}'", ClsSql.EncodeSql(m.BYORGNO));
sbUpdate.AppendFormat(" and RCWYEAR= '{0}'", ClsSql.EncodeSql(m.RCWYEAR));
sbUpdate.AppendFormat(" and RCWCODE= '{0}'", ClsSql.EncodeSql(arrRCWCODE[i]));
sqllist.Add(sbUpdate.ToString());
}
#endregion
#region 添加
else
{
sbInsert.AppendFormat(" select '{0}'", ClsSql.EncodeSql(m.BYORGNO));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.RCWYEAR));;
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrRCWCODE[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrRCWVALUE[i]));
sbInsert.AppendFormat(" UNION ALL ");
}
#endregion
}
string insertStr = sbInsert.ToString();
if (insertStr.Contains(" UNION ALL "))
{
insertStr = insertStr.Substring(0, insertStr.Length - 10);
sqllist.Add(insertStr);
}
#endregion
}
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y >= 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败，事物回滚机制!", ""));
}
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="sw"></param>
/// <param name="total"></param>
/// <returns></returns>
public static DataTable getDT(DC_UTILITY_ISOLATIONSTRIP_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_UTILITY_ISOLATIONSTRIP");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_UTILITY_ISOLATIONSTRIP_ID) == false)
{
sb.AppendFormat(" AND DC_UTILITY_ISOLATIONSTRIP_ID = '{0}'", ClsSql.EncodeSql(sw.DC_UTILITY_ISOLATIONSTRIP_ID));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.ISOLATIONTYPE) == false)
{
sb.AppendFormat(" AND ISOLATIONTYPE = '{0}'", ClsSql.EncodeSql(sw.ISOLATIONTYPE));
}
if (string.IsNullOrEmpty(sw.USESTATE) == false)
{
sb.AppendFormat(" AND USESTATE = '{0}'", ClsSql.EncodeSql(sw.USESTATE));
}
if (string.IsNullOrEmpty(sw.MANAGERSTATE) == false)
{
sb.AppendFormat(" AND MANAGERSTATE = '{0}'", ClsSql.EncodeSql(sw.MANAGERSTATE));
}
if (!string.IsNullOrEmpty(sw.BYORGNO))
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(4, 11) == "xxxxxxxxxxx")//单独市
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,15) = '{0}')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4) + "00000000000"));
}
else if (sw.BYORGNO.Substring(6, 9) == "xxxxxxxxx")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else if (sw.BYORGNO.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,12) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
string sql = "SELECT DC_UTILITY_ISOLATIONSTRIP_ID, ISOLATIONTYPE, NUMBER, NAME, BYORGNO, BUILDDATE, USESTATE,MANAGERSTATE, WIDTH, LENGTH, JDBEGIN, WDBEGIN, JDEND, WDEND,JWDLIST,PLANAREA,REALAREA,WORTH,KINDTYPE,POSITION,PRICE,ALLEYWAYWIDETH,TREETYPE,ENTRYTIME,BUILDDATEBEGIN,BUILDDATEEND"
+ sb.ToString()
+ " order by BYORGNO,DC_UTILITY_ISOLATIONSTRIP_ID desc";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
//获取最大的约束值假设A，要轮询得区间为(B,C]
//如果A<C，则轮询得空间缩小为(B,A]
public object GetMaxConstraintValue(DataRow dr, string constraintValue)
{
if (dr == null)
{
return(null);
}
string viewName = dr["ViewName"].ToString();
string constraintColumnName = dr["ConstraintColumnName"].ToString();
int batchCount = Convert.ToInt32(dr["BatchCount"]);
int reTryCount = Convert.ToInt32(dr["ReTryCount"]); //重试次数
int reTryInterval = Convert.ToInt32(dr["ReTryInterval"]); //重试间隔
ConstraintColumnType constrainType = (ConstraintColumnType)Convert.ToInt32(dr["ConstraintType"]);
DataBaseClass databaseClass = (DataBaseClass)Convert.ToInt32(dr["SourceType"]);
StringBuilder sb = new StringBuilder();
sb.Append("select max(");
sb.Append(constraintColumnName);
sb.Append(") from ");
switch (constrainType)
{
case ConstraintColumnType.日期时间列:
sb.Append(" (select top ");
sb.Append(batchCount);
sb.Append(" ");
sb.Append(constraintColumnName);
sb.Append(" from ");
sb.Append(viewName);
sb.Append(" where ");
sb.Append(constraintColumnName);
sb.Append(">");
sb.Append(FillStringBuilder(databaseClass, constraintValue));
sb.Append(" order by ");
sb.Append(constraintColumnName);
sb.Append(" ) as TempTable");
break;
case ConstraintColumnType.自增长列:
sb.Append(viewName);
sb.Append(" where ");
sb.Append(constraintColumnName);
sb.Append(">");
sb.Append(constraintValue);
break;
default:
sb.Append(" 1=1 ");
break;
}
string sql = sb.ToString();
string conn = dr["SourceConnString"].ToString();
SqlHelper helper = new SqlHelper(conn, databaseClass);
object max = null;
for (int i = 0; i < reTryCount; i++)
{
if (max == null)
{
try
{
max = helper.GetMax(sql);
break;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Thread.Sleep(reTryInterval);
}
}
}
return(max);
}
/// <summary>
///获取分页列表
/// </summary>
/// <param name="sw"></param>
/// <param name="total"></param>
/// <returns></returns>
public static DataTable getDT(WILD_BOTANYDISTRIBUTE_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM WILD_BOTANYDISTRIBUTE");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.WILD_BOTANYDISTRIBUTEID) == false)
{
sb.AppendFormat(" AND WILD_BOTANYDISTRIBUTEID = '{0}'", ClsSql.EncodeSql(sw.WILD_BOTANYDISTRIBUTEID));
}
if (string.IsNullOrEmpty(sw.BIOLOGICALTYPECODE) == false)
{
sb.AppendFormat(" AND BIOLOGICALTYPECODE = '{0}'", ClsSql.EncodeSql(sw.BIOLOGICALTYPECODE));
}
if (string.IsNullOrEmpty(sw.POPULATIONTYPE) == false)
{
sb.AppendFormat(" AND POPULATIONTYPE = '{0}'", ClsSql.EncodeSql(sw.POPULATIONTYPE));
}
if (string.IsNullOrEmpty(sw.ORGNO) == false)
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.ORGNO));
}
if (string.IsNullOrEmpty(sw.BYORGNO) == false)
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(4, 11) == "xxxxxxxxxxx")//单独市
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,15) = '{0}')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4) + "00000000000"));
}
else if (sw.BYORGNO.Substring(6, 9) == "xxxxxxxxx")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000")//获取所有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else if (sw.BYORGNO.Substring(12, 3) == "000")//获取所有村的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,12) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
string sql = "SELECT WILD_BOTANYDISTRIBUTEID, BIOLOGICALTYPECODE, POPULATIONTYPE, JD, WD, BYORGNO, BOTANYCOUNT,BOTANYAREA, MARK"
+ sb.ToString()
+ " order by BYORGNO,WILD_BOTANYDISTRIBUTEID desc";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="sw">参见模型</param>
/// <returns>参见模型</returns>
public static DataTable getDT(T_ALL_AREA_SW sw)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT AREAID, AREACODE, AREANAME, AREAJC, JD, WD");
sb.AppendFormat(" FROM T_ALL_AREA");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.AREAID) == false)
{
sb.AppendFormat(" AND AREAID = '{0}'", ClsSql.EncodeSql(sw.AREAID));
}
if (string.IsNullOrEmpty(sw.AREACODE) == false)
{
sb.AppendFormat(" AND AREACODE = '{0}'", ClsSql.EncodeSql(sw.AREACODE));
}
if (!string.IsNullOrEmpty(sw.AREAJC))
{
sb.AppendFormat(" AND AREAJC = '{0}'", ClsSql.EncodeSql(sw.AREAJC));
}
if (string.IsNullOrEmpty(sw.SubAREACODE) == false)
{
if (sw.SubAREACODE == "0")
{
sb.AppendFormat(" AND Substring(AREACODE,3, 13) ='0000000000000'");
}
else if (sw.SubAREACODE.Substring(2, 13) == "0000000000000")
{
sb.AppendFormat(" AND Substring(AREACODE,5, 11) = '00000000000' AND Substring(AREACODE,3, 2) != '00' AND Substring(AREACODE,1, 2)='{0}'", sw.SubAREACODE.Substring(0, 2));
}
else if (sw.SubAREACODE.Substring(4, 11) == "00000000000" && sw.SubAREACODE.Substring(2, 2) != "00")
{
sb.AppendFormat(" AND Substring(AREACODE,7, 9) = '000000000' AND Substring(AREACODE,5, 2) != '00'AND Substring(AREACODE,1,4)='{0}'", sw.SubAREACODE.Substring(0, 4));
}
else if (sw.SubAREACODE.Substring(6, 9) == "000000000" && sw.SubAREACODE.Substring(4, 2) != "00")
{
sb.AppendFormat("AND Substring(AREACODE,10, 6) = '000000' AND Substring(AREACODE,7, 3) != '000' AND Substring(AREACODE,1,6)='{0}'", sw.SubAREACODE.Substring(0, 6));
}
else if (sw.SubAREACODE.Substring(9, 6) == "000000" && sw.SubAREACODE.Substring(6, 3) != "000")
{
sb.AppendFormat("AND Substring(AREACODE,13, 3) = '000' AND Substring(AREACODE,10, 6) != '000' AND Substring(AREACODE,1,9)='{0}'", sw.SubAREACODE.Substring(0, 6));
}
else
{
sb.AppendFormat(" AND Substring(AREACODE,1, 15) = '{0}' ", sw.SubAREACODE.Substring(0, 15));
}
}
if (!string.IsNullOrEmpty(sw.GetContyORGNOByCity))//市获取所有县
{
sb.AppendFormat(" AND (SUBSTRING(AREACODE,1,4)='{0}' and SUBSTRING(AREACODE,5,2)<>'00' and SUBSTRING(AREACODE,7,9) = '000000000' )", ClsSql.EncodeSql(sw.GetContyORGNOByCity.Substring(0, 4)));
}
if (!string.IsNullOrEmpty(sw.GetXZOrgNOByConty))
{
sb.AppendFormat(" AND (SUBSTRING(AREACODE,1,6)='{0}' and SUBSTRING(AREACODE,7,3) != '000'and SUBSTRING(AREACODE,10,6) = '000000')", ClsSql.EncodeSql(sw.GetXZOrgNOByConty.Substring(0, 6)));
}
if (string.IsNullOrEmpty(sw.OnlyGetShiXian) == false)
{
sb.AppendFormat(" AND (SUBSTRING(AREACODE,1,4)='{0}' and SUBSTRING(AREACODE,7,3) = '000' and SUBSTRING(AREACODE,5,11)<>'00000000000' )", ClsSql.EncodeSql(sw.TOPAREACODE.Substring(0, 4)));
}
sb.AppendFormat(" ORDER BY AREACODE ");
DataSet ds = DataBaseClass.FullDataSet(sb.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 排班表添加方法
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
public static Message Add(OD_USER_Model m)
{
//先删除
if (1 == 1)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("delete from OD_USER");//(OD_TYPEID, ONDUTYDATE, BYORGNO, ONDUTYUSERTYPE, ONDUTYUSERID, ISATTENDED)");
sb.AppendFormat(" where BYORGNO= '{0}'", m.BYORGNO);
sb.AppendFormat(" and OD_TYPEID= '{0}'", m.OD_TYPEID);
sb.AppendFormat(" and ONDUTYUSERTYPE= '{0}'", m.ONDUTYUSERTYPE);
sb.AppendFormat(" and ONDUTYDATE>='{0}'", m.dateBegin);
sb.AppendFormat(" and ONDUTYDATE<='{0}'", m.dateEnd);
bool bln = DataBaseClass.ExeSql(sb.ToString());
}
DateTime dtbeg = Convert.ToDateTime(m.dateBegin);
DateTime dtend = Convert.ToDateTime(m.dateEnd);
TimeSpan ts = dtend.Subtract(dtbeg); //判断下相差多少天
if (string.IsNullOrEmpty(m.ONDUTYUSERID) == false) //插入
{
string[] arr = m.ONDUTYUSERID.Split(',');
for (int i = 0; i < arr.Length; i++)//循环操作
{
if (arr[i].Length > 0)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO OD_USER(OD_TYPEID, ONDUTYDATE, BYORGNO, ONDUTYUSERTYPE, ONDUTYUSERID, ISATTENDED)");
sb.AppendFormat(" select '{0}'", m.OD_TYPEID);
sb.AppendFormat(",ONDUTYDATE");
sb.AppendFormat(", '{0}'", m.BYORGNO);
sb.AppendFormat(", '{0}'", m.ONDUTYUSERTYPE);
sb.AppendFormat(", '{0}'", arr[i]);
sb.AppendFormat(", '0'");
sb.AppendFormat(" from OD_DATE");
sb.AppendFormat(" where OD_TYPEID='{0}'", m.OD_TYPEID);
sb.AppendFormat(" and ONDUTYDATE>='{0}'", m.dateBegin);
sb.AppendFormat(" and ONDUTYDATE<='{0}'", m.dateEnd);
//sb.AppendFormat("(");
//sb.AppendFormat("'{0}'", m.OD_TYPEID);
//sb.AppendFormat(",'{0}'", m.OD_TYPEID);
//sb.AppendFormat("'{0}'", m.OD_TYPEID);
//sb.AppendFormat("'{0}'", m.OD_TYPEID);
//sb.AppendFormat(")");
bool bln = DataBaseClass.ExeSql(sb.ToString());
}
}
}
//if (bln == true)
return(new Message(true, "操作成功！", ts.Days.ToString()));
//else
// return new Message(false, "添加失败，请检查各输入框是否正确！", "");
//StringBuilder sb = new StringBuilder();
//sb.AppendFormat("INSERT INTO OD_USER(ONDUTYDATE, BYORGNO,ONDUTYUSERTYPE,ONDUTYUSERID,ISATTENDED,ATTENDEDTIME )");
//sb.AppendFormat("VALUES(");
//sb.AppendFormat("'{0}'", ClsSql.EncodeSql(m.ONDUTYDATE));
//sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.BYORGNO));
//sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.ONDUTYUSERTYPE));
//sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.ONDUTYUSERID));
//if (string.IsNullOrEmpty(m.ISATTENDED) == true)//是否签到，默认为0 未签到
// m.ISATTENDED = "0";
//sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.ISATTENDED));
//if (string.IsNullOrEmpty(m.ATTENDEDTIME) == true)
// sb.AppendFormat(",null");
//else
// sb.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.ATTENDEDTIME));
//sb.AppendFormat(")");
//bool bln = DataBaseClass.ExeSql(sb.ToString());
//if (bln == true)
// return new Message(true, "添加成功！", "");
//else
// return new Message(false, "添加失败，请检查各输入框是否正确！", "");
}
/// <summary>
/// 保存
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Save(T_SYS_PEST_OBSERVEAREA_Model m)
{
List <string> sqllist = new List <string>();
string[] arrBYORGNO = m.BYORGNO.Split(',');
string[] arrOBSERVEAREA = m.OBSERVEAREA.Split(',');
#region 先删除
StringBuilder sbDelete = new StringBuilder();
sbDelete.AppendFormat(" Delete From T_SYS_PEST_OBSERVEAREA WHERE 1=1");
sbDelete.AppendFormat(" AND OBSERVEYEAR='{0}'", m.OBSERVEYEAR);
if (m.TopORGNO.Substring(4, 5) == "00000")
{
sbDelete.AppendFormat(" AND SUBSTRING(BYORGNO,1,4)='{0}'", m.TopORGNO.Substring(0, 4));
}
if (m.TopORGNO.Substring(6, 3) == "000" && m.TopORGNO.Substring(4, 5) != "00000")
{
sbDelete.AppendFormat(" AND SUBSTRING(BYORGNO,1,6)='{0}'", m.TopORGNO.Substring(0, 6));
}
if (m.TopORGNO.Substring(6, 3) != "000")
{
sbDelete.AppendFormat(" AND BYORGNO='{0}'", m.TopORGNO);
}
DataBaseClass.ExeSql(sbDelete.ToString());
#endregion
#region 再更新
if (arrBYORGNO.Length - 1 > 0)
{
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendFormat("INSERT INTO T_SYS_PEST_OBSERVEAREA(BYORGNO,OBSERVEYEAR, OBSERVEAREA)");
for (int i = 0; i < arrBYORGNO.Length - 1; i++)
{
if (!string.IsNullOrEmpty(arrOBSERVEAREA[i]))
{
#region 更新
if (isExists(new T_SYS_PEST_OBSERVEAREA_SW {
BYORGNO = arrBYORGNO[i], OBSERVEYEAR = m.OBSERVEYEAR
}))
{
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat("UPDATE T_SYS_PEST_OBSERVEAREA SET ");
sbUpdate.AppendFormat(" OBSERVEAREA='{0}'", ClsSql.EncodeSql(arrOBSERVEAREA[i]));
sbUpdate.AppendFormat(" where BYORGNO= '{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbUpdate.AppendFormat(" and OBSERVEYEAR= '{0}'", ClsSql.EncodeSql(m.OBSERVEYEAR));
sqllist.Add(sbUpdate.ToString());
}
#endregion
#region 添加
else
{
sbInsert.AppendFormat(" select '{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.OBSERVEYEAR));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrOBSERVEAREA[i]));
sbInsert.AppendFormat(" UNION ALL ");
}
#endregion
}
}
string insertStr = sbInsert.ToString();
if (insertStr.Contains(" UNION ALL "))
{
insertStr = insertStr.Substring(0, insertStr.Length - 10);
sqllist.Add(insertStr);
}
}
#endregion
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y >= 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败，事物回滚机制!", ""));
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Add(DC_UTILITY_CAMP_Model m)
{
if (DC_UTILITY_CAMP.isExistsPoint(new DC_UTILITY_CAMP_Model {
JD = m.JD, WD = m.WD
}) == false)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO DC_UTILITY_CAMP( NUMBER, NAME, ORGNOS, AREA, BUILDDATE, FLOOR, STRUCTURETYPE, SUBFACILITIES, MARK, JD, WD,WORTH,BUILDDATEBEGIN,BUILDDATEEND) output inserted.DC_UTILITY_CAMP_ID");
sb.AppendFormat(" VALUES(");
sb.AppendFormat("{0}", ClsSql.saveNullField(m.NUMBER));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.NAME));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.ORGNOS));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.AREA));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.BUILDDATE));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.FLOOR));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.STRUCTURETYPE));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.SUBFACILITIES));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.MARK));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.JD));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.WD));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.WORTH));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.BUILDDATEBEGIN));
sb.AppendFormat(",{0}", ClsSql.saveNullField(m.BUILDDATEEND));
sb.AppendFormat(")");
try
{
string strid = DataBaseClass.ReturnSqlField(sb.ToString());
if (string.IsNullOrEmpty(strid) == false)
{
return(new Message(true, "添加成功！", strid));
}
else
{
return(new Message(false, "添加失败！请检查输入框是否正确！", strid));
}
}
catch (Exception)
{
throw;
}
}
else
{
return(new Message(false, "添加失败,已有相同的位置的营房！", ""));
}
//StringBuilder sb = new StringBuilder();
//sb.AppendFormat("INSERT INTO DC_UTILITY_CAMP( NUMBER, NAME, ORGNOS, AREA, BUILDDATE, FLOOR, STRUCTURETYPE, SUBFACILITIES, MARK, JD, WD)");
//sb.AppendFormat("VALUES(");
//sb.AppendFormat("{0}", ClsSql.saveNullField(m.NUMBER));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.NAME));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.ORGNOS));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.AREA));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.BUILDDATE));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.FLOOR));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.STRUCTURETYPE));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.SUBFACILITIES));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.MARK));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.JD));
//sb.AppendFormat(",{0}", ClsSql.saveNullField(m.WD));
//sb.AppendFormat(")");
//bool bln = DataBaseClass.ExeSql(sb.ToString());
//if (bln == true)
// return new Message(true, "添加成功！", "");
//else
// return new Message(false, "添加失败，请检查各输入框是否正确！", "");
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="sw"></param>
/// <param name="total"></param>
/// <returns></returns>
public static DataTable getDT(DC_UTILITY_RELAY_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_UTILITY_RELAY");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_UTILITY_RELAY_ID) == false)
{
sb.AppendFormat(" AND DC_UTILITY_RELAY_ID = '{0}'", ClsSql.EncodeSql(sw.DC_UTILITY_RELAY_ID));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.COMMUNICATIONWAY) == false)
{
sb.AppendFormat(" AND COMMUNICATIONWAY = '{0}'", ClsSql.EncodeSql(sw.COMMUNICATIONWAY));
}
if (string.IsNullOrEmpty(sw.USESTATE) == false)
{
sb.AppendFormat(" AND USESTATE = '{0}'", ClsSql.EncodeSql(sw.USESTATE));
}
if (string.IsNullOrEmpty(sw.MANAGERSTATE) == false)
{
sb.AppendFormat(" AND MANAGERSTATE = '{0}'", ClsSql.EncodeSql(sw.MANAGERSTATE));
}
if (!string.IsNullOrEmpty(sw.BYORGNO))
{
if (sw.BYORGNO.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,4) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4)));
}
else if (sw.BYORGNO.Substring(4, 11) == "xxxxxxxxxxx")//单独市
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,15) = '{0}')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 4) + "00000000000"));
}
else if (sw.BYORGNO.Substring(6, 9) == "xxxxxxxxx")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,6) = '{0}' or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 6)));
}
else if (sw.BYORGNO.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,9) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 9)));
}
else if (sw.BYORGNO.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(BYORGNO,1,12) = '{0}'or BYORGNO is null or BYORGNO='')", ClsSql.EncodeSql(sw.BYORGNO.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND BYORGNO = '{0}'", ClsSql.EncodeSql(sw.BYORGNO));
}
}
string sql = "SELECT DC_UTILITY_RELAY_ID, NUMBER, NAME, ADDRESS, MODEL, BYORGNO, COMMUNICATIONWAY,BUILDDATE, USESTATE, MANAGERSTATE, MARK, JD, WD,WORTH,BUILDDATEBEGIN,BUILDDATEEND"
+ sb.ToString()
+ " order by BYORGNO,DC_UTILITY_RELAY_ID desc";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="sw"></param>
/// <param name="total"></param>
/// <returns></returns>
public static DataTable getDT(DC_UTILITY_CAMP_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_UTILITY_CAMP");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_UTILITY_CAMP_ID) == false)
{
sb.AppendFormat(" AND DC_UTILITY_CAMP_ID = '{0}'", ClsSql.EncodeSql(sw.DC_UTILITY_CAMP_ID));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.STRUCTURETYPE) == false)
{
sb.AppendFormat(" AND STRUCTURETYPE = '{0}'", ClsSql.EncodeSql(sw.STRUCTURETYPE));
}
if (!string.IsNullOrEmpty(sw.ORGNOS))
{
if (sw.ORGNOSXZ != "1")
{
if (sw.ORGNOS.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,4) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 4)));
}
else if (sw.ORGNOS.Substring(4, 11) == "xxxxxxxxxxx")//单独市
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,15) = '{0}')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 4) + "00000000000"));
}
else if (sw.ORGNOS.Substring(6, 9) == "xxxxxxxxx")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,6) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 6)));
}
else if (sw.ORGNOS.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,9) = '{0}'or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 9)));
}
else if (sw.ORGNOS.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,12) = '{0}'or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND ORGNOS = '{0}'", ClsSql.EncodeSql(sw.ORGNOS));
}
}
else
{
sb.AppendFormat(" AND ORGNOS = '{0}'", ClsSql.EncodeSql(sw.ORGNOS));
}
}
string sql = "SELECT DC_UTILITY_CAMP_ID, NUMBER, NAME, ORGNOS, AREA, BUILDDATE, FLOOR, STRUCTURETYPE, SUBFACILITIES, MARK, JD, WD,WORTH,BUILDDATEEND,BUILDDATEBEGIN "
+ sb.ToString()
+ " order by ORGNOS,DC_UTILITY_CAMP_ID desc";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
/// <summary>
/// 保存
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Save(PEST_HARMCLASS_Model m)
{
List <string> sqllist = new List <string>();
string[] arrBYORGNO = m.BYORGNO.Split(',');
string[] arrTOWNNAME = m.TOWNNAME.Split(',');
string[] arrDCDATE = m.DCDATE.Split(',');
string[] arrJD = m.JD.Split(',');
string[] arrWD = m.WD.Split(',');
string[] arrHARMCLASS = m.HARMCLASS.Split(',');
if (arrBYORGNO.Length - 1 > 0)
{
#region 再更新
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendFormat(" INSERT INTO PEST_HARMCLASS(DCDATE, BYORGNO, TOWNNAME, JD, WD, HARMCLASS) ");
for (int i = 0; i < arrBYORGNO.Length - 1; i++)
{
#region 更新
if (isExists(new PEST_HARMCLASS_SW {
BYORGNO = arrBYORGNO[i], DCDATE = arrDCDATE[i]
}))
{
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat(" UPDATE PEST_HARMCLASS SET ");
sbUpdate.AppendFormat(" JD={0}", ClsSql.saveNullField(arrJD[i]));
sbUpdate.AppendFormat(",WD={0}", ClsSql.saveNullField(arrWD[i]));
sbUpdate.AppendFormat(",HARMCLASS={0}", ClsSql.saveNullField(arrHARMCLASS[i]));
sbUpdate.AppendFormat(" WHERE BYORGNO= '{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbUpdate.AppendFormat(" AND DCDATE= '{0}'", ClsSql.EncodeSql(arrDCDATE[i]));
sqllist.Add(sbUpdate.ToString());
}
#endregion
#region 添加
else
{
sbInsert.AppendFormat(" SELECT '{0}'", ClsSql.EncodeSql(arrDCDATE[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrTOWNNAME[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrJD[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrWD[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrHARMCLASS[i]));
sbInsert.AppendFormat(" UNION ALL ");
}
#endregion
}
string insertStr = sbInsert.ToString();
if (insertStr.Contains(" UNION ALL "))
{
insertStr = insertStr.Substring(0, insertStr.Length - 10);
sqllist.Add(insertStr);
}
#endregion
}
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y >= 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败,事物回滚!", ""));
}
}
/// <summary>
/// 保存
/// </summary>
/// <param name="m">参见模型</param>
/// <returns>参见模型</returns>
public static Message Save(PEST_REPORT_FORECAST_Model m)
{
List <string> sqllist = new List <string>();
string[] arrBYORGNO = m.BYORGNO.Split(',');
string[] arrFORECASTSTAGECODE = m.FORECASTSTAGECODE.Split(',');
string[] arrFORECASTAREA = m.FORECASTAREA.Split(',');
if (arrBYORGNO.Length - 1 > 0)
{
#region 先删除
StringBuilder sbDelete = new StringBuilder();
sbDelete.AppendFormat("delete from PEST_REPORT_FORECAST where 1=1");
sbDelete.AppendFormat(" and FORECASTYEAR='{0}'", m.FORECASTYEAR);
sbDelete.AppendFormat(" and PESTBYCODE='{0}'", m.PESTBYCODE);
if (m.TopORGNO.Substring(4, 11) == "00000000000") //所有市
{
sbDelete.AppendFormat(" and SUBSTRING(BYORGNO,1,4)='{0}'", m.TopORGNO.Substring(0, 4));
}
else if (m.TopORGNO.Substring(6, 9) == "000000000" && m.TopORGNO.Substring(4, 11) != "00000000000") //所有县
{
sbDelete.AppendFormat(" and SUBSTRING(BYORGNO,1,6)='{0}'", m.TopORGNO.Substring(0, 6));
}
else if (m.TopORGNO.Substring(9, 6) == "000000" && m.TopORGNO.Substring(6, 9) != "000000000") //所有乡镇
{
sbDelete.AppendFormat(" and SUBSTRING(BYORGNO,1,9)='{0}'", m.TopORGNO.Substring(0, 9));
}
else if (m.TopORGNO.Substring(9, 6) != "000000") //所有村
{
sbDelete.AppendFormat(" and SUBSTRING(BYORGNO,1,12)='{0}'", m.TopORGNO.Substring(0, 12));
}
else
{
sbDelete.AppendFormat(" and BYORGNO='{0}'", m.TopORGNO);
}
DataBaseClass.ExeSql(sbDelete.ToString());
#endregion
#region 再更新
StringBuilder sbInsert = new StringBuilder();
sbInsert.AppendFormat("INSERT INTO PEST_REPORT_FORECAST( BYORGNO, FORECASTYEAR, PESTBYCODE, FORECASTSTAGECODE, FORECASTAREA)");
for (int i = 0; i < arrBYORGNO.Length - 1; i++)
{
#region 更新
if (isExists(new PEST_REPORT_FORECAST_SW {
BYORGNO = arrBYORGNO[i], FORECASTYEAR = m.FORECASTAREA, PESTBYCODE = m.PESTBYCODE, FORECASTSTAGECODE = arrFORECASTSTAGECODE[i]
}))
{
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.AppendFormat("UPDATE PEST_REPORT_FORECAST SET ");
sbUpdate.AppendFormat(" FORECASTAREA='{0}',", ClsSql.saveNullField(arrFORECASTAREA[i]));
sbUpdate.AppendFormat(" where BYORGNO= '{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbUpdate.AppendFormat(" and FORECASTYEAR= '{0}'", ClsSql.EncodeSql(m.FORECASTYEAR));
sbUpdate.AppendFormat(" and PESTBYCODE= '{0}'", ClsSql.EncodeSql(m.PESTBYCODE));
sbUpdate.AppendFormat(" and FORECASTSTAGECODE= '{0}'", ClsSql.EncodeSql(arrFORECASTSTAGECODE[i]));
sqllist.Add(sbUpdate.ToString());
}
#endregion
#region 添加
else
{
sbInsert.AppendFormat(" select '{0}'", ClsSql.EncodeSql(arrBYORGNO[i]));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.FORECASTYEAR));
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(m.PESTBYCODE));;
sbInsert.AppendFormat(",'{0}'", ClsSql.EncodeSql(arrFORECASTSTAGECODE[i]));
sbInsert.AppendFormat(",{0}", ClsSql.saveNullField(arrFORECASTAREA[i]));
sbInsert.AppendFormat(" UNION ALL ");
}
#endregion
}
string insertStr = sbInsert.ToString();
if (insertStr.Contains(" UNION ALL "))
{
insertStr = insertStr.Substring(0, insertStr.Length - 10);
sqllist.Add(insertStr);
}
#endregion
}
var y = DataBaseClass.ExecuteSqlTran(sqllist);
if (y >= 0)
{
return(new Message(true, "保存成功!", ""));
}
else
{
return(new Message(false, "保存失败，事物回滚机制!", ""));
}
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="sw"></param>
/// <param name="total"></param>
/// <returns></returns>
public static DataTable getDT(DC_RESOURCE_NEW_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM DC_RESOURCE_NEW");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.DC_RESOURCE_NEW_ID) == false)
{
sb.AppendFormat(" AND DC_RESOURCE_NEW_ID = '{0}'", ClsSql.EncodeSql(sw.DC_RESOURCE_NEW_ID));
}
if (string.IsNullOrEmpty(sw.RESOURCETYPE) == false)
{
sb.AppendFormat(" AND RESOURCETYPE = '{0}'", ClsSql.EncodeSql(sw.RESOURCETYPE));
}
if (string.IsNullOrEmpty(sw.NUMBER) == false)
{
sb.AppendFormat(" AND NUMBER like '%{0}%'", ClsSql.EncodeSql(sw.NUMBER));
}
if (string.IsNullOrEmpty(sw.NAME) == false)
{
sb.AppendFormat(" AND NAME like '%{0}%'", ClsSql.EncodeSql(sw.NAME));
}
if (string.IsNullOrEmpty(sw.KINDTYPE) == false)
{
sb.AppendFormat(" AND KINDTYPE = '{0}'", ClsSql.EncodeSql(sw.KINDTYPE));
}
if (string.IsNullOrEmpty(sw.AGETYPE) == false)
{
sb.AppendFormat(" AND AGETYPE = '{0}'", ClsSql.EncodeSql(sw.AGETYPE));
}
if (string.IsNullOrEmpty(sw.ORIGINTYPE) == false)
{
sb.AppendFormat(" AND ORIGINTYPE = '{0}'", ClsSql.EncodeSql(sw.ORIGINTYPE));
}
if (string.IsNullOrEmpty(sw.BURNTYPE) == false)
{
sb.AppendFormat(" AND BURNTYPE = '{0}'", ClsSql.EncodeSql(sw.BURNTYPE));
}
if (string.IsNullOrEmpty(sw.TREETYPE) == false)
{
sb.AppendFormat(" AND TREETYPE = '{0}'", ClsSql.EncodeSql(sw.TREETYPE));
}
if (!string.IsNullOrEmpty(sw.ORGNOS))
{
if (sw.ORGNOS.Substring(4, 11) == "00000000000")//获取所有市的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,4) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 4)));
}
else if (sw.ORGNOS.Substring(4, 11) == "xxxxxxxxxxx")//单独市
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,15) = '{0}')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 4) + "00000000000"));
}
else if (sw.ORGNOS.Substring(6, 9) == "xxxxxxxxx")//获取所有县的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,6) = '{0}' or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 6)));
}
else if (sw.ORGNOS.Substring(9, 6) == "000000")//获取说有乡镇的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,9) = '{0}'or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 9)));
}
else if (sw.ORGNOS.Substring(12, 3) == "000")//获取说有村的
{
sb.AppendFormat(" AND (SUBSTRING(ORGNOS,1,12) = '{0}'or ORGNOS is null or ORGNOS='')", ClsSql.EncodeSql(sw.ORGNOS.Substring(0, 12)));
}
else
{
sb.AppendFormat(" AND ORGNOS = '{0}'", ClsSql.EncodeSql(sw.ORGNOS));
}
}
string sql = "SELECT DC_RESOURCE_NEW_ID, RESOURCETYPE, NUMBER, NAME, ORGNOS, KINDTYPE, AGETYPE, ORIGINTYPE, AREA, BURNTYPE, TREETYPE, ASPECT, ANGLE, MARK, JD, WD,POTHOOKLEADER,POTHOOKLEADERJOB,POTHOOKLEADERTLEE,DUTYPERSON,DUTYPERSONTELL,JWDLIST"
+ sb.ToString()
+ " order by ORGNOS ";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}
/// <summary>
/// 获取数据
/// </summary>
/// <returns>参见模型</returns>
public static DataTable getDT(T_IPSFR_USER_SW sw, out int total)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" FROM T_IPSFR_USER a");
sb.AppendFormat(" WHERE 1=1");
if (string.IsNullOrEmpty(sw.ISENABLE) == false)
{
sb.AppendFormat(" AND ISENABLE = '{0}'", ClsSql.EncodeSql(sw.ISENABLE));
}
if (string.IsNullOrEmpty(sw.HID) == false)
{
sb.AppendFormat(" AND HID = '{0}'", ClsSql.EncodeSql(sw.HID));
}
if (string.IsNullOrEmpty(sw.HNAME) == false)
{
sb.AppendFormat(" AND HNAME like '%{0}%'", ClsSql.EncodeSql(sw.HNAME));
}
if (string.IsNullOrEmpty(sw.SN) == false)
{
sb.AppendFormat(" AND SN like '%{0}%'", ClsSql.EncodeSql(sw.SN));
}
if (string.IsNullOrEmpty(sw.PHONE) == false)
{
sb.AppendFormat(" AND PHONE '%{0}%'", ClsSql.EncodeSql(sw.PHONE));
}
if (string.IsNullOrEmpty(sw.SEX) == false)
{
sb.AppendFormat(" AND SEX = '{0}'", ClsSql.EncodeSql(sw.SEX));
}
if (string.IsNullOrEmpty(sw.ONSTATE) == false)
{
sb.AppendFormat(" AND ONSTATE = '{0}'", ClsSql.EncodeSql(sw.ONSTATE));
}
if (string.IsNullOrEmpty(sw.MOBILEPARAMLIST) == false)
{
sb.AppendFormat(" AND MOBILEPARAMLIST = '{0}'", ClsSql.EncodeSql(sw.MOBILEPARAMLIST));
}
if (string.IsNullOrEmpty(sw.BYORGNO) == false)
{
if (PublicCls.OrgIsShi(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getShiIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsXian(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getXianIncOrgNo(sw.BYORGNO));
}
else if (PublicCls.OrgIsZhen(sw.BYORGNO))
{
sb.AppendFormat(" and BYORGNO like '{0}%'", PublicCls.getZhenIncOrgNo(sw.BYORGNO));
}
else
{
sb.AppendFormat(" and BYORGNO = '{0}'", sw.BYORGNO);
}
}
if (string.IsNullOrEmpty(sw.PhoneHname) == false)
{
sb.AppendFormat(" AND (a.PHONE like '%{0}%' or a.HNAME like '%{0}%')", ClsSql.EncodeSql(sw.PhoneHname));
}
string sql = "SELECT HID, HNAME, SN, PHONE, SEX, BIRTH, ONSTATE, BYORGNO,ISENABLE,PATROLLENGTH,MOBILEPARAMLIST"
+ sb.ToString()
+ " order by BYORGNO,HNAME";
string sqlC = "select count(1) " + sb.ToString();
total = int.Parse(DataBaseClass.ReturnSqlField(sqlC));
sw.curPage = PagerCls.getCurPage(new PagerSW {
curPage = sw.curPage, pageSize = sw.pageSize, rowCount = total
});
DataSet ds = DataBaseClass.FullDataSet(sql, (sw.curPage - 1) * sw.pageSize, sw.pageSize, "a");
return(ds.Tables[0]);
}