Ejemplo n.º 1
0
 public static string SELECT(QueryConditions conditions)
 {
     return(WorkReport.SELECT(conditions, true));
 }
Ejemplo n.º 2
0
 public static WorkReports GetList(QueryConditions conditions, bool childs)
 {
     return(GetList(WorkReports.SELECT(conditions), childs));
 }
Ejemplo n.º 3
0
 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));
 }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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));
 }
Ejemplo n.º 8
0
 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));
 }
Ejemplo n.º 12
0
 public List <Category> Query(QueryConditions queryConditions, out int totalCount)
 {
     return(Query <Category>(queryConditions, out totalCount));
 }
Ejemplo n.º 13
0
        /// <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));
 }
Ejemplo n.º 15
0
        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;
            }
        }