/// <summary>
/// 统计-县-段道级分发
/// </summary>
/// <param name="BKDH">报刊代号</param>
/// <param name="chooseorg">所选机构ID</param>
/// <param name="userorg">用户所属机构ID</param>
/// <param name="Group_Type">排序方式:1根据报刊名称排序.2根据机构排序</param>
/// <param name="dt1">分发日期</param>
/// <param name="type">如果为0,则表示分发行为;如果为1,则表示查看日志</param>
/// <returns></returns>
public DataTable GetTable2(string BKDH, string chooseorg, string userorg, string Group_Type, string dt1, int limit, int page, int type = 0)
{
DataTable dt = new DataTable();
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userorg, chooseorg);
string wheresql = " AND OrderPeople.ORGID in (" + all + @") AND t.STATE=0 ";
SqlParameter Para = null;
if (!string.IsNullOrEmpty(BKDH))
{
wheresql += " AND upper(t.BKDH)=@BKDH";
Para = new SqlParameter("BKDH", BKDH.ToUpper());
dbhelper.SqlParameterList.Add(Para);
}
string logsql = "";
logsql += " and log.type=0 ";
string sql = @"select ROW_NUMBER() over (order by a.DocName) as rownumber,a.BKDH,a.DocName,a.OrgName ,SUM(a.OrderNum) OrderNum,ids=STUFF((SELECT ','+ltrim([order].ID) FROM [order]
WHERE PersonID=a.PersonID and BKDH=a.BKDH FOR XML PATH('')), 1, 1, '') from (
select t.ID ,t.BKDH,Doc.Name DocName,Org.Name OrgName ,t.OrderNum,t.PersonID from [Order] t
left join OrderPeople on t.PersonID=OrderPeople.ID
left join Org on org.OrgID=OrderPeople.OrgID
left join Doc on Doc.BKDH=t.BKDH
where 1=1 " + wheresql + @" and dateadd(MONTH,t.OrderMonths,t.OrderDate)>CONVERT(varchar(100), @INDATE1, 23)
and exists(
select 1 from log where log.orderid=t.id and
CONVERT(varchar(100), log.date, 23)=CONVERT(varchar(100), @INDATE1, 23) " + logsql + @"
)
) a
GROUP BY a.BKDH,a.DocName,a.OrgName ,a.PersonID ";
if (Group_Type == "1")
{
sql += " order by a.DocName";
}
if (Group_Type == "2")
{
sql += " order by a.OrgName";
}
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
Para = new SqlParameter("INDATE1", dt1._ToDateTime()._ToStr("yyyy-MM-dd"));
dbhelper.SqlParameterList.Add(Para);
}
else
{
Para = new SqlParameter("INDATE1", System.DateTime.Now.ToShortDateString()._ToStr("yyyy-MM-dd"));
dbhelper.SqlParameterList.Add(Para);
}
string top = "";
if (limit > 0)
{
top = " top " + limit;
}
dt = dbhelper.ExecuteSql("select " + top + " * from (" + sql + ") tttt where tttt.rownumber>" + limit * (page - 1));
return(dt);
}
public int GetCount(int ID, string OrderNo, string UnitName, string name,
string OrgID, string dt1, string dt2, string orgid)
{
string sql = "";
sql = string.Format(@"select count(1) as count FROM OrderPeople
LEFT JOIN USERS ON OrderPeople.InUser=USERS.ID
LEFT JOIN Org ON Org.OrgID=OrderPeople.OrgID WHERE 1=1 ");
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(orgid, OrgID._ToStr());
sql += " AND OrderPeople.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(OrderNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("OrderNo", OrderNo._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.OrderNo LIKE '%'+@OrderNo+'%'";
}
if (!string.IsNullOrEmpty(UnitName._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("UnitName", UnitName._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.UnitName LIKE '%'+@UnitName+'%'";
}
if (!string.IsNullOrEmpty(name._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("name", name._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.Name LIKE '%'+@name+'%'";
}
if (ID > 0)
{
SqlParameter Para = new SqlParameter("ID", ID._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.ID =@ID";
}
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE1", dt1._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),OrderPeople.INDATE, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(dt2._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE2", dt2._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),OrderPeople.INDATE, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
int count = dbhelper.Count(sql);
return(count);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="id">缴费单据流水号</param>
/// <param name="state">0:未缴清,1:已缴清</param>
/// <param name="OrderNo">单位编号</param>
/// <param name="unitname">单位名称</param>
/// <param name="dt1">录入时间</param>
/// <param name="dt2">录入时间</param>
/// <param name="pagesize">每页显示数量</param>
/// <param name="pageindex">页码</param>
/// <returns></returns>
public DataTable GetCostRecords(int id, string state, int orderid, string OrderNo, string unitname, int pagesize, int pageindex, int userOrg = 0)
{
DataTable dt = new DataTable();
string sql = string.Format(@"select top {0} * from (
select a.id,a.orderid,c.UnitName,c.OrderNo,a.money,
a.moneypayed,CONVERT(varchar(100), a.updatetime, 23)updatetime,d.NAME updateuser ,
b.indate,b.ordermonths,b.ordernum,
ROW_NUMBER() over (order by a.ID) as rownumber ,
case isnull(b.state,0) when 0 then '正常' when -1 then '退订' when 1 then '过期' end as OrderState,
case ISNULL(a.state,0) when 0 then '已缴清' when '1' then '未缴清' when -1 then '退订未处理' when '-2' then '退订已处理'
end as CostState from cost a
left join [Order] b on a.orderid=b.ID
left join OrderPeople c on b.PersonID=c.ID
left join USERS d on a.updateuser=d.ID where 1=1 ", pagesize);
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userOrg._ToStr(), "");
sql += " AND c.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(OrderNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("OrderNo", OrderNo._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND upper(c.OrderNo) LIKE '%'+@OrderNo+'%'";
}
if (id > 0)
{
SqlParameter Para = new SqlParameter("ID", id._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.ID =@ID";
}
if (orderid > 0)
{
SqlParameter Para = new SqlParameter("orderid", orderid._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND b.ID =@orderid";
}
if (!string.IsNullOrEmpty(state._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("state", state._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.state=@state";
}
if (!string.IsNullOrEmpty(unitname._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("unitname", unitname._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND upper(c.UnitName) LIKE '%'+@unitname+'%'";
}
dt = dbhelper.ExecuteSql(sql + string.Format(") t where rownumber > {0} ", (pageindex - 1) * pagesize));
return(dt);
}
/// <summary>
/// 获取数据总数
/// </summary>
/// <param name="id"></param>
/// <param name="OrderNo"></param>
/// <param name="unitname"></param>
/// <param name="dt1"></param>
/// <param name="dt2"></param>
/// <returns></returns>
public DataTable GetCount(int id, string state, int orderid, string OrderNo, string unitname, int userOrg = 0)
{
string sql = string.Format(@"
select count(1) as counts,SUM(b.OrderMonths)OrderMonths,SUM(b.OrderNum)OrderNum,
SUM(a.Money)Money,SUM(a.MoneyPayed)MoneyPayed
from cost a
left join [Order] b on a.orderid=b.ID
left join OrderPeople c on b.PersonID=c.ID
left join USERS d on a.updateuser=d.ID where 1=1 ");
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userOrg._ToStr(), "");
sql += " AND c.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(OrderNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("OrderNo", OrderNo._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND upper(c.OrderNo) LIKE '%'+@OrderNo+'%'";
}
if (id > 0)
{
SqlParameter Para = new SqlParameter("ID", id._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.ID =@ID";
}
if (orderid > 0)
{
SqlParameter Para = new SqlParameter("orderid", orderid._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND b.ID =@orderid";
}
if (!string.IsNullOrEmpty(state._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("state", state._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.state=@state";
}
if (!string.IsNullOrEmpty(unitname._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("unitname", unitname._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND upper(c.UnitName) LIKE '%'+@unitname+'%'";
}
DataTable dt = dbhelper.ExecuteSql(sql);
return(dt);
}
/// <summary>
/// 统计-市-县级分发历史
/// </summary>
/// <param name="dt1">分发日期</param>
/// <param name="dt2">分发日期</param>
/// <param name="userid">分发员</param>
/// <returns></returns>
public DataTable getlog1(string dt1, string dt2, int userorg, string userid, int limit = 0, int index = 0)
{
DataTable dt = new DataTable();
//OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
//string all = orgInfoDAL.getChilds(userorg, chooseorg);
SqlParameter Para = null;
string logsql = "";
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
Para = new SqlParameter("INDATE1", dt1._ToDateTime()._ToStr("yyyy-MM-dd"));
dbhelper.SqlParameterList.Add(Para);
logsql += " AND CONVERT(varchar(100),log.date, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(dt2._ToStrTrim()))
{
Para = new SqlParameter("INDATE2", dt2._ToDateTime()._ToStr("yyyy-MM-dd"));
dbhelper.SqlParameterList.Add(Para);
logsql += " AND CONVERT(varchar(100),log.date, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
if (!string.IsNullOrEmpty(userid._ToStrTrim()))
{
Para = new SqlParameter("userid", userid._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
logsql += " AND log.userid=@userid ";
}
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userorg._ToStr(), "");
logsql += " AND e.ORGID in (" + all + ")";
string sql = @"select ROW_NUMBER() over (order by log.ID) as rownumber, log.ID,log.nianjuanqi,CONVERT(VARCHAR, log.Date,120) Date,c.name as ffname, f.Name as OrgName,
e.UnitName UnitName,d.Name,
case Log.type when 0 then '市级分发' else '县级分发' end as type,b.BKDH,b.OrderNum from log
left join [Order] b on Log.OrderID=b.ID
left join USERS c on c.ID=Log.UserID
left join Doc d on d.BKDH=b.BKDH
left join OrderPeople e on b.PersonID=e.ID
left join Org f on f.OrgID=e.OrgID where Log.TYPE=0 AND 1=1 " + logsql;
string top = "";
if (limit > 0)
{
top = " top " + limit;
}
dt = dbhelper.ExecuteSql(" select " + top + " a.* from (" + sql + " )a where a.rownumber>" + limit * index);
return(dt);
}
public DataTable GetPosters(string orgid, int role = 3)
{
DataTable dt = new DataTable();
string sql = @"SELECT USERS.ID, USERS.USERNO, USERS.NAME
FROM USERS WHERE 1=1 ";
//这里加载当前登录人可以操作的用户
if (!string.IsNullOrEmpty(orgid._ToStrTrim()))
{
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string ids = orgInfoDAL.getChilds(orgid, "");
sql += " AND USERS.ORGID in (" + ids + ") ";
}
sql += " AND USERS.ROLE=" + role + @" AND STATE=0";
dt = dbhelper.ExecuteSql(sql + " ORDER BY USERNO");
return(dt);
}
public DataTable GetCount(string BKDH, string chooseorg, string userorg, string Group_Type, string dt1, bool area = false)
{
DataTable dt = new DataTable();
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userorg, chooseorg);
string wheresql = " AND OrderPeople.ORGID in (" + all + @") AND t.STATE=0";
SqlParameter Para = null;
if (!string.IsNullOrEmpty(BKDH))
{
wheresql += " AND upper(t.BKDH)=@BKDH";
Para = new SqlParameter("BKDH", BKDH.ToUpper());
dbhelper.SqlParameterList.Add(Para);
}
if (area)
{
wheresql += @" and exists(
select 1 from log where log.orderid=t.id and
CONVERT(varchar(100), log.date, 23)=CONVERT(varchar(100), @INDATE1, 23) and log.type=0
) ";
}
string sql = string.Format(@"
select sum(t.OrderNum)OrderNum from [Order] t
left join OrderPeople on t.PersonID=OrderPeople.ID
left join Org on org.OrgID=OrderPeople.OrgID
where dateadd(MONTH,t.OrderMonths,t.OrderDate)>CONVERT(varchar(100), @INDATE1, 23)
and CONVERT(varchar(100),t.OrderDate, 23)<=CONVERT(varchar(100),getdate(), 23)
" + wheresql);
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
Para = new SqlParameter("INDATE1", dt1._ToDateTime()._ToStr("yyyy-MM-dd"));
dbhelper.SqlParameterList.Add(Para);
}
else
{
Para = new SqlParameter("INDATE1", System.DateTime.Now.ToShortDateString()._ToStr("yyyy-MM-dd"));
dbhelper.SqlParameterList.Add(Para);
}
dt = dbhelper.ExecuteSql(sql);
return(dt);
}
/// <summary>
/// 插入用户
/// </summary>
/// <param name="userNo">用户编号</param>
/// <param name="userName">名称</param>
/// <param name="sex">性别,1男0女</param>
/// <param name="userRole">角色</param>
/// <param name="userOrg">用户所属机构</param>
/// <param name="IDCard">证件号</param>
/// <param name="Password">密码</param>
/// <param name="PhoneNumber">电话</param>
/// <param name="Address">地址</param>
/// <param name="Email">邮箱</param>
/// <param name="OPERATOR">操作者</param>
/// <returns>成功返回空值,否则返回提示</returns>
public string Insert(string userNo, string userName, string sex, string userRole, string chooseOrg,
string IDCard, string Password, string PhoneNumber, string Address, string Email, string OPERATOR, int userlevel)
{
string res = "";
try
{
res = checkUser(userNo);
if (!string.IsNullOrEmpty(res))
{
return(res);
}
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
int level = orgInfoDAL.GetOrgByPK(chooseOrg._ToInt32()).Rows[0]["Level"]._ToInt32();
if (level < userlevel)
{
res = "你不能添加当前机构下的用户";
return(res);
}
string sql = @"INSERT INTO USERS(USERNO, NAME, SEX,ROLE,ORGID, PASSWORD, IDCARD, PHONENUMBER,
ADDRESS, EMAIL, OPERATOR, INDATE) VALUES (@USERNO, @NAME, @SEX,@ROLE,@ORGID, @PASSWORD, @IDCARD, @PHONENUMBER,
@ADDRESS, @EMAIL, @OPERATOR, GETDATE())";
SqlParameter Para = new SqlParameter("USERNO", userNo._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("NAME", userName._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("SEX", sex._ToInt32());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("ROLE", userRole._ToInt32());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("ORGID", chooseOrg._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("PASSWORD", Password._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("IDCARD", IDCard._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("PHONENUMBER", PhoneNumber._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("ADDRESS", Address._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("EMAIL", Email._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
Para = new SqlParameter("OPERATOR", OPERATOR._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
int num = dbhelper.ExecuteNonQuery(sql);
if (num == 0)
{
res = "操作失败";
}
}
catch (Exception ex)
{
res = ex.Message;
}
return(res);
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="userNo">用户编号</param>
/// <param name="userName">名称</param>
/// <param name="sex">性别,1男0女</param>
/// <param name="userRole">角色</param>
/// <param name="userOrg">用户所属机构</param>
/// <param name="IDCard">证件号</param>
/// <param name="userState">状态(0有效,1无效)</param>
/// <param name="userRegData_Begin">用户注册时间范围,起始值</param>
/// <param name="userRegData_End">用户注册时间范围,截止值</param>
/// <returns></returns>
public DataTable GetUser(string userNo, string userName, string sex, string userRole, string orgid,
string IDCard, string userState, string userRegData_Begin, string userRegData_End, string userOrg, int limit, int page)
{
DataTable dt = new DataTable();
string top = "";
if (limit > 0)
{
top = " top " + limit;
}
string sql = @"select " + top + @" * from ( SELECT ROW_NUMBER() over (order by USERS.ID) as rownumber , USERS.ID, USERS.USERNO, USERS.NAME, ISNULL(ROLE.ROLENAME, '未知角色') ROLE,
USERS.PASSWORD,
CASE USERS.SEX
WHEN 0 THEN
'女'
ELSE
'男'
END AS SEX, ISNULL(ORG.NAME, '未知机构') ORGNAME,
ISNULL(USERS.IDCARD, '') IDCARD, ISNULL(USERS.PHONENUMBER, '') PHONENUMBER,
ISNULL(USERS.ADDRESS, '') ADDRESS, ISNULL(USERS.EMAIL, '') EMAIL,
CASE USERS.STATE
WHEN 0 THEN
'有效'
ELSE
'无效'
END AS STATE,B.NAME OPERATOR,
CONVERT(varchar(100), USERS.INDATE, 23) INDATE, USERS.MGUID
FROM USERS
LEFT JOIN ORG ON ORG.ORGID = USERS.ORGID
LEFT JOIN ROLE ON ROLE.ROLEID = USERS.ROLE
LEFT JOIN USERS B ON B.ID=USERS.Operator WHERE 1=1 ";
if (!string.IsNullOrEmpty(userNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("USERNO", userNo._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.USERNO LIKE @USERNO+'%'";
}
if (!string.IsNullOrEmpty(userName._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("NAME", userName._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.NAME LIKE '%'+@NAME+'%'";
}
if (!string.IsNullOrEmpty(sex._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("SEX", sex._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.SEX =@SEX";
}
if (!string.IsNullOrEmpty(userRole._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("ROLE", userRole._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.ROLE =@ROLE";
}
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userOrg, orgid);
sql += " AND USERS.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(IDCard._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("IDCARD", IDCard._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.IDCARD =@IDCARD";
}
if (!string.IsNullOrEmpty(userState._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("STATE", userState._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.STATE =@STATE";
}
if (!string.IsNullOrEmpty(userRegData_Begin._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE1", userRegData_Begin._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),USERS.INDATE, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(userRegData_End._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE2", userRegData_End._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),USERS.INDATE, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
dt = dbhelper.ExecuteSql(sql + ") b where b.rownumber>" + limit * (page - 1));
return(dt);
}
public int GetUserCount(string userNo, string userName, string sex, string userRole, string orgid,
string IDCard, string userState, string userRegData_Begin, string userRegData_End, string userOrg)
{
string sql = @"SELECT count(1)
FROM USERS
LEFT JOIN ORG ON ORG.ORGID = USERS.ORGID
LEFT JOIN ROLE ON ROLE.ROLEID = USERS.ROLE
LEFT JOIN USERS B ON B.ID=USERS.Operator WHERE 1=1 ";
if (!string.IsNullOrEmpty(userNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("USERNO", userNo._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.USERNO LIKE @USERNO+'%'";
}
if (!string.IsNullOrEmpty(userName._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("NAME", userName._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.NAME LIKE '%'+@NAME+'%'";
}
if (!string.IsNullOrEmpty(sex._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("SEX", sex._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.SEX =@SEX";
}
if (!string.IsNullOrEmpty(userRole._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("ROLE", userRole._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.ROLE =@ROLE";
}
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(userOrg, orgid);
sql += " AND USERS.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(IDCard._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("IDCARD", IDCard._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.IDCARD =@IDCARD";
}
if (!string.IsNullOrEmpty(userState._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("STATE", userState._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND USERS.STATE =@STATE";
}
if (!string.IsNullOrEmpty(userRegData_Begin._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE1", userRegData_Begin._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),USERS.INDATE, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(userRegData_End._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE2", userRegData_End._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),USERS.INDATE, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
return(dbhelper.Count(sql));
}
/// <summary>
/// 查询
/// </summary>
/// <param name="docname">报刊名称</param>
/// <param name="OrderNo">单位编号</param>
/// <param name="unitname">单位名称</param>
/// <param name="dt1">录入时间</param>
/// <param name="dt2">录入时间</param>
/// <param name="orgid">当前登录用户所属机构ID</param>
/// <param name="chooseorg">界面上机构选择的</param>
/// <param name="pagesize">每页显示数量</param>
/// <param name="pageindex">页码</param>
/// <returns></returns>
public DataTable GetOrderInfo(int id, string BKDH, string OrderNo, string unitname, string dt1, string dt2,
int pagesize, int pageindex, string orgid, string chooseorg, string orderstate, string coststate)
{
DataTable dt = new DataTable();
string top = "";
if (pagesize > 0)
{
top = "top " + pagesize;
}
string sql = string.Format(@"select " + top + @" * from (
select a.ID ,b.Name docname ,a.BKDH,c.UnitName,c.Name ToUser,a.OrderDate,a.OrderMonths,
a.OrderNum,CONVERT(varchar(100), a.Indate, 23) Indate,a.PosterID,d.Name as GetUser,e.NAME as InUser ,a.PersonID,a.NGUID,Cost.Money,Cost.MoneyPayed,Cost.ID as CostID,
ROW_NUMBER() over (order by a.ID) as rownumber,b.Price, case isnull(a.state,0) when 0 then '正常' when -1 then '退订' when 1 then '过期' end as OrderState,
case ISNULL(Cost.state,0) when 0 then '已缴清' when '1' then '未缴清' when '-1' then '退订未处理' when '-2' then '退订已处理' end as CostState
from [Order] a
inner join dbo.OrderPeople c on a.PersonID=c.ID
left join Cost on Cost.OrderID=a.ID
left join dbo.Doc b on a.BKDH=b.BKDH
left join dbo.USERS d on a.PosterID=d.ID
left join dbo.USERS e on a.userid=e.ID
where 1=1 ");
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(orgid, chooseorg);
sql += " AND c.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(OrderNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("OrderNo", OrderNo._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND c.OrderNo LIKE '%'+@OrderNo+'%'";
}
if (id > 0)
{
SqlParameter Para = new SqlParameter("ID", id._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.ID =@ID";
}
if (!string.IsNullOrEmpty(unitname._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("unitname", unitname._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND c.UnitName LIKE '%'+@unitname+'%'";
}
if (!string.IsNullOrEmpty(BKDH._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("BKDH", BKDH._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.BKDH=@BKDH";
}
if (!string.IsNullOrEmpty(orderstate._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("orderstate", orderstate._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND ISNULL(a.state,0)=@orderstate";
}
if (!string.IsNullOrEmpty(coststate._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("coststate", coststate._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND ISNULL(Cost.state,0)=@coststate";
}
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE1", dt1._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),a.INDATE, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(dt2._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE2", dt2._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),a.INDATE, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
dt = dbhelper.ExecuteSql(sql + string.Format(") t where rownumber > {0} ", (pageindex - 1) * pagesize));
return(dt);
}
public DataTable GetCount(int id, string BKDH, string OrderNo, string unitname,
string dt1, string dt2, string orgid, string chooseorg, string orderstate, string coststate)
{
string sql = string.Format(@"
select count(1) as num,sum(a.OrderMonths) as OrderMonths,sum(a.OrderNum) as OrderNum,
sum(Cost.Money) as Money,
sum(Cost.MoneyPayed) as MoneyPayed
from [Order] a
inner join dbo.OrderPeople c on a.PersonID=c.ID
left join Cost on Cost.OrderID=a.ID
left join dbo.Doc b on a.BKDH=b.BKDH
left join dbo.USERS d on a.PosterID=d.ID
left join dbo.USERS e on a.userid=d.ID
where 1=1 ");
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(orgid, chooseorg);
sql += " AND c.ORGID in (" + all + ")";
if (!string.IsNullOrEmpty(OrderNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("OrderNo", OrderNo._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND c.OrderNo LIKE '%'+@OrderNo+'%'";
}
if (!string.IsNullOrEmpty(orderstate._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("orderstate", orderstate._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.state=@orderstate";
}
if (!string.IsNullOrEmpty(coststate._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("coststate", coststate._ToStrTrim());
dbhelper.SqlParameterList.Add(Para);
sql += " AND Cost.state=@coststate";
}
if (id > 0)
{
SqlParameter Para = new SqlParameter("ID", id._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.ID =@ID";
}
if (!string.IsNullOrEmpty(unitname._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("unitname", unitname._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND c.UnitName LIKE '%'+@unitname+'%'";
}
if (!string.IsNullOrEmpty(BKDH._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("BKDH", BKDH._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND a.BKDH=@BKDH";
}
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE1", dt1._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),a.INDATE, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(dt2._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE2", dt2._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),a.INDATE, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
DataTable dt = dbhelper.ExecuteSql(sql);
return(dt);
}
/// <summary>
/// 获取订户
/// </summary>
/// <param name="ID">主键</param>
/// <param name="OrderNo">公司代码,一般是纳税号</param>
/// <param name="UnitName">单位名称</param>
/// <param name="name">负责人名称</param>
/// <param name="OrgID">所属网点</param>
/// <param name="dt1">录入时间</param>
/// <param name="dt2">录入时间</param>
/// <returns>返回DataTable</returns>
public DataTable GetSubscriber(int ID, string OrderNo, string UnitName, string name,
string OrgID, string dt1, string dt2, string orgid, int pageLimit = 0, int pageIndex = 0)
{
DataTable dt = new DataTable();
string sql = "";
string top = "";
if (pageLimit > 0)
{
top = " top " + pageLimit;
}
sql = string.Format(@"SELECT " + top + @" * from (select OrderPeople.ID,OrderPeople.OrderNo,OrderPeople.UnitName,
OrderPeople.Name,OrderPeople.Phone,OrderPeople.Address,OrderPeople.MGuid,
ROW_NUMBER() over (order by OrderPeople.ID) as rownumber,
USERS.NAME InUser,CONVERT(varchar(100),OrderPeople.INDATE, 23) Indate,
Org.Name OrgName FROM OrderPeople
LEFT JOIN USERS ON OrderPeople.InUser=USERS.ID
LEFT JOIN Org ON Org.OrgID=OrderPeople.OrgID WHERE 1=1 ");
if (!string.IsNullOrEmpty(OrderNo._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("OrderNo", OrderNo._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.OrderNo LIKE '%'+@OrderNo+'%'";
}
if (!string.IsNullOrEmpty(UnitName._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("UnitName", UnitName._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.UnitName LIKE '%'+@UnitName+'%'";
}
if (!string.IsNullOrEmpty(name._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("name", name._ToStrTrim().ToUpper());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.Name LIKE '%'+@name+'%'";
}
OrgInfoDAL orgInfoDAL = new OrgInfoDAL();
string all = orgInfoDAL.getChilds(orgid, OrgID._ToStr());
sql += " AND OrderPeople.ORGID in (" + all + ")";
if (ID > 0)
{
SqlParameter Para = new SqlParameter("ID", ID._ToInt32());
dbhelper.SqlParameterList.Add(Para);
sql += " AND OrderPeople.ID =@ID";
}
if (!string.IsNullOrEmpty(dt1._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE1", dt1._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),OrderPeople.INDATE, 23) >= CONVERT(varchar(100),@INDATE1, 23)";
}
if (!string.IsNullOrEmpty(dt2._ToStrTrim()))
{
SqlParameter Para = new SqlParameter("INDATE2", dt2._ToDateTime());
dbhelper.SqlParameterList.Add(Para);
sql += " AND CONVERT(varchar(100),OrderPeople.INDATE, 23) <= CONVERT(varchar(100),@INDATE2, 23) ";
}
dt = dbhelper.ExecuteSql(sql + string.Format(") OrderPeople where rownumber > {0} ", (pageIndex - 1) * pageLimit));
return(dt);
}
