/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strSelect, string strWhere, string filedOrder, out int recordCount)
{
if (strSelect != "")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select " + strSelect + " FROM td_property ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
else
{
return(GetList(pageSize, pageIndex, strWhere, filedOrder, out recordCount));
}
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT A.*, B.Name AS GoodsName, B.CategoryName AS GoodsCategory, B.Variety AS GoodsVariety, ");
strSql.Append("C.ShortName AS Shipper, D.ShortName AS Receiver ");
strSql.Append("FROM mtms_Order A ");
strSql.Append("LEFT JOIN mtms_Goods B ON A.GoodsId = B.Id ");
strSql.Append("LEFT JOIN mtms_Customer C ON A.ShipperId = C.Id ");
strSql.Append("LEFT JOIN mtms_Customer D ON A.ReceiverId = D.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
//throw new Exception(PagingHelper.CreatePagingSql(1, pageSize, pageIndex, strSql.ToString(), filedOrder));
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT id,year,month,empid,name,cast(znjy as float) znjy,cast(jxjy as float) jxjy,cast(dbyl as float) dbyl,");
strSql.Append(" cast(zfdk as float) zfdk, cast(zfzj as float) zfzj,cast(sylr as float) sylr, ");
strSql.Append(" cast((znjy+jxjy+dbyl+zfdk+zfzj+sylr) as float) as total ");
strSql.Append(" FROM t_zxkc ");
if (strWhere != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" group by id,year,month,empid,name,znjy,jxjy,dbyl,zfdk,zfzj,sylr ");
recordCount = Convert.ToInt32(DbHelperSql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 【微帐号】获得查询分页数据
/// </summary>
public DataSet GetWCodeList(int wid, int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM wx_product");
strSql.Append(" where wid=" + wid);
if (category_id > 0)
{
strSql.Append(" and typeId=" + category_id);
}
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="strWhere"></param>
/// <param name="filedOrder"></param>
/// <param name="recordCount"></param>
/// <param name="type">默认false,当为true时为订单上的未收款订单和多付款订单</param>
/// <param name="type">当查询我的策划订单和我的设计订单时，orderType、currentUser这两个参数才有值，用于实现“我的策划订单”“我的设计订单”这两个页签按人员的添加时间排序</param>
/// <returns></returns>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount, bool type = false, string where = "", bool isPage = true, string orderType = "", string currentUser = "")
{
string addTable = "", addfield = "";
if (!string.IsNullOrEmpty(orderType))
{
addfield = ",op2.op_addTime as op_addTime1";
addTable = " left join ms_orderperson op2 on o_id=op2.op_oid and op2.op_type=" + orderType + " and op2.op_number='" + currentUser + "' ";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select *,unMoney=finMoney - rpdMoney,profit=finMoney-finMoney1-isnull(o_financeCust,0) from (select o.*,c.*,co.*,op1.* " + addfield + ",person2 = (SELECT op_name FROM MS_OrderPerson WHERE op_oid=o_id and op_type=2),person3 = isnull(STUFF((SELECT ',' + op_name+'('+(case when op_dstatus=0 then '待定' else case when op_dstatus=1 then '处理中' else case when op_dstatus=2 then '已完成' else '待定与处理中' end end end)+')' FROM MS_OrderPerson WHERE op_oid=o_id and op_type=3 FOR XML PATH('')), 1, 1, ''),'无')");
strSql.Append(" , person4 = isnull(STUFF((SELECT ',' + op_name + '(' + (case when op_dstatus = 0 then '待定' else case when op_dstatus = 1 then '处理中' else case when op_dstatus=2 then '已完成' else '待定与处理中' end end end) + ')' FROM MS_OrderPerson WHERE op_oid = o_id and op_type = 5 FOR XML PATH('')), 1, 1, ''),'无') ,finMoney=isnull((select sum(isnull(fin_money,0)) fin_money from MS_finance where fin_type=1 and fin_oid=o_id),0),finMoney1=isnull((select sum(isnull(fin_money,0)) fin_money from MS_finance where fin_type=0 and fin_oid=o_id),0),rpdMoney = isnull((select sum(isnull(rpd_money,0)) rpd_money from MS_ReceiptPayDetail left join MS_ReceiptPay on rp_id=rpd_rpid where rpd_type=1 and rp_isConfirm=1 and rpd_oid=o_id),0) FROM MS_Order o left join ms_customer c on o_cid=c_id left join ms_contacts co on o_coid=co_id left join ms_orderperson op1 on o_id=op_oid and op_type=1 " + addTable + ") t");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
string sql = string.Empty;
if (type)
{
sql = "select * from (" + strSql.ToString() + ") v left join (";
sql += " select isnull(fin_oid,rpd_oid) fin_oid,isnull(fin_type,rpd_type) fin_type,isnull(finMoney,0) finMoney,isnull(rpdMoney,0) rpdMoney from ";
sql += " (select fin_oid, fin_type, sum(isnull(fin_money, 0)) finMoney from MS_finance where fin_flag <> 1 group by fin_oid, fin_type) t1";
sql += " full join(select rpd_oid, rpd_type, sum(isnull(rpd_money, 0)) rpdMoney from MS_ReceiptPayDetail left join MS_ReceiptPay on rp_id = rpd_rpid where rp_isConfirm = 1 group by rpd_oid, rpd_type) t3 on t1.fin_oid = t3.rpd_oid and t1.fin_type = t3.rpd_type";
sql += " left join MS_Order on isnull(fin_oid, rpd_oid) = o_id where 1 = 1";
sql += " ) v3 on v.o_id = v3.fin_oid where isnull(fin_oid,'')<>'' " + where;
}
else
{
sql = strSql.ToString();
}
recordCount = 0;
if (isPage)
{
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(sql)));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, sql, filedOrder)));
}
else
{
return(DbHelperSQL.Query(sql.ToString()));
}
}
public string Manager_List()
{
int pageSize = Utils.StrToInt(Request.Query["page_size"]);
int pageIndex = Utils.StrToInt(Request.Query["page_index"], 0) + 1;
int dept_id = Utils.StrToInt(Request.Query["dept_id"]); //部门id
int Rid = Utils.StrToInt(Request.Query["rid"]); //角色id
string keyword = Request.Query["keyword"]; //搜索关键字
string strWhere = Request.Query["sw"].ToString() ?? string.Empty;
strWhere += " and m.id>1 ";
if (Rid > 0)
{
strWhere += " and mr.id='" + Rid + "'";
}
if (dept_id > 1)
{
strWhere += " and m.dept_id='" + dept_id + "'";
}
if (!string.IsNullOrEmpty(keyword))
{
strWhere += " and (user_name like '%" + keyword + "%' or real_name like '%" + keyword + "%' or email like '%" + keyword + "%')";
}
string filedOrder = " m.id desc";
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT m.*,mr.role_name FROM Nc_Manager m
LEFT JOIN nc_manager_role mr ON m.role_id=mr.id
");
if (strWhere != "")
{
strSql.Append(" where 1=1 " + strWhere);
}
int recordCount = Convert.ToInt32(Dbl.ZZCMS.CreateSqlScalar(PagingHelper.CreateCountingSql(strSql.ToString())));
DataTable dt = Dbl.ZZCMS.CreateSqlDataTable(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
var result = JsonHelper.DataTableToJSON(dt);
return(result);
}
/// <summary>
/// TopicId 这个参数不为 0 时 where 中会多出 id in (SELECT [Id] FROM dt_Forum_PostId] where TopicId=" + TopicId + ")
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, int TopicId, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + "Forum_Post_" + postSubTableId.ToString() + " ");
if (strWhere.Trim() == "")
{
strWhere = " 1=1 ";
}
strSql.Append(" where " + strWhere);
if (TopicId != 0)
{
strSql.Append(" and id in (SELECT [Id] FROM " + databaseprefix + "Forum_PostId where TopicId=" + TopicId + ") ");
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 客户榜单
/// 获得查询分页数据
/// </summary>
/// <param name="pageSize">每页数</param>
/// <param name="pageIndex">页码</param>
/// <param name="strWhere">条件</param>
/// <param name="recordCount">返回数据总条数</param>
/// <param name="timeWhere">时间条件</param>
/// <returns></returns>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filterWhere, out int recordCount, string timeWhere)
{
string strSql = "select (select sum(totalMoney) from sf_goods_order as b where b.userNum = a.userNum and ispay=2 " + timeWhere + ") as moneys,[id],busnieseNum,appNum,templatesNum,userNum,nickName,province,city,addTime,openID,photoUri,isAttention,sellModel,inviteKey,fatherKey from sf_user_info as a";
if (strWhere.Trim() != "")
{
strSql += " where " + strWhere;
}
string temp_SQL = strSql;
if (filterWhere.Length > 0)
{
strSql = "select * from (" + strSql + ") t Where " + filterWhere;
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
strSql = temp_SQL;
return(DbHelperSQL.Query(CreatePagingSql(recordCount, pageSize, pageIndex, strSql, "(select sum(totalMoney) from sf_goods_order as b where b.userNum = a.userNum and ispay=2 " + timeWhere + ") desc,addtime desc", filterWhere)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount, bool isPage = true)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM MS_Customer c left join MS_Contacts co on c.c_id=co.co_cid and co_flag=1 left join dt_manager on c_owner=user_name");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = 0;
if (isPage)
{
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
else
{
return(DbHelperSQL.Query(strSql.ToString() + " order by " + filedOrder));
}
}
/// <summary>
/// 根据频道名称及规格查询分页数据
/// </summary>
public DataSet ArticleList(string channel_name, int category_id, Dictionary <string, string> dicSpecIds, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
string specWhere = string.Empty;
foreach (KeyValuePair <string, string> kv in dicSpecIds)
{
if (Utils.StrToInt(kv.Value, 0) > 0)
{
if (!string.IsNullOrEmpty(specWhere))
{
specWhere += "and ";
}
specWhere += "B.spec_ids like '%," + kv.Value + ",%'";
}
}
if (!string.IsNullOrEmpty(specWhere))
{
specWhere = " and (" + specWhere + ")";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where datediff(d,add_time,getdate())>=0");
if (category_id > 0)
{
strSql.Append(" and category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
if (!string.IsNullOrEmpty(specWhere))
{
strSql.Append(" and id in(");
strSql.Append("select A.id from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name + " as A," + databaseprefix + "article_goods as B");
strSql.Append(" where A.channel_id=B.channel_id and A.id=B.article_id " + specWhere);
strSql.Append(" group by A.id)");
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 根據視圖獲得查詢分頁資料
/// </summary>
public DataSet DalGetList(string view_name, string view_field, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (view_field.Length > 0)
{
strSql.Append(view_field);
}
else
{
strSql.Append(" * ");
}
strSql.Append(" FROM " + view_name);
if (strWhere.Length > 2)
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(string channel_name, int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
if (category_id > 0)
{
strSql.Append(" where category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
if (category_id > 0)
{
strSql.Append(" and " + strWhere);
}
else
{
strSql.Append(" where " + strWhere);
}
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, int typeId, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.*,(select typeName from wx_albums_type where id=a.typeId) typeName from wx_albums_info a ");
if (typeId != 0)
{
strSql.Append(" where a.typeId=" + typeId);
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
}
else
{
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
public DataSet GetFullList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT A.*, B.Name AS GoodsName, B.CategoryName AS GoodsCategory, B.Variety AS GoodsVariety, ");
strSql.Append("C.ShortName AS Shipper, D.ShortName AS Receiver, ");
strSql.Append("E.Status AS TransportOrderStatus, E.DispatchTime AS DispatchTime, ");
strSql.Append("F.RealName AS DriverName, F.CarNumber AS CarNumber, F.LinkTel AS DriverTel ");
strSql.Append("FROM mtms_Order A ");
strSql.Append("LEFT JOIN mtms_Goods B ON A.GoodsId = B.Id ");
strSql.Append("LEFT JOIN mtms_Customer C ON A.ShipperId = C.Id ");
strSql.Append("LEFT JOIN mtms_Customer D ON A.ReceiverId = D.Id, ");
strSql.Append("mtms_TransportOrder E ");
strSql.Append("LEFT JOIN mtms_Driver F ON E.DriverId = F.Id ");
strSql.Append("WHERE A.TransportOrderId = E.Id ");
if (strWhere.Trim() != "")
{
strSql.Append(strWhere);
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public List <Model.article> GetListPage(string channel_name, int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
if (category_id > 0)
{
strSql.Append(" where category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
if (category_id > 0)
{
strSql.Append(" and " + strWhere);
}
else
{
strSql.Append(" where " + strWhere);
}
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(GetModelList(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int board_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + "forum_posts");
if (board_id > 0)
{
strSql.Append(" where board_id in(select id from " + databaseprefix + "forum_board where class_list like '%," + board_id + ",%')");
}
if (strWhere.Trim() != "")
{
if (board_id > 0)
{
strSql.Append(" and " + strWhere);
}
else
{
strSql.Append(" where " + strWhere);
}
}
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT
a.*,
CASE
b.Nickname
WHEN NULL THEN
b.TrueName ELSE b.Nickname
END AS CustomerName,
CASE
c.TrueName
WHEN NULL THEN
c.Nickname ELSE c.TrueName
END AS BusinessName,
d.StoreName,
CASE
f.TrueName
WHEN NULL THEN
f.Nickname ELSE f.TrueName
END AS AgentName
FROM
t_reward a
LEFT JOIN t_customer b ON a.CustomerId = b.ID
LEFT JOIN t_business c ON a.BusinessId = c.ID
LEFT JOIN t_store d ON a.StoreId = d.ID
LEFT JOIN t_agent_store e ON d.ID = e.StoreId
LEFT JOIN t_agent f ON e.AgentId = f.ID " );
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 分配列表
/// </summary>
public DataSet GetDistributionList(Dictionary <string, string> dict, int pageSize, int pageIndex, out int recordCount)
{
recordCount = 0;
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
StringBuilder strSql3 = new StringBuilder();
string addChkFiled1 = "", addChkFiled2 = "", addChkGroup = "";
string selectFiled = "isnull(t1.fin_oid,t3.rpd_oid) fin_oid,o_content,isnull(finMoney,0) finMoney,isnull(rpdMoney,0) rpdMoney,(isnull(finMoney,0)-isnull(rpdMoney,0)) unMoney,isnull(totalDistribute,0) totalDistribute,isnull(currentDistribute,0) currentDistribute";
string addTable = "";
if (dict != null)
{
strSql2.Append(" and fin_cid=" + dict["cid"] + "");
strSql2.Append(" and fin_type='" + dict["type"] + "'");
strSql3.Append(" and rpd_cid=" + dict["cid"] + "");
strSql3.Append(" and rpd_type='" + dict["type"] + "'");
if (dict.ContainsKey("oID"))
{
strSql1.Append(" and isnull(t1.fin_oid,t3.rpd_oid) in ('" + dict["oID"].Replace("，", ",").Replace(",", "','") + "')");
}
if (dict.ContainsKey("sdate"))
{
strSql1.Append(" and datediff(day,o_sdate,'" + dict["sdate"] + "')<=0");
}
if (dict.ContainsKey("edate"))
{
strSql1.Append(" and datediff(day,o_sdate,'" + dict["edate"] + "')>=0");
}
if (dict.ContainsKey("sdate1"))
{
strSql1.Append(" and datediff(day,o_edate,'" + dict["sdate1"] + "')<=0");
}
if (dict.ContainsKey("edate1"))
{
strSql1.Append(" and datediff(day,o_edate,'" + dict["edate1"] + "')>=0");
}
if (dict.ContainsKey("money"))
{
switch (dict["moneyType"])
{
case "0":
strSql1.Append(" and isnull(finMoney,0) " + dict["sign"] + " " + dict["money"] + "");
break;
case "1":
strSql1.Append(" and isnull(rpdMoney,0) " + dict["sign"] + " " + dict["money"] + "");
break;
case "2":
strSql1.Append(" and isnull(finMoney,0)-isnull(rpdMoney,0) " + dict["sign"] + " " + dict["money"] + "");
break;
default:
break;
}
}
if (dict.ContainsKey("person"))
{
strSql1.Append(" and op_number='" + dict["person"] + "'");
}
if (dict.ContainsKey("chk"))
{
selectFiled = "isnull(t1.fin_oid,t3.rpd_oid) fin_oid,o_content,isnull(finMoney,0) finMoney,isnull(rpdMoney,0) rpdMoney,(isnull(finMoney,0)-isnull(rpdMoney,0)) unMoney,isnull(chkMoney,0) chkMoney,isnull(chktotalDistribute,0) chktotalDistribute,isnull(chkcurrentDistribute,0) chkcurrentDistribute,isnull(totalDistribute,0) totalDistribute,isnull(currentDistribute,0) currentDistribute,isnull(fc_num,'') fc_num,isnull(fcMoney,0) fcMoney";
addTable = "left join (select fin_oid,fc_num,sum(isnull(fc_money,0)) fcMoney from MS_finance left join MS_finance_chk on fin_id = fc_finid where fin_flag<>1 " + strSql2 + " group by fin_oid,fc_num) t2 on t1.fin_oid=t2.fin_oid ";
strSql1.Append(" and fc_num='" + dict["chk"] + "'");
addChkFiled1 = ",sum(chkMoney) chkMoney,sum(chktotalDistribute) chktotalDistribute,sum(chkcurrentDistribute) chkcurrentDistribute";
addChkFiled2 = ",(case when rpd_num='" + dict["chk"] + "' and rp_isConfirm=1 then sum(isnull(rpd_money,0)) else 0 end) chkMoney, (case when rpd_num='" + dict["chk"] + "' then sum(isnull(rpd_money,0)) else 0 end) chktotalDistribute ,(case when rp_id = " + dict["rpID"] + " and rpd_num='" + dict["chk"] + "' then sum(isnull(rpd_money, 0)) else 0 end) chkcurrentDistribute";
addChkGroup = ",rpd_num";
}
if (dict["tag"] == "1")
{
strSql1.Append(" and isnull(currentDistribute,0) <> 0");
}
}
strSql.Append("select " + selectFiled + " from ");
strSql.Append(" (select fin_oid,sum(isnull(fin_money, 0)) finMoney from MS_finance where fin_flag <> 1 " + strSql2 + " group by fin_oid) t1");
strSql.Append(" " + addTable + "");
strSql.Append(" full join (select rpd_oid,sum(rpdMoney) rpdMoney " + addChkFiled1 + ", sum(totalDistribute) totalDistribute, sum(currentDistribute) currentDistribute from(");
strSql.Append(" select rpd_oid,(case when rp_isConfirm = 1 then sum(isnull(rpd_money, 0)) else 0 end) rpdMoney " + addChkFiled2 + ", sum(isnull(rpd_money, 0)) totalDistribute, (case when rp_id = " + dict["rpID"] + " then sum(isnull(rpd_money, 0)) else 0 end) currentDistribute");
strSql.Append(" from MS_ReceiptPayDetail left join MS_ReceiptPay on rp_id = rpd_rpid");
strSql.Append(" where 1=1 " + strSql3 + "");
strSql.Append(" group by rpd_oid " + addChkGroup + ",rp_isConfirm, rp_id) as r group by rpd_oid) t3 on t1.fin_oid = t3.rpd_oid ");
strSql.Append(" left join MS_Order on isnull(t1.fin_oid,t3.rpd_oid) = o_id left join MS_OrderPerson on o_id=op_oid and op_type=1 where 1=1 " + strSql1 + "");
recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), "isnull(t1.fin_oid,t3.rpd_oid) asc")));
}
/// <summary>
/// 获取客户信息列表 2014-08-29 14:58:50 By 唐有炜
/// </summary>
/// <param name="compNum">企业编号</param>
/// <param name="selectFields">选择的字段</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页的数目</param>
/// <param name="strWhere">筛选条件</param>
/// <param name="filedOrder">排序字段</param>
/// <param name="recordCount">记录结果</param>
/// <returns>DataTable</returns>
public DataTable GetCustomerLsit(string compNum, string[] selectFields, int pageIndex, int pageSize,
string strWhere, string filedOrder,
out int recordCount)
{
using (teaCRMDBContext db = new teaCRMDBContext())
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
string select = @"
a.id,
a.cus_no,
a.cus_name,
a.cus_sname,
a.cus_lastid,
(SELECT cus_name FROM T_cus_base WHERE id=a.cus_lastid) AS cus_lastname,
a.cus_tel,
a.cus_city,
a.cus_address,
a.cus_note,
a.con_id,
(
SELECT cc.con_name
FROM T_cus_con cc
WHERE id = a.con_id
) AS con_name,
(SELECT u.user_tname FROM t_sys_user AS u WHERE u.id=a.user_id) as user_name,
(
SELECT STUFF((SELECT ','+u2.user_tname FROM t_sys_user AS u2 WHERE CHARINDEX(CAST(u2.id as VARCHAR),a.con_team) >0 FOR XML PATH ('')),1,1,'')
)
as con_team,
a.con_is_pub,
a.con_back,
a.cus_createTime,
a.cus_fields
";
//选择字段
if (selectFields.Length > 0)
{
select = "";
foreach (var selectField in selectFields)
{
if (!selectField.Contains("exp"))
{
select += " a." + selectField + ",";
}
else //如果是扩展字段，把整个数据读出来
{
select = " a.cus_fields,";
break;
}
}
select = select.TrimEnd(',');
}
strSql.Append(select);
//表处理
string from = "FROM T_cus_base AS a";
strSql.Append(from);
if (strWhere.Trim() != "")
{
strSql.Append(" WHERE " + strWhere);
}
//查询总数Sql
//counttingSql
string counttingSql = PagingHelper.CreateCountingSql(strSql.ToString());
LogHelper.Debug("customer counttingSql," + counttingSql);
//查询分页Sql
//pagingSql
recordCount =
Convert.ToInt32(db.DbHelper.ExecuteScalar(counttingSql));
dynamic namedParameters = null;
string pagingSql = PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(),
filedOrder);
LogHelper.Debug("customer pagingSql," + pagingSql);
try
{
DataTable cusTable =
db.DbHelper.ExecuteDataTable(pagingSql, namedParameters);
for (int i = 0; i < cusTable.Rows.Count; i++)
{
for (int j = 0; j < cusTable.Columns.Count; j++)
{
if (cusTable.Columns[j].ColumnName == "cus_fields")
{
DataColumn fieldColumn = cusTable.Columns[j];
//转换字段
string fieldString = cusTable.Rows[i][j].ToString();
IDictionary <string, JToken> fieldsDictionary =
(JObject)JsonConvert.DeserializeObject(fieldString);
LogHelper.Debug(cusTable.Rows[i][j].ToString());
//循环添加新字段
foreach (var f in fieldsDictionary)
{
DataColumn newColumn = new DataColumn();
newColumn.ColumnName = f.Key;
newColumn.DefaultValue = f.Value;
cusTable.Columns.Add(newColumn);
}
//删除此字段
cusTable.Columns.Remove(fieldColumn);
}
}
}
return(cusTable);
}
catch (Exception ex)
{
recordCount = 0;
LogHelper.Error("序列化出差：", ex);
return(null);
}
}
}
/// <summary>
/// 获取联系人信息列表
/// </summary>
/// <param name="compNum">企业编号</param>
/// <param name="selectFields">选择的字段</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页的数目</param>
/// <param name="strWhere">筛选条件</param>
/// <param name="filedOrder">排序字段</param>
/// <param name="recordCount">记录总数</param>
/// <returns>DataTable</returns>
public DataTable GetContactLsit(string compNum, string[] selectFields, int pageIndex, int pageSize,
string strWhere, string filedOrder, out int recordCount)
{
using (teaCRMDBContext db = new teaCRMDBContext())
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT ");
string select = @"id
,cus_id
,comp_num
,con_name
,con_tel
,con_qq
,con_email
,con_bir
,con_note
,con_is_main
,
(SELECT ISNULL(u.user_tname,u.user_lname) FROM t_sys_user AS u WHERE id=a.[user_id]) AS user_name,
con_fields
";
//选择字段
if (selectFields.Length > 0)
{
select = "";
foreach (var selectField in selectFields)
{
if (!selectField.Contains("exp"))
{
select += " a." + selectField + ",";
}
else
{
select += " a.con_fields,";
}
}
select = select.TrimEnd(',');
}
strSql.Append(select);
//表处理
string from = "FROM T_cus_con AS a";
strSql.Append(from);
if (strWhere.Trim() != "")
{
strSql.Append(" WHERE " + strWhere);
strSql.Append(String.Format(" AND comp_num='{0}'", compNum));
}
else
{
strSql.Append(String.Format(" WHERE comp_num='{0}'", compNum));
}
//查询总数Sql
//counttingSql
string counttingSql = PagingHelper.CreateCountingSql(strSql.ToString());
LogHelper.Debug("contact counttingSql," + counttingSql);
//查询分页Sql
//pagingSql
recordCount =
Convert.ToInt32(db.DbHelper.ExecuteScalar(counttingSql));
dynamic namedParameters = null;
string pagingSql = PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(),
filedOrder);
LogHelper.Debug("contact pagingSql," + pagingSql);
try
{
DataTable conTable =
db.DbHelper.ExecuteDataTable(pagingSql, namedParameters);
for (int i = 0; i < conTable.Rows.Count; i++)
{
for (int j = 0; j < conTable.Columns.Count; j++)
{
if (conTable.Columns[j].ColumnName == "con_fields")
{
DataColumn fieldColumn = conTable.Columns[j];
//转换字段
string fieldString = conTable.Rows[i][j].ToString();
IDictionary <string, JToken> fieldsDictionary =
(JObject)JsonConvert.DeserializeObject(fieldString);
LogHelper.Debug(conTable.Rows[i][j].ToString());
//循环添加新字段
foreach (var f in fieldsDictionary)
{
DataColumn newColumn = new DataColumn();
newColumn.ColumnName = f.Key;
newColumn.DefaultValue = f.Value;
conTable.Columns.Add(newColumn);
}
//删除此字段
conTable.Columns.Remove(fieldColumn);
}
}
}
return(conTable);
}
catch (Exception ex)
{
recordCount = 0;
LogHelper.Error("联系人扩展失败：", ex);
return(null);
}
}
}
