/// <summary>
/// search一览
/// </summary>
/// <param name="param"></param>
/// <param name="IType"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List <object> Search(EasyUIGridParamModel param, InvoiceType IType, out int itemCount)
{
List <object> list = new List <object>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from T_InvoiceType where 1=1");
Dictionary <string, object> paramList = new Dictionary <string, object>();
if (IType != null)
{
if (!string.IsNullOrEmpty(IType.ID))
{
strSql.Append(" and ID=@ID ");
paramList.Add("ID", IType.ID);
}
if (!string.IsNullOrEmpty(IType.Name))
{
strSql.Append(" and Standard=@Standard ");
paramList.Add("Standard", IType.Standard);
}
}
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.ToString()), paramList);
list = db.GetDynaminObjectList(strSql.ToString(), pageIndex, pageSize, "ID", paramList);
}
return(list);
}
/// <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));
}
}
/// <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));
}
}
/// <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>
/// 获取收费项目数据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="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="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="emp"></param>
/// <param name="itemCount"></param>
/// <returns></returns>
public List <object> GetSearchJson(EasyUIGridParamModel param, Employee emp, out int itemCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select te.ID,te.Name,te.PY,BirthDate,Sex,DeptID,Job,'' as CardType,te.ICCardID as CardID,Address,te.OfficePhone,td.Name as DEPTNAME,WorkTime,FireTime,te.Status
FROM T_Employee te
LEFT JOIN T_Dept td ON td.ID = te.DeptID
where 1=1 and te.Status!=2 " );
StringBuilder strSqlCount = new StringBuilder();
strSqlCount.Append(@"select count(0) from t_employee te where 1=1 ");
Dictionary <string, object> paramm = new Dictionary <string, object>();
if (!string.IsNullOrEmpty(emp.Name))
{
strSql.Append(" and te.Name like @Name ");
strSqlCount.Append(" and te.Name like @Name ");
paramm.Add("Name", "%" + emp.Name + "%");
strSql.Append(" and te.PY like @PY ");
strSqlCount.Append(" and te.Name like @Name ");
paramm.Add("PY", "%" + Pinyin.GetPinyin(emp.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(strSqlCount.ToString(), paramm);
return(db.GetDynaminObjectList(sql, pageIndex, pageSize, "Name", paramm));
}
}
/// <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="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 SearchGroup(EasyUIGridParamModel param, Group group)
{
int itemCount = 0;
List <dynamic> groupList = new GroupRule().SearchGroup(param, group, out itemCount);
var showList = from groups in groupList
select new
{
ID = groups.ID,
NAME = groups.NAME
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
public ActionResult SearchChargeItemCategory(EasyUIGridParamModel param, ChargeItemCategory chargeItemType)
{
int itemCount = 0;
List <dynamic> CategoryList = new ChargeItemCategoryRule().SearchChargeItemCategory(param, chargeItemType, out itemCount);
var ShowList = from category in CategoryList
select new
{
ID = category.ID,
NAME = category.NAME
};
return(Json(new { total = itemCount, rows = ShowList }, JsonRequestBehavior.AllowGet));
}
public ActionResult GetAllCustomerType(EasyUIGridParamModel param, CustomerType cType)
{
int itemCount = 0;
List <dynamic> customerTypeList = new CustomerTypeRule().GetAllCustomerType(param, cType, out itemCount);
var showList = from c in customerTypeList
select new
{
ID = Convert.ToString(c.ID),
NAME = c.NAME
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
public ActionResult UnitSearch(EasyUIGridParamModel param, Unit unit)
{
int itemCount = 0;
List <dynamic> uList = new UnitRule().GetSearchJson(param, unit, out itemCount);
var listResult = from u in uList.ToList()
select new
{
ID = u.ID,
Name = u.NAME,
Level = u.LEVEL,
TimeValue = u.TIMEVALUE
};
return(Json(new { total = itemCount, rows = listResult.ToList() }, 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>
/// 票据分类一览数据
/// </summary>
/// <returns></returns>
public ActionResult TypeSearch(EasyUIGridParamModel param)
{
int itemCount = 0;
List <dynamic> inTypeList = new InvoiceTypeRule().Search(param, new InvoiceType(), out itemCount);
var showList = from inType in inTypeList
select new
{
ID = inType.ID,
Name = inType.NAME,
Standard = inType.STANDARD,
Code = inType.CODE,
StepValue = inType.STEPVALUE
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取消息的json数据
/// </summary>
/// <param name="gridParam">分页信息</param>
/// <param name="msg">记录查询条件的实体</param>
/// <param name="itemCount">查询的总行书</param>
/// <returns></returns>
public List <object> GetSearchJson(EasyUIGridParamModel gridParam, int status, string userID, out int itemCount)
{
List <string> paramNames = new List <string>();
List <string> paramValus = new List <string>();
List <SqlParameter> paramList = new List <SqlParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT m.ID, Title, CreateDate, (
CASE
WHEN (om.status = 0) THEN '未读'
WHEN (om.status = 1) THEN '已读'
END
) STATUS
FROM T_Message m
LEFT JOIN t_operatorMsg om
ON m.ID = om.MsgID
WHERE 1 = 1 " );
StringBuilder countSql = new StringBuilder(@"SELECT count(0)
FROM T_Message m
LEFT JOIN t_operatorMsg om
ON m.ID = om.MsgID
WHERE 1 = 1 " );
Dictionary <string, object> param = new Dictionary <string, object>();
//前台查询条件
if (status != -1)
{
strSql.Append(" and om.status=@status ");
countSql.Append(" and om.status=@status ");
param.Add("status", status);
}
if (!string.IsNullOrEmpty(userID))
{
strSql.Append(" and om.OperatorID=@OperatorID ");
countSql.Append(" and om.OperatorID=@OperatorID ");
param.Add("OperatorID", userID);
}
int pageIndex = Convert.ToInt32(gridParam.page) - 1;
int pageSize = Convert.ToInt32(gridParam.rows);
using (DBHelper db = DBHelper.Create())
{
string sql = strSql.ToString();
itemCount = db.GetCount(countSql.ToString(), param);
return(db.GetDynaminObjectList(sql, pageIndex, pageSize, "ID", param));
}
}
/// <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));
}
public ActionResult CustomerChargeConfirmSearch(EasyUIGridParamModel param)
{
int count = 0;
List <dynamic> chargeList = new ChargeRule().ChargeSearch("", param, out count);
var showList = from charge in chargeList
select new
{
ID = charge.ID,
CustomerName = charge.CUSTOMERNAME,
Money = charge.MONEY,
CreateTime = charge.CREATEDATE,
OperName = charge.OPERATORNAME,
Detail = "查看详情"
};
return(Json(new { total = count, rows = showList }, JsonRequestBehavior.AllowGet));
}
public ActionResult Search(EasyUIGridParamModel param, TempCharge tCharge, TempChargeDetail tChargeDetail)
{
int itemCount = 0;
List <dynamic> chargeList = new Ajax.BLL.TempChargeRule().Search(param, tCharge, out itemCount);
var showList = from tempCharge in chargeList
select new
{
ID = tempCharge.ID,
CUSTOMERNAME = tempCharge.CUSTOMERNAME,
CHARGENAME = tempCharge.CHARGENAME,
MONEY = tempCharge.MONEY,
CREATETIME = tempCharge.CREATETIME,
NAME = tempCharge.NAME
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
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));
}
public ActionResult SearchLoginLog(EasyUIGridParamModel param, string OperatorID, string startDate, string endDate)
{
int itemCount = 0;
DateTime sDate = TimeParser.ConvertStringToDateTime(startDate);
DateTime eDate = TimeParser.ConvertStringToDateTime(endDate);
List <dynamic> logList = new LoginLogRule().Search(param, OperatorID, sDate, eDate, out itemCount);
var showList = from loginLog in logList
select new
{
ID = loginLog.ID,
OperatorName = loginLog.OPERATORID,
Type = loginLog.TYPE.Replace("1", "登录").Replace("0", "退出"),
CreateTime = loginLog.CREATETIME,
oprname = loginLog.OPRNAME
};
return(Json(new { total = itemCount, 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);
}
}
public ActionResult SearchOperator(EasyUIGridParamModel param, Operator oper, Employee emp)
{
int itemCount = 0;
List <dynamic> operList = new OperatorRule().SearchOperator(param, oper, emp, out itemCount);
var showList = from opers in operList
select new
{
ID = opers.ID,
EMPID = opers.EMPID,
STATUS = opers.STATUSNAME,
NAME = opers.NAME,
EMPNAME = opers.EMPNAME,
ISADMIN = opers.ISADMIN,
CREATEDATE = opers.CREATEDATE,
GROUPNAME = opers.GROUPNAME,
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
/// <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));
}
}
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>
/// 票据管理一览json
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public ActionResult InvoiceSearch(EasyUIGridParamModel param, string InvoiceType)
{
int itemCount = 0;
string strWhere = string.IsNullOrEmpty(InvoiceType) ? "" : string.Format(" and InvoiceType='{0}' ", InvoiceType);
List <dynamic> IrList = new InvoiceRegisterRule().Search(param, strWhere, out itemCount);
var showList = from Ir in IrList
select new
{
ID = Ir.ID,
BeginCode = Ir.BEGINCODE,
EndCode = Ir.ENDCODE,
OperatorName = Ir.NAME,
CurrentCode = Ir.CURRENTCODE,
UseStatus = Ir.USESTATUS == "0" ? "已登记未使用" : "正在使用",
InvoiceTypeName = Ir.INVOICETYPENAME,
InvoiceType = Ir.INVOICETYPE
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
public ActionResult Search(EasyUIGridParamModel param, Ajax.Model.Customer customer)
{
int itemCount = 0;
CustomerRule cr = new CustomerRule();
List <dynamic> list = cr.Search(param, customer, out itemCount);
var showList = from customers in list
select new
{
ID = customers.ID,
Name = customers.NAME,
TypeName = customers.CUSTOMERTYPE,
Status = customers.STATUS.Replace("1", "正常").Replace("0", "未审核").Replace("2", "禁用").Replace("3", "已删除"),
CreateTime = Ajax.Common.TimeParser.FormatDateTime(customers.CREATETIME),
Contactor = customers.CONTACTOR,
FeeType = string.IsNullOrEmpty(customers.AGREEID) ? "非协议缴费" : "协议缴费",
Address = customers.ADDRESS
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
/// <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));
}
public ActionResult SearchChargeItem(EasyUIGridParamModel param, string name)
{
int itemCount = 0;
List <dynamic> ChargeItemList = new ChargeItemRule().SearchChargeItem(param, name, out itemCount);
var showList = from charge in ChargeItemList
select new
{
ID = charge.ID,
Code = charge.CODE,
Name = charge.NAME,
IsRegular = Convert.ToString(charge.ISREGULAR).Replace("False", "否").Replace("True", "是"),
IsPloy = Convert.ToString(charge.ISPLOY).Replace("False", "否").Replace("True", "是"),
UnitPrice = string.Format("{0} ", charge.UNITPRICE),
Unit = string.Format(" 元 [{0}]", charge.UNIT),
CategoryName = charge.CATEGORYNAME,
UnitID1 = charge.UNITID1,
UnitID2 = charge.UNITID2
};
return(Json(new { total = itemCount, rows = showList }, JsonRequestBehavior.AllowGet));
}
