public static string SELECT(QueryConditions conditions)
{
return(WorkReport.SELECT(conditions, true));
}
public static WorkReports GetList(QueryConditions conditions, bool childs)
{
return(GetList(WorkReports.SELECT(conditions), childs));
}
public List <Dish> Query(QueryConditions queryConditions, out int totalCount)
{
return(Query <Dish>(queryConditions, out totalCount));
}
public List <VInventoryList> Query(QueryConditions queryConditions, out int totalCount)
{
return(Query <VInventoryList>(queryConditions, out totalCount));
}
internal static string SELECT_FIELDS(EQueryType queryType, QueryConditions conditions)
{
string query;
if (conditions.Groups == null)
{
query = @"
SELECT
WO.*
,COALESCE(WR.""CODE"", '') AS ""WORK_REPORT_ID""
,COALESCE(WC.""NAME"", '') AS ""CATEGORY""
,COALESCE(WC.""OID"", 0) AS ""OID_CATEGORY""
,COALESCE(EX.""CODIGO"", '') AS ""EXPEDIENT_ID""";
}
else
{
query = @"
SELECT
(MAX(WO.""OID"")::varchar || '00' || MAX(WO.""ENTITY_TYPE"")::varchar)::bigint AS ""OID""
,MAX(WO.""OID_RESOURCE"") AS ""OID_RESOURCE""
,MAX(WO.""OID_WORK_REPORT"") AS ""OID_WORK_REPORT""
,MAX(WO.""ENTITY_TYPE"") AS ""ENTITY_TYPE""
,1 AS ""AMOUNT""
,MAX(WO.""FROM"") AS ""FROM""
,MAX(WO.""TILL"") AS ""TILL""
,SUM(WO.""HOURS"" * WO.""AMOUNT"") AS ""HOURS""
,AVG(WO.""COST"") AS ""COST""
,SUM(WO.""EXTRA_COST"") AS ""EXTRA_COST""
,SUM(WO.""TOTAL"") AS ""TOTAL""
,'' AS ""COMMENTS""
,MAX(WR.""CODE"") AS ""WORK_REPORT_ID""
,COALESCE(MAX(WC.""OID""), 0) AS ""OID_CATEGORY""
,COALESCE(MAX(WC.""NAME""), '') AS ""CATEGORY""
,COALESCE(MAX(EX.""CODIGO""), '') AS ""EXPEDIENT_ID""";
}
switch (queryType)
{
case EQueryType.ALL:
query += @"
,'' AS ""RESOURCE_CODE""
,'' AS ""RESOURCE""" ;
break;
case EQueryType.STAFF:
if (conditions.Groups == null)
{
query += @"
,RS.""CODIGO"" AS ""RESOURCE_CODE""
,RS.""APELLIDOS"" || ', ' || RS.""NOMBRE"" AS ""RESOURCE""" ;
}
else
{
query += @"
,MAX(RS.""CODIGO"") AS ""RESOURCE_CODE""
,MAX(RS.""APELLIDOS"") || ', ' || MAX(RS.""NOMBRE"") AS ""RESOURCE""" ;
}
break;
case EQueryType.TOOLS:
if (conditions.Groups == null)
{
query += @"
,RS.""CODE"" AS ""RESOURCE_CODE""
,RS.""NAME"" AS ""RESOURCE""" ;
}
else
{
query += @"
,MAX(RS.""CODE"") AS ""RESOURCE_CODE""
,MAX(RS.""NAME"") AS ""RESOURCE""" ;
}
break;
case EQueryType.DELIVERIES:
if (conditions.Groups == null)
{
query += @"
,RS.""CODIGO"" AS ""RESOURCE_CODE""
,'ALBARAN DE OBRA' AS ""RESOURCE""" ;
}
else
{
query += @"
,MAX(RS.""CODIGO"") AS ""RESOURCE_CODE""
,'ALBARAN DE OBRA' AS ""RESOURCE""" ;
}
break;
case EQueryType.CLUSTERED:
query = @"
SELECT " + (long)queryType + @" AS ""QUERY_TYPE""
,DATE_TRUNC('" + conditions.Step.ToString() + @"', WO.""DATE"") AS ""STEP""
,SUM(WO.""TOTAL"") AS ""TOTAL""" ;
break;
}
return(query);
}
internal static string SELECT(QueryConditions conditions, bool lockTable)
{
string query = string.Empty;
if (conditions.Step != EStepGraph.None)
{
query =
SELECT_FIELDS(EQueryType.CLUSTERED, conditions) +
JOIN(conditions);
}
else
{
switch (conditions.EntityType)
{
case ETipoEntidad.Empleado:
query = SELECT_EMPLOYEES(conditions);
break;
case ETipoEntidad.OutputDelivery:
query = SELECT_DELIVERIES(conditions);
break;
case ETipoEntidad.Tool:
query = SELECT_TOOLS(conditions);
break;
case ETipoEntidad.Todos:
default:
if (conditions.Groups != null &&
conditions.Groups.Count == 1 &&
conditions.Groups.Contains("OidCategory"))
{
query =
SELECT_ALL(conditions);
}
else
{
query =
SELECT_EMPLOYEES(conditions) + @"
UNION " +
SELECT_TOOLS(conditions) + @"
UNION " +
SELECT_DELIVERIES(conditions);
}
break;
}
}
if (conditions.Groups != null)
{
query += ORDER(conditions.Orders, "WO", ForeignFields());
query += LIMIT(conditions.PagingInfo);
}
else
{
if (conditions.Step != EStepGraph.None)
{
query += @"
GROUP BY ""STEP""
ORDER BY ""STEP""" ;
}
else
{
query += ORDER(conditions.Orders, "WO", ForeignFields());
query += LIMIT(conditions.PagingInfo);
}
}
query += Common.Common.EntityBase.LOCK("WO", lockTable);
return(query);
}
public List <CODetails> Query(QueryConditions queryConditions, out int totalCount)
{
return(Query <CODetails>(queryConditions, out totalCount));
}
public static string SELECT(QueryConditions conditions)
{
return(SELECT(conditions, true));
}
public static string SELECT(QueryConditions conditions)
{
return(WorkReportResource.SELECT(conditions, false));
}
public static WorkReportResourceList GetList(QueryConditions conditions, bool childs)
{
return(GetList(SELECT(conditions), childs));
}
public List <Bill> Query(QueryConditions queryConditions, out int totalCount)
{
return(Query <Bill>(queryConditions, out totalCount));
}
public List <Category> Query(QueryConditions queryConditions, out int totalCount)
{
return(Query <Category>(queryConditions, out totalCount));
}
/// <summary>
/// تابع ایجاد کوئری سلکت به صورت بازگشتی
/// </summary>
private SelectQuery CreateFullSelectQuery(ReflectionTable node,
QueryConditions tableConditions = null,
FilterRequest filter = null)
{
var result = new SelectQuery()
{
Query = "",
Root = node
};
var nodeQuery = $"select * into #{node.UniqName} from [{node.TableName}] ";
bool hasWhere = false;
if (tableConditions != null)
{
nodeQuery += "where " + tableConditions.GetConditions(node.TableName, node.Cols.Select(q => q.Name));
hasWhere = true;
}
if (filter != null)
{
if (!string.IsNullOrEmpty(filter.SearchPhrase))
{
var searchableCols = node.Cols.Where(q => q.IsSearchable)
.ToList();
if (searchableCols.Any())
{
if (!hasWhere)
{
nodeQuery += " Where ";
}
else
{
nodeQuery += " And ";
}
nodeQuery += "(";
nodeQuery += String.Join(" Or ", searchableCols.Select(q =>
$" {q.Name} like N'%{filter.SearchPhrase}%' "
));
nodeQuery += ")";
}
}
nodeQuery += Environment.NewLine;
nodeQuery +=
$@" ORDER BY [{node.TableName}].{filter.Sort} {filter.SortDirection}
OFFSET {filter.PageSize} * ({filter.PageNumber} - 1) ROWS
FETCH NEXT {filter.PageSize} ROWS ONLY OPTION (RECOMPILE);";
}
nodeQuery += Environment.NewLine;
nodeQuery += $" select * from #{node.UniqName}";
result.Query += nodeQuery;
var stack = new Stack <SelectStackItem>();
if (node.JoinTables != null)
{
foreach (var joinTable in node.JoinTables)
{
stack.Push(new SelectStackItem()
{
Table = joinTable,
ParentUniqName = node.UniqName,
ParentPrimaryKeyName = node.PrimaryKey.Name
});
}
while (stack.Any())
{
var item = stack.Pop();
node = item.Table.ReflectionTable;
nodeQuery = Environment.NewLine;
nodeQuery += $" select * into #{node.UniqName} from [{node.TableName}] ";
if (!item.Table.IsInverse)
{
nodeQuery += $" where [{item.ParentPrimaryKeyName}] in (select [{item.Table.ForeignKey}] from #{item.ParentUniqName})";
}
else
{
nodeQuery += $" where [{item.Table.ForeignKey}] in (select [{item.ParentPrimaryKeyName}] from #{item.ParentUniqName})";
}
var conditions = tableConditions?.GetConditions(node.TableName,
node.Cols.Select(q => q.Name));
if (!String.IsNullOrEmpty(conditions))
{
nodeQuery += " and " + conditions;
}
nodeQuery += Environment.NewLine;
nodeQuery += $" select * from #{node.UniqName}";
result.Query += nodeQuery;
if (node.JoinTables != null)
{
foreach (var joinTable in node.JoinTables)
{
stack.Push(new SelectStackItem()
{
Table = joinTable,
ParentUniqName = node.UniqName,
ParentPrimaryKeyName = node.PrimaryKey.Name
});
}
}
}
}
return(result);
}
public List <User> Query(QueryConditions queryConditions, out int totalCount)
{
return(Query <User>(queryConditions, out totalCount));
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (dtpStartTime.Value > dtpEndTime.Value)
{
MessageBox.Show("开始时间大于结束时间");
return;
}
//if(txtProductLineNo.Text.Trim().Equals(""))
//{
// MessageBox.Show("请输入产线");
// txtProductLineNo.Focus();
// return;
//}
//if(txtProductLineNo.Text.Trim().Length != 3)
//{
// MessageBox.Show("产线必须为3位");
// txtProductLineNo.SelectAll();
// txtProductLineNo.Focus();
// return;
//}
WebService.QueryConditions conditions = new QueryConditions();
if (txtProductLineNo.Text.Trim().Length > 0)
{
conditions.ProductLineNo = txtProductLineNo.Text.Trim();
}
if (txtMaterialNo.Text.Trim().Length > 0)
{
conditions.MaterialNo = txtMaterialNo.Text.Trim();
}
if (txtMaterialDesc.Text.Trim().Length > 0)
{
conditions.MaterialDesc = txtMaterialDesc.Text.Trim();
}
if (txtMaterialStd.Text.Trim().Length > 0)
{
conditions.MaterialStd = txtMaterialStd.Text.Trim();
}
if (dtpStartTime.Checked)
{
conditions.StartTime = dtpStartTime.Value;
}
if (dtpEndTime.Checked)
{
conditions.EndTime = dtpEndTime.Value;
}
List <WebService.Stock_Model> modelList = null;
string strError = "";
if (!WMS.Common.WMSWebService.service.GetCapacityForWMS(ref modelList, conditions, ref strError))
{
MessageBox.Show("查询失败:" + strError);
return;
}
//添加总计
Stock_Model sum_model = new Stock_Model();
foreach (Stock_Model st_model in modelList)
{
sum_model.ErpQty += st_model.ErpQty;
sum_model.SaveQty += st_model.SaveQty;
sum_model.TrayQty += st_model.TrayQty;
sum_model.TotalQty += st_model.TotalQty;
}
sum_model.ProductLineNo = "总计";
modelList.Add(sum_model);
dataGridView1.DataSource = modelList;
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
if (dataGridView1.Columns[i].Name.Equals("ProductLineNo"))
{
dataGridView1.Columns[i].HeaderText = "产线";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("MaterialNo"))
{
dataGridView1.Columns[i].HeaderText = "物料编码";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("MaterialDesc"))
{
dataGridView1.Columns[i].HeaderText = "物料名称";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("MaterialStd"))
{
dataGridView1.Columns[i].HeaderText = "规格型号";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("ErpQty"))
{
dataGridView1.Columns[i].HeaderText = "已入库数量";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("SaveQty"))
{
dataGridView1.Columns[i].HeaderText = "已保存未过账数量";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("TrayQty"))
{
dataGridView1.Columns[i].HeaderText = "已组托未入库数量";
continue;
}
if (dataGridView1.Columns[i].Name.Equals("TotalQty"))
{
dataGridView1.Columns[i].HeaderText = "总数量";
continue;
}
dataGridView1.Columns[i].Visible = false;
}
}