/// <summary>
/// 获取其他缴费的一览json
/// </summary>
/// <param name="param"></param>
/// <param name="aCharge"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> AnotherChargeSearch(EasyUIGridParamModel param, AnotherCharge aCharge, out int itemCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT ac.ID, ac.CustomerName, ac.Money, ac.ActMoney, ac.ChargeDate, ac.Remark,
te.Name AS OperatorName
FROM T_AnotherCharge ac
LEFT JOIN T_operator o
ON o.ID = ac.OperatorID
LEFT JOIN T_EMPLOYEE te
ON o.EmployeeID = te.ID
WHERE 1 = 1 ");
Dictionary<string, object> paramList = new Dictionary<string, object>();
if (aCharge.Status != -1)
{
strSql.Append("and ac.status=@status ");
paramList.Add("Status", aCharge.Status);
}
using (DBHelper db = DBHelper.Create())
{
itemCount = db.GetCount(strSql.ToString(), paramList);
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
return db.GetDynaminObjectList(strSql.ToString(), pageIndex, pageSize, "ID", paramList);
}
}
/// <summary>
/// 协议用户欠费统计
/// </summary>
/// <param name="param"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public List<dynamic> AnalysisAgreeArrear(EasyUIGridParamModel param, out int pageCount)
{
string strSql = @"";
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
pageCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, strSql), null);
return db.GetDynaminObjectList(strSql, null);
}
}
public ActionResult AnotherChargeSearch(EasyUIGridParamModel param, AnotherCharge aCharge)
{
int itemCount = 0;
List<dynamic> chargeList = new AnotherChargeRule().AnotherChargeSearch(param, aCharge, out itemCount);
var showList = from anotherCharge in chargeList
select new
{
ID = anotherCharge.ID,
CustomerName = anotherCharge.CUSTOMERNAME,
Money = anotherCharge.MONEY,
ActMoney = anotherCharge.ACTMONEY,
Remark = anotherCharge.REMARK,
ChargeDate = TimeParser.FormatDateTime(anotherCharge.CHARGEDATE),
OperatorName = anotherCharge.OPERATORNAME,
Detail = "查看详细"
};
return Json(new { total = chargeList.Count, rows = showList }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 票据一览查询
/// </summary>
/// <param name="param"></param>
/// <param name="strWhere"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel param, string strWhere, out int itemCount)
{
string strSql = @"select ir.*,e.Name,it.Name as InvoiceTypeName from T_InvoiceRegister ir
left join t_Employee e on ir.OperatorID=e.ID
left join t_InvoiceType it on it.ID=ir.InvoiceType
where 1=1 ";
if (!string.IsNullOrEmpty(strWhere))
{
strSql += strWhere;
}
using (DBHelper db = DBHelper.Create())
{
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, strSql), null);
return db.GetDynaminObjectList(strSql, pageIndex, pageSize, "ID", null);
}
}
/// <summary>
/// 非协议用户欠费统计
/// </summary>
/// <param name="param"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public List<dynamic> AnalysisNoAgreeArrear(EasyUIGridParamModel param, out int pageCount)
{
string strSql = @"SELECT tc.ID,tc.Name,isnull(ta.[Money],0) agreeMoney,
isnull(sum(tcharge.[Money]),0) feeMoney
FROM T_Customer tc
INNER JOIN T_Agreements ta ON tc.id=ta.CustomerID AND ta.[Status]=1
LEFT JOIN T_Charge tcharge ON tcharge.CustomerID=tc.ID
WHERE tc.[Status]=1 AND ta.[Status]=1
GROUP BY tc.ID,tc.Name,ta.[Money]
HAVING isnull(sum(tcharge.[Money]),0)<isnull(ta.[Money],0)";
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
pageCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, strSql), null);
return db.GetDynaminObjectList(strSql, null);
}
}
/// <summary>
/// 查询付费单位列表json
/// </summary>
/// <param name="param"></param>
/// <param name="unit"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<object> GetSerachJson(EasyUIGridParamModel param, Unit unit, out int itemCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,NAME,CASE tu.LEVEL WHEN 1 THEN '计量' else '计时' end as level,timevalue ");
strSql.Append("FROM T_Unit tu where 1=1 ");
Dictionary<string, object> paramList = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(unit.Name))
{
strSql.Append("and (Name like @Name or PY like @PY)");
paramList.Add("Name", string.Format("%{0}%", unit.Name));
paramList.Add("PY", string.Format("%{0}%", Pinyin.GetPinyin(unit.Name)));
}
if (unit.Status != null)
{
strSql.Append("and Status=@Status ");
paramList.Add("Status", unit.Status);
}
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
string sql = strSql.ToString();
itemCount = db.GetCount(sql, paramList);
return db.GetDynaminObjectList(sql, pageIndex, pageSize, "ID", paramList);
}
}
/// <summary>
/// 获取缴费信息表json
/// </summary>
/// <param name="customerID">客户ID</param>
/// <returns></returns>
public List<dynamic> ChargeSearch(string customerID, EasyUIGridParamModel param, out int itemCount)
{
StringBuilder strSql = new StringBuilder();
string sql = @"SELECT tc.ID,
tc.CustomerID,
tc.[Money],
tc.CreateDate,
tc.OperatorID,
CASE tc.IsAgreementCharge
WHEN 0 THEN '否'
WHEN 1 THEN '是'
END AS IsAgreementCharge,
tc.BeginDate,
tc.EndDate,
CASE tc.[Status]
WHEN 0 THEN '未审核'
WHEN 1 THEN '已审核'
WHEN 2 THEN '已退费'
WHEN 3 THEN '无效'
END AS STATUS,
te.Name AS OperatorName,
ta.Code,
tc2.Name AS customerName
FROM T_Charge tc
INNER JOIN T_Customer tc2
ON tc2.ID = tc.CustomerID
INNER JOIN T_Operator to1
ON to1.ID = tc.OperatorID
INNER JOIN T_Employee te ON te.id = to1.EmployeeID
LEFT JOIN T_Agreements ta
ON tc.AgreementID = ta.ID
where 1=1 and (tc.status = 0) {0}";
string sqlCount = @"select count(0) FROM T_Charge tc
INNER JOIN T_Customer tc2
ON tc2.ID = tc.CustomerID
INNER JOIN T_Operator to1
ON to1.ID = tc.OperatorID
INNER JOIN T_Employee te ON te.id = to1.EmployeeID
LEFT JOIN T_Agreements ta
ON tc.AgreementID = ta.ID
where 1=1 and (tc.status = 0 ) {0}";
using (DBHelper db = DBHelper.Create())
{
StringBuilder strWhere = new StringBuilder();
Dictionary<string, object> paramss = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(customerID))
{
strWhere.Append(" and tc.CustomerID = @CUSTOMERID ");
paramss.Add("CustomerID", customerID);
}
itemCount = db.GetCount(string.Format(sqlCount, strWhere.ToString()), paramss);
return db.GetDynaminObjectList(string.Format(sql, strWhere.ToString()), Convert.ToInt32(param.page) - 1, Convert.ToInt32(param.rows), "", paramss);
}
}
/// <summary>
/// 获取期初欠费一览
/// </summary>
/// <param name="param"></param>
/// <param name="fMoney"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel param, FirstMoney fMoney, out int itemCount)
{
Dictionary<string, object> DicParam = new Dictionary<string, object>();
string strSql = @"select fm.*,c.Name from T_FirstMoney fm
left join T_Customer c on c.ID=fm.CustomerID
where 1=1 ";
if (!string.IsNullOrEmpty(fMoney.CustomerID))
{
strSql+=" and fm.CustomerID=@customerID";
DicParam.Add("customerID", fMoney.CustomerID);
}
if (fMoney.Status!=-1)
{
strSql += " and fm.Status=@Status";
DicParam.Add("Status", fMoney.Status);
}
//分页信息
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, strSql), DicParam);
return db.GetDynaminObjectList(strSql, pageIndex, pageSize, "ID", DicParam);
}
}
/// <summary>
/// 客户查询
/// </summary>
/// <param name="pageModel">分页参数</param>
/// <param name="customer">查询实体</param>
/// <param name="itemCount">查询结果总数</param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel pageModel, Customer customer, out int itemCount)
{
Dictionary<string, object> param = new Dictionary<string, object>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select tc.ID,tc.Name,tct.Name as customerType,tc.createTime,tc.Status,
Contactor,Address,a.ID as agreeID
FROM T_Customer tc
left join T_CustomerType tct on tc.TypeID=tct.ID
left join T_Agreements a on a.CustomerID=tc.ID and a.status=1
where 1=1 ");
//前台三个查询条件
if (!string.IsNullOrEmpty(customer.Name))
{
string str = string.Format("%{0}%", customer.Name.ToUpper());
strSql.Append(" and (tc.Name like @name or tc.PY like @PY)");
param.Add("name", str);
param.Add("PY", str);
}
if (customer.Status != -1)
{
strSql.Append(" and tc.status=@status ");
param.Add("status", customer.Status);
}
if (!string.IsNullOrEmpty(customer.AreaID))
{
strSql.Append(" and tc.areaID=@areaID ");
param.Add("status", customer.AreaID);
}
//分页信息
int pageIndex = Convert.ToInt32(pageModel.page) - 1;
int pageSize = Convert.ToInt32(pageModel.rows);
using (DBHelper db = DBHelper.Create())
{
string sql = strSql.ToString();
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, sql), param);
return db.GetDynaminObjectList(sql, pageIndex, pageSize, "ID", param);
}
}
/// <summary>
/// 日志查询
/// </summary>
/// <param name="param"></param>
/// <param name="OperatorID"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel param, string OperatorID, DateTime startTime, DateTime endTime, out int itemCount)
{
return dal.Search(param, OperatorID, startTime, endTime, out itemCount);
}
/// <summary>
/// 获取缴费项分类json
/// </summary>
/// <param name="param"></param>
/// <param name="chargeItemType"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> SearchChargeItemCategory(EasyUIGridParamModel param, ChargeItemCategory chargeItemType, out int itemCount)
{
return dal.SearchChargeItemCategory(param, chargeItemType, out itemCount);
}
/// <summary>
/// 获取年终总结一览数据
/// </summary>
/// <param name="param"></param>
/// <param name="yeArrear"></param>
/// <returns></returns>
public ActionResult YearBalanceSearch(EasyUIGridParamModel param, YearEndArrear yeArrear)
{
int itemCount = 0;
var YeArrearList = new YearEndArrearRule().Search(param, yeArrear, out itemCount);
var showList = from ya in YeArrearList
select new
{
ID = ya.ID,
Name = ya.NAME,
Money = ya.MONEY,
Status = GetYearEndArrearStatus(ya.STATUS),
Year = ya.YEAR,
ChargeDate = ya.CHARGEDATE
};
return Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 获取收费协议json
/// </summary>
/// <param name="param"></param>
/// <param name="agree"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> SearchContrast(EasyUIGridParamModel param, Agreements agree, Customer c, out int itemCount)
{
List<SqlParameter> paramList = new List<SqlParameter>();
StringBuilder StrSql = new StringBuilder();
StrSql.Append(@"select a.ID,c.ID as customerID,c.Code,c.Name,a.Money,a.Status,
a.begindate,a.enddate,a.createtime,a.checktime,a.Code as agreementCode
from T_Agreements a
left join T_Customer c on c.ID=a.CustomerID
where 1=1 ");
Dictionary<string, object> paramDic = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(agree.ID))
{
StrSql.Append("and a.ID=@ID ");
paramDic.Add("ID", agree.ID);
}
if (agree.BeginDate.ToString() != "0001/1/1 0:00:00" && agree.EndDate.ToString() != "0001/1/1 0:00:00")
{
StrSql.Append("and a.begindate>=@beginDate and a.enddate<=@endDate ");
paramDic.Add("beginDate", agree.BeginDate);
paramDic.Add("endDate", agree.EndDate);
}
if (agree.Status != -1)
{
StrSql.Append("and a.status=@status ");
paramDic.Add("status", agree.Status);
}
if (!string.IsNullOrEmpty(agree.CustomerID))
{
StrSql.Append("and c.ID=@customerID ");
paramDic.Add("customerID", agree.CustomerID);
}
if (!string.IsNullOrEmpty(c.Name))
{
StrSql.Append("and c.Name like @name ");
paramDic.Add("name", string.Format("%{0}%", c.Name));
}
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, StrSql), paramDic);
return db.GetDynaminObjectList(StrSql.ToString(), pageIndex, pageSize, null, paramDic);
}
}
/// <summary>
/// 获取临时缴费信息json
/// </summary>
/// <param name="pageModel"></param>
/// <param name="tCharge"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel pageModel, Model.TempCharge tCharge, out int itemCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT tCharge.ID,customerName,chargeName,Money,createTime,oper.Name ");
strSql.Append("FROM T_TempCharge tCharge ");
strSql.Append("left join T_Operator oper on oper.ID=tCharge.OperatorID ");
strSql.Append("where 1=1 ");
Dictionary<string, object> param = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(tCharge.CustomerName))
{
strSql.Append("and customerName like @customerName ");
param.Add("customerName", string.Format("%{0}%", tCharge.CustomerName));
}
DateTime firstTime = new DateTime();
if (tCharge.CreateTime > firstTime)
{
strSql.Append("and convert(varchar(10),createTime,121)=@createTime ");
param.Add("createTime", Convert.ToDateTime(tCharge.CreateTime).ToString("yyyy-MM-dd"));
}
if (tCharge.Status != -1)
{
strSql.Append("and tCharge.Status=@status ");
param.Add("status", tCharge.Status);
}
int pageIndex = Convert.ToInt32(pageModel.page) - 1;
int pageSize = Convert.ToInt32(pageModel.rows);
using (DBHelper db = DBHelper.Create())
{
string sql = strSql.ToString();
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, sql), param);
return db.GetDynaminObjectList(sql, pageIndex, pageSize, "ID", param);
}
}
/// <summary>
/// 日志查询json
/// <param name="param"></param>
/// <param name="log"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// </summary>
public List<dynamic> Search(EasyUIGridParamModel param, string OperatorID, DateTime startTime, DateTime endTime, out int itemCount)
{
List<LoginLog> logList = new List<LoginLog>();
Dictionary<string, object> paramDic = new Dictionary<string, object>();
using (DBHelper db = DBHelper.Create())
{
string strSql = @" SELECT l.ID,o.Name OPeratorID,CreateTime,case Type when 0 then '退出登录' else '登录' end as type,te.name as oprname
FROM T_LoginLog l
left join T_Operator o on o.ID=l.OperatorID
inner join t_employee te on o.employeeid = te.id
WHERE 1=1";
StringBuilder strSqlCount = new StringBuilder();
strSqlCount.Append(@"select count(0) from t_loginlog tl where 1=1 ");
if (!string.IsNullOrEmpty(OperatorID))
{
strSql += " and l.OperatorID=@OperatorID";
strSqlCount.Append(" and tl.OperatorID=@OperatorID");
paramDic.Add("OperatorID", OperatorID);
}
if (startTime != DateTime.MinValue)
{
strSql += " and l.CreateTime > @CreateTime ";
strSqlCount.Append(" and tl.CreateTime > @CreateTime ");
paramDic.Add("CreateTime", startTime);
}
if (endTime != DateTime.MinValue)
{
endTime = endTime.AddDays(1);
strSql += " and l.CreateTime < @endTime";
strSqlCount.Append(" and tl.CreateTime < @endTime");
paramDic.Add("endTime", endTime);
}
itemCount = db.GetCount(strSqlCount.ToString(), paramDic);
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
return db.GetDynaminObjectList(strSql, pageIndex, pageSize, "ID", paramDic); ;
}
}
/// <summary>
/// 获取收费项目数据json
/// </summary>
/// <param name="param"></param>
/// <param name="chargeItem"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> SearchChargeItem(EasyUIGridParamModel param, string name, out int itemCount)
{
List<SqlParameter> paramList = new List<SqlParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT tci.ID,code,tci.Name, case ISREGULAR when 0 then '否' else '是' end as ISREGULAR,");
strSql.Append("case IsPloy when 0 then '否' else '是' end as IsPloy,");
strSql.Append("tci.UnitPrice,('每'+tu.Name+'/每'+tu2.Name) AS UNIT,");
strSql.Append("tcic.Name AS CATEGORYNAME,tci.UnitID1,tci.UnitID2 ");
strSql.Append("FROM T_ChargeItem tci ");
strSql.Append("LEFT JOIN T_Unit tu ON tu.id= tci.UnitID1 ");
strSql.Append("LEFT JOIN T_Unit tu2 ON tci.UnitID2= tu2.ID ");
strSql.Append("LEFT JOIN T_ChargeItemCategory tcic ON tcic.ID = tci.CategoryID ");
strSql.Append("where 1=1 ");
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
string countSql = @"select count(0) from T_ChargeItem tci where 1=1 {0}";
using (DBHelper db = DBHelper.Create())
{
Dictionary<string, object> dic = new Dictionary<string, object>();
StringBuilder whereStr = new StringBuilder();
whereStr.Append(" and tci.Name like @Name");
dic.Add("Name", string.Format("%{0}%", name));
itemCount = db.GetCount(string.Format(countSql, whereStr.ToString()), dic);
return db.GetDynaminObjectList(strSql.ToString(), pageIndex, pageSize, "ID", dic);
}
}
/// <summary>
/// 获取职员管理列表json
/// </summary>
/// <param name="param"></param>
/// <param name="emp"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<object> GetSearchJson(EasyUIGridParamModel param, Employee emp, out int itemCount)
{
return new DAL.EmployeeDAL().GetSearchJson(param, emp, out itemCount);
}
/// <summary>
/// 获取收费项目数据json
/// </summary>
/// <param name="param"></param>
/// <param name="chargeItem"></param>
/// <returns></returns>
public List<dynamic> SearchChargeItem(EasyUIGridParamModel param, string name, out int itemCount)
{
return dal.SearchChargeItem(param, name, out itemCount);
}
/// <summary>
/// 获取json数据
/// </summary>
/// <param name="gridParam"></param>
/// <param name="status"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<object> GetSearchJson(EasyUIGridParamModel gridParam, int status, string userID, out int itemCount)
{
return dal.GetSearchJson(gridParam, status, userID, out itemCount);
}
/// <summary>
/// 获取期初欠费一览数据
/// </summary>
/// <param name="param"></param>
/// <param name="customerID"></param>
/// <returns></returns>
public ActionResult NewArrearSearch(EasyUIGridParamModel param, FirstMoney fMoney)
{
int itemCount = 0;
List<dynamic> newArrearList = new FirstMoneyRule().Search(param, fMoney, out itemCount);
var showList = from newArrear in newArrearList
select new
{
ID = newArrear.ID,
Name = newArrear.NAME,
Year = newArrear.YEAR,
Money = newArrear.MONEY,
Status = GetFirstMoneyStatus(newArrear.STATUS),
ChargeDate = newArrear.CHARGEDATE
};
return Json(new { total = itemCount, rows = showList, footer = new List<dynamic>() { new { Money = showList.Sum(t => Convert.ToInt32(Convert.ToDecimal(t.Money)).ToString("0.00")), Name = "合计" } } }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// ��ɫ��ѯjson
/// </summary>
/// <param name="param"></param>
/// <param name="g"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> SearchGroup(EasyUIGridParamModel param, Group g, out int itemCount)
{
StringBuilder strSql = new StringBuilder("select ID,Name from T_group g ");
strSql.Append("where 1=1 ");
Dictionary<string, object> paramLists = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(g.NAME))
{
strSql.Append("and Name like @Name ");
paramLists.Add("Name", string.Format("%{0}%", g.NAME));
}
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
string sql = strSql.ToString();
itemCount = db.GetCount(sql, paramLists);
return db.GetDynaminObjectList(sql, pageIndex, pageSize, "ID", paramLists);
}
}
public JsonResult Search(EasyUIGridParamModel gridParam, int status)
{
int itemCount = 0;
MessageRule msgR = new MessageRule();
List<dynamic> list = msgR.GetSearchJson(gridParam, status, MyTicket.CurrentTicket.UserID, out itemCount);
var listResult = from m in list
select new
{
ID = m.ID,
TITLE = "<a >" + m.TITLE + "</a>",
CREATEDATE = m.CREATEDATE,
STATUS = m.STATUS,
VIEW = "<b class='btnView'>查看</b>"
};
return Json(new { total = itemCount, rows = listResult }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 系统用户管理json
/// </summary>
/// <param name="param"></param>
/// <param name="oper"></param>
/// <param name="emp"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> SearchOperator(EasyUIGridParamModel param, Operator oper, Employee emp, out int itemCount)
{
List<SqlParameter> paramList = new List<SqlParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT oper.ID, oper.EmployeeID AS empid,oper.status ,");
strSql.Append("case oper.[Status] WHEN 1 THEN '启用' WHEN 0 THEN '<font color=''Gray''>已禁用</font>' else '<font color=''red''>已删除</font>' end AS STATUSNAME, ");
strSql.Append("oper.Name, case oper.IsAdmin WHEN 1 THEN '是' ELSE '否' end as isAdmin,oper.createdate,oper.groupid,");
strSql.Append("emp.name AS empname,G.NAME AS GROUPNAME ");
strSql.Append("FROM T_Operator oper ");
strSql.Append("LEFT JOIN T_Employee emp ON emp.ID = oper.EmployeeID ");
strSql.Append("LEFT JOIN T_GROUP G ON G.ID=OPER.GROUPID ");
strSql.Append("where 1=1 and oper.status <3");
Dictionary<string, object> paramss = new Dictionary<string, object>();
if (!string.IsNullOrEmpty(oper.Name))
{
strSql.Append("and (oper.name like @name or emp.name like @name) ");
paramss.Add("name", string.Format("%{0}%", oper.Name));
}
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
itemCount = db.GetCount(strSql.ToString(), paramss);
return db.GetDynaminObjectList(strSql.ToString(), pageIndex, pageSize, "Status", paramss);
}
}
public ActionResult SearchAgree(EasyUIGridParamModel param, Agreements agree, Ajax.Model.Customer c)
{
int itemCount = 0;
List<dynamic> agreeList = new AgreementsRule().SearchAgree(param, agree, c, out itemCount);
var showList = from a in agreeList
select new
{
ID = a.ID,
CONTRASTID = a.ID,
CUSTOMERID = a.CUSTOMERID,
CODE = a.CODE,
NAME = a.NAME,
MONEY = a.MONEY,
BEGINDATE = a.BEGINDATE,
AGREEMENTCODE = a.AGREEMENTCODE,
ENDDATE = TimeParser.FormatDateTime(a.ENDDATE),
STATUS = Convert.ToString(a.STATUS).Replace("0", "未审核").Replace("1", "已审核").Replace("2", "已删除")
};
return Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet);
}
//(JQueryDataTableParamModel param, string orderType, string sortingCols)
/// <summary>
/// 获取所有的客户类型
/// </summary>
/// <param name="param"></param>
/// <param name="orderType"></param>
/// <param name="sortingCols"></param>
/// <returns></returns>
public List<dynamic> GetAllCustomerType(EasyUIGridParamModel param, CustomerType cType, out int itemCount)
{
return dal.GetAllCustomerType(param, cType, out itemCount);
}
public ActionResult Search(EasyUIGridParamModel param, Employee emp)
{
int ItemCount = 0;
List<dynamic> list = new EmployeeRule().GetSearchJson(param, emp, out ItemCount);
var listResult = from m in list.ToList()
select new
{
ID = m.ID,
NAME = m.NAME,
SEX = m.SEX,
BIRTHDATE = m.BIRTHDATE,
CARDTYPE = m.CARDTYPE,
CARDID = m.CARDID,
ADDRESS = m.ADDRESS,
OfficePhone = m.OFFICEPHONE,
DEPTNAME = m.DEPTNAME,
CURRENTSTATUS = m.STATUS.Replace("1", "在职").Replace("3", "停职")
};
return Json(new { total = ItemCount, rows = listResult.ToList() }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 获取缴费信息表json
/// </summary>
/// <param name="customerID">客户ID</param>
/// <param name="param">EasyUIGridParamModel query parameter</param>
/// <param name="itemCount">count</param>
/// <returns></returns>
public List <dynamic> ChargeSearch(string customerID, Ajax.Common.EasyUIGridParamModel param, out int itemCount)
{
return(dal.ChargeSearch(customerID, param, out itemCount));
}
/// <summary>
/// 获取付费单位列表json
/// </summary>
/// <param name="param"></param>
/// <param name="u"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<object> GetSearchJson(EasyUIGridParamModel param, Unit u, out int itemCount)
{
return dal.GetSerachJson(param, u, out itemCount);
}
/// <summary>
/// 票据一览查询
/// </summary>
/// <param name="param"></param>
/// <param name="strWhere"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel param, string strWhere, out int itemCount)
{
return dal.Search(param, strWhere, out itemCount);
}
/// <summary>
/// 获取临时缴费信息json
/// </summary>
/// <param name="pageModel"></param>
/// <param name="tCharge"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel pageModel, Model.TempCharge tCharge, out int itemCount)
{
return new DAL.TempChargeDAL().Search(pageModel, tCharge, out itemCount);
}
/// <summary>
/// 获取年度欠费一览
/// </summary>
/// <param name="param"></param>
/// <param name="yeArrear"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List<dynamic> Search(EasyUIGridParamModel param, YearEndArrear yeArrear, out int itemCount)
{
Dictionary<string, object> DicParam = new Dictionary<string, object>();
string strSql = @"select yea.*,c.Name from T_YearEndArrear yea
left join T_Customer c on c.ID=yea.CustomerID
where 1=1 ";
if (!string.IsNullOrEmpty(yeArrear.CustomerID))
{
strSql += " and yea.CustomerID=@customerID";
DicParam.Add("customerID", yeArrear.CustomerID);
}
if (yeArrear.Status != -1)
{
strSql += " and yea.Year=@Year";
DicParam.Add("Year", yeArrear.Year);
}
//分页信息
int pageIndex = Convert.ToInt32(param.page) - 1;
int pageSize = Convert.ToInt32(param.rows);
using (DBHelper db = DBHelper.Create())
{
itemCount = db.GetCount(string.Format(DBHelper.StrGetCountSql, strSql), DicParam);
return db.GetDynaminObjectList(strSql, pageIndex, pageSize, "ID", DicParam);
}
}
