/// <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); }