/// <summary> /// 根据查询条件查询数据 /// </summary> /// <param name="userId"></param> /// <param name="para"></param> /// <returns></returns> public QueryResultDto GetResult(long userId, QueryParaDto para) { QueryResultDto result = new QueryResultDto(); int count = 0; string sql = GetSql(userId, para, out count); // 根据查询条件获取查询sql语句 result.count = count; result.query_type_id = para.query_type_id; result.para_group_id = para.para_group_id; if (count == 0) // 查询记录总数为0 { return(result); } // 计算分页信息 if (para.page_size == 0) { para.page_size = _pageSize; } int totalPage = count / para.page_size; if (count % para.page_size != 0) { ++totalPage; } if (para.page <= 0) { para.page = 1; } if (para.page > totalPage) { para.page = totalPage; } int offset = (para.page - 1) * para.page_size; // 缓存查询sql语句 /* * CacheQuerySqlDto sqlCache = new CacheQuerySqlDto * { * query_page_name = para.query_page_name, * count = count, * page_size = para.page_size, * page_count = totalPage, * query_sql = sql * }; * string queryId = userId + new Random().Next(1000, 9999).ToString(); * CachedInfoBLL.SetQuerySql(queryId, sqlCache, _sqlExpireMins); */ // 获取查询结果 sql = sql + $" LIMIT {offset},{para.page_size}"; var table = new sys_query_type_user_dal().ExecuteDataTable(sql); List <Dictionary <string, object> > list = new List <Dictionary <string, object> >(); foreach (DataRow row in table.Rows) { Dictionary <string, object> column = new Dictionary <string, object>(); foreach (DataColumn col in table.Columns) { column.Add(col.ColumnName, row[col.ColumnName]); } list.Add(column); } result.order_by = para.order_by; result.page = para.page; result.page_count = totalPage; result.page_size = para.page_size; //result.query_id = queryId; result.query_id = ""; result.result = list; return(result); }
/// <summary> /// 生成查询参数sql字符串 /// </summary> /// <param name="userId"></param> /// <param name="para"></param> /// <returns></returns> private string GetPara(long userId, QueryParaDto para) { if (para == null || para.query_params == null) { return(""); } StringBuilder queryPara = new StringBuilder(); queryPara.Append("'{"); // 组合查询条件 d_query_para_dal paraDal = new d_query_para_dal(); para.query_params = para.query_params.OrderBy(_ => _.id).ToList(); foreach (var p in para.query_params) { if (p.value == null && p.value2 == null) { continue; } var param = paraDal.FindSignleBySql <d_query_para>($"SELECT * FROM d_query_para WHERE id={p.id} AND query_type_id={para.query_type_id}"); if (param == null) { continue; } if (param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.DATE || param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.DATETIME || param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.NUMBER || param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.TIMESPAN) // 数值和日期类型 { if (param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.TIMESPAN) { DateTime t1 = DateTime.MinValue, t2 = DateTime.MinValue; if ((p.value != null && !DateTime.TryParse(p.value, out t1)) || (p.value2 != null && !DateTime.TryParse(p.value2, out t2))) { continue; } if (p.value != null) { p.value = Tools.Date.DateHelper.ToUniversalTimeStamp(t1).ToString(); } if (p.value2 != null) { p.value2 = Tools.Date.DateHelper.ToUniversalTimeStamp(t2).ToString(); } } queryPara.Append('"').Append(param.col_name).Append("\":"); if (p.value != null) { queryPara.Append('"').Append(param.col_name).Append(" >= '").Append(p.value).Append("'"); if (p.value2 != null) { queryPara.Append(" and ").Append(param.col_name).Append(" <= '").Append(p.value2).Append("'\""); } else { queryPara.Append('"'); } } else { queryPara.Append('"').Append(param.col_name).Append(" <= '").Append(p.value2).Append("'\""); } } else { if (p.value == null) { continue; } queryPara.Append('"').Append(param.col_name).Append("\":\"").Append(p.value).Append('"'); } queryPara.Append(","); } // 移除最后条件末尾的, if (queryPara.Length > 2) { queryPara = queryPara.Remove(queryPara.Length - 1, 1); } queryPara.Append("}'"); return(queryPara.ToString()); }
/// <summary> /// 展示我的工单工作列表 /// </summary> public void LoadMyTicketWorkList(HttpContext context) { QueryCommonBLL bll = new QueryCommonBLL(); var ticketResultPara = bll.GetResultParaSelect(LoginUserId, 264);// groupId QueryParaDto tickeQueryPara = new QueryParaDto(); tickeQueryPara.query_params = new List <Para>(); tickeQueryPara.query_params.Add(new Para() { id = 3572, value = LoginUserId.ToString() }); tickeQueryPara.query_type_id = (int)QueryType.MyWorkListTicket; tickeQueryPara.para_group_id = 264; var ticketQueryResult = bll.GetResult(LoginUserId, tickeQueryPara); System.Text.StringBuilder thisHtml = new System.Text.StringBuilder(); var thisWorkList = new DAL.sys_work_list_dal().GetByResId(LoginUserId); var isJiShi = ""; // 页面打开工单时,是否计时 if (thisWorkList != null && thisWorkList.auto_start == 1) { isJiShi = "JiShi"; } if (ticketQueryResult != null && ticketQueryResult.result != null) { var stDal = new DAL.sdk_task_dal(); foreach (var rslt in ticketQueryResult.result) { // DisabledState 不计时 添加此状态禁用 if (ticketResultPara != null && ticketResultPara.Count > 0) { var id = ""; var idPara = ticketResultPara.FirstOrDefault(_ => _.type == (int)EMT.DoneNOW.DTO.DicEnum.QUERY_RESULT_DISPLAY_TYPE.ID); if (idPara != null) { id = rslt[idPara.name].ToString(); } if (id == "") { continue; } var thisTicket = stDal.FindNoDeleteById(long.Parse(id)); if (thisTicket == null) { continue; } var isDisabled = ""; if (thisTicket.status_id == (int)DicEnum.TICKET_STATUS.DONE) { isDisabled = "DisabledState"; } thisHtml.AppendFormat($"<div class='WorkListItem' data-task-id='{id}'><div class='Left' onclick=\"PageNewOpenTicket('{id}','{isJiShi}')\"><div class='StopwatchContainer {id}ThisWatch'><div class='StopwatchTime {isDisabled}'>{(!string.IsNullOrEmpty(isDisabled)?"--:--:--":"00:00:00")}</div><div class='StopwatchButton Play {isDisabled}'></div><div class='StopwatchButton Record {isDisabled}'></div><div class='StopwatchButton Stop {isDisabled}'></div></div><div class='WorkListLineItemDetailsContainer'>"); //ticketResultPara = ticketResultPara.Where(_=>_.type!= (int)EMT.DoneNOW.DTO.DicEnum.QUERY_RESULT_DISPLAY_TYPE.ID).Take(6).ToList(); foreach (var para in ticketResultPara) { if (para.type == (int)EMT.DoneNOW.DTO.DicEnum.QUERY_RESULT_DISPLAY_TYPE.ID) { continue; } thisHtml.Append("<p>" + rslt[para.name] + "</p>"); } thisHtml.AppendFormat("</div></div><div class='Right'><div class='WorkListItemButtons'><div class='WorkListClose Icon' onclick=\"NewOpenTicket('{0}')\"></div><div class='WorkListArrow Icon' onclick=\"RemoveWorkListTicket('{0}','1')\"></div></div><div class='Grip'></div></div></div>", id); } } } context.Response.Write(thisHtml.ToString()); }
private void QueryData() { queryParaValue.Clear(); resultPara = bll.GetResultParaSelect(GetLoginUserId(), paraGroupId); // 获取查询结果列信息 //var keys = Request.Form; var keys = HttpContext.Current.Request.QueryString; string order = keys["order"]; // order by 条件 int page; if (!int.TryParse(keys["page_num"], out page)) { page = 1; } //int page = string.IsNullOrEmpty(keys["page_num"]) ? 1 : int.Parse(keys["page_num"]); // 查询页数 int pageSize = string.IsNullOrEmpty(keys["page_size"]) ? 0 : int.Parse(keys["page_size"]); // 查询每页个数 // 检查order if (order != null) { order = order.Trim(); string[] strs = order.Split(' '); if (strs.Length != 2 || (!strs[1].ToLower().Equals("asc") && !strs[1].ToLower().Equals("desc"))) { order = ""; } } if (string.IsNullOrEmpty(order)) { order = null; } if (!string.IsNullOrEmpty(keys["search_id"])) // 使用缓存查询条件 { queryResult = bll.GetResult(GetLoginUserId(), keys["search_id"], page, order); return; } if (objId != 0) // 查询条件只有实体id,可以默认带入id查找 { var cdts = bll.GetConditionParaVisiable(GetLoginUserId(), paraGroupId); if (cdts.Count == 1) { QueryParaDto queryPara = new QueryParaDto(); queryPara.query_params = new List <Para>(); Para pa = new Para(); // 975 pa.id = cdts[0].id; pa.value = objId.ToString(); queryPara.query_params.Add(pa); queryPara.query_type_id = queryTypeId; queryPara.para_group_id = paraGroupId; queryPara.page = page; queryPara.order_by = order; queryPara.page_size = pageSize; queryResult = bll.GetResult(GetLoginUserId(), queryPara); return; } } if (queryResult == null) // 不使用缓存或缓存过期 { var para = bll.GetConditionParaVisiable(GetLoginUserId(), paraGroupId); // 查询条件信息 QueryParaDto queryPara = new QueryParaDto(); queryPara.query_params = new List <Para>(); foreach (var p in para) { Para pa = new Para(); if (p.data_type == (int)DicEnum.QUERY_PARA_TYPE.NUMBER || p.data_type == (int)DicEnum.QUERY_PARA_TYPE.DATE || p.data_type == (int)DicEnum.QUERY_PARA_TYPE.DATETIME || p.data_type == (int)DicEnum.QUERY_PARA_TYPE.TIMESPAN) // 数值和日期类型是范围值 { string ql = keys["con" + p.id.ToString() + "_l"]; string qh = keys["con" + p.id.ToString() + "_h"]; if (string.IsNullOrEmpty(ql) && string.IsNullOrEmpty(qh)) // 空值,跳过 { continue; } if (!string.IsNullOrEmpty(ql)) { queryParaValue.Add(new DictionaryEntryDto("con" + p.id.ToString() + "_l", ql)); // 记录查询条件和条件值 pa.value = ql; } if (!string.IsNullOrEmpty(qh)) { queryParaValue.Add(new DictionaryEntryDto("con" + p.id.ToString() + "_h", qh)); // 记录查询条件和条件值 pa.value2 = qh; } pa.id = p.id; queryPara.query_params.Add(pa); } else // 其他类型一个值 { string val = keys["con" + p.id.ToString()]; if (string.IsNullOrEmpty(val)) { continue; } pa.id = p.id; pa.value = val; queryParaValue.Add(new DictionaryEntryDto("con" + p.id.ToString(), val)); // 记录查询条件和条件值 queryPara.query_params.Add(pa); } } if (queryTypeId == (int)QueryType.PROJECT_BASELINE) { if (thisProject.baseline_project_id != null) { Para pa = new Para(); pa.id = 975; pa.value = thisProject.baseline_project_id.ToString(); } } queryPara.query_type_id = queryTypeId; queryPara.para_group_id = paraGroupId; queryPara.page = page; queryPara.order_by = order; queryPara.page_size = pageSize; queryResult = bll.GetResult(GetLoginUserId(), queryPara); } }
protected void Page_Load(object sender, EventArgs e) { long orderId = 0; if (!string.IsNullOrEmpty(Request.QueryString["id"])) { orderId = long.Parse(Request.QueryString["id"]); } string itemIds = Request.QueryString["ids"]; List <ivt_order_product> items = null; // 指定接收的采购项 if (!string.IsNullOrEmpty(itemIds)) { items = bll.GetPurchaseItems(itemIds); if (items == null || items.Count == 0) { Response.End(); return; } long ordId = items[0].order_id; foreach (var itm in items) { if (itm.order_id != ordId) { Response.Write("<script>alert('一次只能接收同一个采购订单的采购项');window.close();</script>"); Response.End(); } } orderId = ordId; //Session["PurchaseReceiveItem"] = items; } else if (orderId == 0) { Response.End(); return; } order = bll.GetPurchaseOrder(orderId); if (order.status_id == (int)DicEnum.PURCHASE_ORDER_STATUS.NEW || order.status_id == (int)DicEnum.PURCHASE_ORDER_STATUS.CANCELED) { Response.Write("<script>alert('新增和取消状态的订单不能接收/取消接收');window.close();</script>"); Response.End(); } QueryCommonBLL queryBll = new QueryCommonBLL(); QueryParaDto queryPara = new QueryParaDto(); queryPara.query_params = new List <Para>(); queryPara.query_params.Add(new Para { id = 1171, value = orderId.ToString() }); queryPara.query_type_id = (long)QueryType.PurchaseItem; queryPara.para_group_id = 160; queryPara.page = 1; queryPara.page_size = 500; queryResult = queryBll.GetResult(0, queryPara); if (!string.IsNullOrEmpty(itemIds)) // 指定了采购项,只显示指定的采购项,其他不显示 { for (int idx = queryResult.result.Count - 1; idx >= 0; --idx) { if (!items.Exists(_ => _.id.ToString() == (queryResult.result[idx]["采购项id"]).ToString())) { queryResult.result.RemoveAt(idx); queryResult.count = queryResult.count - 1; } } } if (!IsPostBack) { Session["PurchaseReceiveItemSn"] = new Dictionary <long, string>(); // 接收的采购项串号,或者取消接收的串号id Session["PurchaseUnReceiveItemSn"] = new Dictionary <long, string>(); // 取消接收的采购项串号 } else { List <InventoryOrderReceiveItemDto> itemList = new List <InventoryOrderReceiveItemDto>(); var sns = Session["PurchaseReceiveItemSn"] as Dictionary <long, string>; foreach (var itm in queryResult.result) { InventoryOrderReceiveItemDto recvItem = new InventoryOrderReceiveItemDto(); recvItem.id = long.Parse(itm["采购项id"].ToString()); var cost = Request.Form["cost" + recvItem.id]; var recvCnt = Request.Form["receive" + recvItem.id]; if (string.IsNullOrEmpty(recvCnt) || int.Parse(recvCnt) == 0) { continue; } recvItem.count = int.Parse(recvCnt); recvItem.cost = string.IsNullOrEmpty(cost) ? 0 : decimal.Parse(cost); if (sns.ContainsKey(recvItem.id)) { sns[recvItem.id] = sns[recvItem.id].Replace("\r\n", ","); recvItem.sns = sns[recvItem.id].Split(',').ToList(); recvItem.sns.RemoveAll(_ => string.IsNullOrEmpty(_)); } itemList.Add(recvItem); } if (itemList.Count == 0) { Response.Write("<script>alert('请选择接收项');</script>"); return; } decimal freight_cost = 0; if (!string.IsNullOrEmpty(Request.Form["freight_cost"])) { freight_cost = decimal.Parse(Request.Form["freight_cost"]); } bll.OrderReceive(orderId, Request.Form["vendor_invoice_no"], freight_cost, itemList, LoginUserId); Response.Write("<script>window.close();self.opener.location.reload();</script>"); Response.End(); } }
/// <summary> /// 根据查询条件获取查询结果 /// </summary> private void QueryData() { queryParaValue.Clear(); resultPara = bll.GetResultParaSelect(GetLoginUserId(), paraGroupId); // 获取查询结果列信息 //queryResult = bll.getDataTest();return; var keys = HttpContext.Current.Request; string order = keys["order"]; // order by 条件 int page = string.IsNullOrEmpty(keys["page_num"]) ? 1 : int.Parse(keys["page_num"]); // 查询页数 // 检查order if (order != null) { order = order.Trim(); string[] strs = order.Split(' '); if (strs.Length != 2 || (!strs[1].ToLower().Equals("asc") && !strs[1].ToLower().Equals("desc"))) { order = ""; } } if (string.IsNullOrEmpty(order)) { order = null; } if (!string.IsNullOrEmpty(keys["search_id"])) // 使用缓存查询条件 { queryResult = bll.GetResult(GetLoginUserId(), keys["search_id"], page, order); } if (queryResult == null) // 不使用缓存或缓存过期 { var para = bll.GetConditionParaAll(GetLoginUserId(), paraGroupId); // 查询条件信息 QueryParaDto queryPara = new QueryParaDto(); queryPara.query_params = new List <Para>(); foreach (var p in para) { Para pa = new Para(); jqueryString += $"$('#con{p.id.ToString()}').val('{(!string.IsNullOrEmpty(keys["con" + p.id.ToString()])? keys["con" + p.id.ToString()] : "")}');"; if (p.data_type == (int)DicEnum.QUERY_PARA_TYPE.NUMBER || p.data_type == (int)DicEnum.QUERY_PARA_TYPE.DATE || p.data_type == (int)DicEnum.QUERY_PARA_TYPE.DATETIME) // 数值和日期类型是范围值 { string ql = keys["con" + p.id.ToString() + "_l"]; string qh = keys["con" + p.id.ToString() + "_h"]; if (string.IsNullOrEmpty(ql) && string.IsNullOrEmpty(qh)) // 空值,跳过 { continue; } if (!string.IsNullOrEmpty(ql)) { queryParaValue.Add(new DictionaryEntryDto("con" + p.id.ToString() + "_l", ql)); // 记录查询条件和条件值 pa.value = ql; } if (!string.IsNullOrEmpty(qh)) { queryParaValue.Add(new DictionaryEntryDto("con" + p.id.ToString() + "_h", qh)); // 记录查询条件和条件值 pa.value2 = qh; } pa.id = p.id; queryPara.query_params.Add(pa); } else // 其他类型一个值 { string val = keys["con" + p.id.ToString()]; if (string.IsNullOrEmpty(val)) { continue; } pa.id = p.id; pa.value = val; queryParaValue.Add(new DictionaryEntryDto("con" + p.id.ToString(), val)); // 记录查询条件和条件值 queryPara.query_params.Add(pa); } } queryPara.query_type_id = queryTypeId; queryPara.para_group_id = paraGroupId; queryPara.page = page; queryPara.order_by = order; queryPara.page_size = 0; queryResult = bll.GetResult(GetLoginUserId(), queryPara); } }
/// <summary> /// 根据库存产品id列表获取采购订单的采购项信息 /// </summary> /// <param name="pdtIds"></param> /// <returns></returns> public PurchaseOrderItemManageDto InitPurchaseOrderItems(string pdtIds, out long vendorId) { vendorId = 0; PurchaseOrderItemManageDto dto = new PurchaseOrderItemManageDto(); string sql = $"select product_id,warehouse_id,(select name from ivt_product where id=product_id) as product,(select unit_cost from ivt_product where id=product_id) as unit_cost,(select name from ivt_warehouse where id=warehouse_id) as locationName,(quantity_maximum-quantity) as quantity from ivt_warehouse_product where id in({pdtIds})"; dto.items = dal.FindListBySql <PurchaseItemDto>(sql); for (var i = 0; i < dto.items.Count; ++i) { dto.items[i].id = dto.index++; if (dto.items[i].quantity < 0) { dto.items[i].quantity = 0; } } if (dto.items.Count == 0) { sql = $"select id,id as costId,product_id,(select id from ivt_warehouse where is_default=1 and delete_time=0) as warehouse_id,unit_cost,(select name from ivt_product where id=product_id) as product,(select name from ivt_warehouse where is_default=1 and delete_time=0) as locationName from ctt_contract_cost where id in({pdtIds})"; dto.items = dal.FindListBySql <PurchaseItemDto>(sql); if (dto.items.Count == 0) { return(dto); } QueryCommonBLL queryBll = new QueryCommonBLL(); QueryParaDto queryPara = new QueryParaDto(); queryPara.query_params = new List <Para>(); queryPara.query_type_id = (long)QueryType.PurchaseFulfillment; queryPara.para_group_id = 156; queryPara.page = 1; queryPara.page_size = 500; QueryResultDto queryResult = queryBll.GetResult(0, queryPara); if (queryResult.count == 0) { return(dto); } for (var i = 0; i < dto.items.Count; ++i) { var find = queryResult.result.Find(_ => _["成本id"].ToString().Equals(dto.items[i].id.ToString())); if (find == null) { continue; } dto.items[i].quantity = (int)(string.IsNullOrEmpty(find["采购数量"].ToString()) ? 0 : decimal.Parse(find["采购数量"].ToString())); dto.items[i].accountName = find["销售订单"].ToString() + "(" + find["客户"].ToString() + ")"; dto.items[i].contractName = find["工单或项目或合同"].ToString(); dto.items[i].id = dto.index++; } } string productIds = ""; for (var i = 0; i < dto.items.Count; ++i) { productIds += dto.items[i].product_id + ","; long lctPdtId = dal.FindSignleBySql <long>($"select id from ivt_warehouse_product where product_id={dto.items[i].product_id} and warehouse_id={dto.items[i].warehouse_id}"); var lctPdt = GetIvtProductEdit(lctPdtId); if (lctPdt != null) { dto.items[i].ivtQuantity = lctPdt.quantity; dto.items[i].onOrder = lctPdt.on_order; dto.items[i].max = lctPdt.quantity_maximum; dto.items[i].min = lctPdt.quantity_minimum; dto.items[i].reserved_picked = lctPdt.reserved_picked; dto.items[i].back_order = lctPdt.back_order; if (string.IsNullOrEmpty(lctPdt.reserved_picked)) { dto.items[i].avaCnt = ""; } else { dto.items[i].avaCnt = (lctPdt.quantity - int.Parse(lctPdt.reserved_picked)).ToString(); } } } // 获取采购项产品的默认供应商,如果只有一个默认供应商,则返回 if (productIds != "") { productIds = productIds.Substring(0, productIds.Length - 1); var ids = dal.FindListBySql <long>($"select vendor_account_id from ivt_product_vendor where is_default=1 and product_id in({productIds})"); if (ids != null && ids.Count == 1) { vendorId = ids[0]; } } return(dto); }