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