예제 #1
0
        public void ExportKBTurnoverRate(KanbanCardSearchModel searchModel)
        {
            if (searchModel.StartDate == null || searchModel.EndDate == null)
            {
                SaveWarningMessage("开始结束时间不能为空。");
            }
            else if (searchModel.StartDate.Value.AddDays(31) < searchModel.EndDate)
            {
                SaveWarningMessage("一次最多只能查30天的。");
            }
            else
            {

                if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
                {
                    if (searchModel.CardNo.Length > 2 && searchModel.CardNo.Substring(0, 2).ToUpper() == "$K")
                    {
                        searchModel.CardNo = searchModel.CardNo.Substring(2, searchModel.CardNo.Length - 2);
                    }
                }
                string searchSql = PrepareTurnoverRateSearchStatement(searchModel);

                IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(searchSql + " order by CardNo ");
                IList<KanbanCard> opList = new List<KanbanCard>();
                if (searchResult != null && searchResult.Count > 0)
                {
                    #region
                    //c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,c.Qty,c.opref,CONVERT(varchar(10), s.CreateDate, 121) as '扫描日期',COUNT(*) as '扫描次数'
                    opList = (from tak in searchResult
                              select new KanbanCard
                              {
                                  CardNo = (string)tak[0],
                                  OpRefSequence = (string)tak[1],
                                  Item = (string)tak[2],
                                  ReferenceItemCode = (string)tak[3],
                                  ItemDescription = (string)tak[4],
                                  Qty = (decimal)tak[5],
                                  OpRef = (string)tak[6],
                                  ScanDate = tak[7] == null ? searchModel.StartDate.Value.Date : Convert.ToDateTime(tak[7]),
                                  //ScanDate = tak[7] == null ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[7]) < searchModel.StartDate.Value.Date ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[7]) > searchModel.EndDate.Value.Date ? searchModel.EndDate.Value.Date : Convert.ToDateTime(tak[7]))),
                                  //ScanDate = Convert.ToDateTime(tak[7]),
                                  ScanCount = (int?)tak[8],
                                  Container1 = (string)tak[9],
                                  ContainerDescription = (string)tak[10],
                              }).ToList();
                    #endregion

                    var groups = opList.GroupBy(g => new { g.CardNo, g.OpRefSequence, g.Item, g.ReferenceItemCode, g.ItemDescription, g.OpRef, g.Qty }).ToList();

                    var listDays = opList.Select(o => o.ScanDate).Distinct().OrderBy(o => o.Value).ToList();
                    #region
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                    ISheet sheet1 = hssfworkbook.CreateSheet("sheet1");
                    MemoryStream output = new MemoryStream();

                    #region 写入字段
                    IRow rowHeader = sheet1.CreateRow(0);
                    // 零件号PRP	旧零件号	零件名称	看板号	工位	包装量
                    for (int i = 0; i < 10; i++)
                    {
                        if (i == 0) //序号
                        {
                            rowHeader.CreateCell(i).SetCellValue("序号");
                        }
                        else if (i == 1)  //OpRefSequence
                        {
                            rowHeader.CreateCell(i).SetCellValue("看板号");
                        }
                        else if (i == 2) //Item
                        {
                            rowHeader.CreateCell(i).SetCellValue("物料代码");
                        }
                        else if (i == 3)    //ReferenceItemCode
                        {
                            rowHeader.CreateCell(i).SetCellValue("旧图号");
                        }
                        else if (i == 4)    //ItemDescription
                        {
                            rowHeader.CreateCell(i).SetCellValue("物料描述");
                        }
                        else if (i == 5)    //ItemDescription
                        {
                            rowHeader.CreateCell(i).SetCellValue("容器代码");
                        }
                        else if (i == 6)    //ItemDescription
                        {
                            rowHeader.CreateCell(i).SetCellValue("容器描述");
                        }
                        else if (i == 7)      //Qty
                        {
                            rowHeader.CreateCell(i).SetCellValue("包装量");
                        }
                        else if (i == 8)      //OpRef
                        {
                            rowHeader.CreateCell(i).SetCellValue("工位");
                        }
                        else
                        {
                            //foreach (var date in listDays)
                            //{
                            //    rowHeader.CreateCell(i).SetCellValue(date.Value.ToShortDateString());
                            //    i++;
                            //}
                            var minDate = searchModel.StartDate.Value;
                            var maxDate = searchModel.EndDate.Value;
                            TimeSpan ts = minDate - maxDate; int sub = ts.Days;
                            while (minDate <= maxDate)
                            {
                                rowHeader.CreateCell(i).SetCellValue(minDate.ToShortDateString());
                                minDate = minDate.AddDays(1);
                                i++;
                            }
                        }
                    }
                    #endregion

                    #region 写入数值
                    int l = 0;
                    int rowIndex = 1;
                    foreach (var g in groups)
                    {
                        var firstPlan = g.First();
                        l++;
                        IRow rowDetail = sheet1.CreateRow(rowIndex);
                        int cell = 0;
                        rowDetail.CreateCell(cell++).SetCellValue(l);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.OpRefSequence);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.Item);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.ReferenceItemCode);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.ItemDescription);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.Container1);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.ContainerDescription);
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.Qty.ToString("0.##"));
                        rowDetail.CreateCell(cell++).SetCellValue(firstPlan.OpRef);
                        var minDate1 = searchModel.StartDate.Value;
                        var maxDate1 = searchModel.EndDate.Value;
                        int i = 0;
                        while (minDate1 <= maxDate1)
                        {
                            var curenKbs = g.Where(p => p.ScanDate == minDate1);
                            var curenKb = curenKbs.Count() > 0 ? curenKbs.First() : new KanbanCard();
                            var createCell = rowDetail.CreateCell(cell++);
                            createCell.SetCellType(CellType.NUMERIC);
                            createCell.SetCellValue(Convert.ToDouble(curenKb.ScanCount));
                            minDate1 = minDate1.AddDays(1);
                        }
                        //foreach (var day in listDays)
                        //{
                        //    var curenKbs = g.Where(p => p.ScanDate == day);
                        //    var curenKb = curenKbs.Count() > 0 ? curenKbs.First() : new KanbanCard();
                        //    var createCell = rowDetail.CreateCell(cell++);
                        //    createCell.SetCellType(CellType.NUMERIC);
                        //    createCell.SetCellValue(Convert.ToDouble(curenKb.ScanCount));
                        //}
                        rowIndex++;
                    }
                    #endregion

                    hssfworkbook.Write(output);
                    string filename = "ExportKBTurnoverRate.xls";
                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
                    Response.Clear();
                    Response.BinaryWrite(output.GetBuffer());
                    Response.End();

                    #endregion

                }
            }




        }
예제 #2
0
        public ActionResult _AjaxTurnoverRateList(GridCommand command, KanbanCardSearchModel searchModel)
        {
            #region  grid column
            var columns = new List<GridColumnSettings>();
            columns.Add(new GridColumnSettings
            {
                Member = "CardNo",
                Title = Resources.KB.KanbanCard.KanbanCard_CardNo,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "OpRefSequence",
                Title = Resources.KB.KanbanCard.KanbanCard_OpRefSequence,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "Item",
                Title = Resources.KB.KanbanCard.KanbanCard_Item,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "ReferenceItemCode",
                Title = Resources.KB.KanbanCard.KanbanCard_ReferenceItemCode,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "ItemDescription",
                Title = Resources.KB.KanbanCard.KanbanCard_ItemDescription,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "Container1",
                Title = Resources.SCM.FlowDetail.FlowDetail_Container,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "ContainerDescription",
                Title = Resources.SCM.FlowDetail.FlowDetail_ContainerDescription,
                Sortable = false
            });
           
            columns.Add(new GridColumnSettings
            {
                Member = "Qty",
                Title = Resources.KB.KanbanCard.KanbanCard_Qty,
                Sortable = false
            });
            columns.Add(new GridColumnSettings
            {
                Member = "OpRef",
                Title = Resources.KB.KanbanCard.KanbanCard_OpRef,
                Sortable = false
            });

            #endregion
            if (searchModel.StartDate == null || searchModel.EndDate == null)
            {
            }
            else if (searchModel.StartDate.Value.AddDays(31) < searchModel.EndDate)
            {
            }
            else
            {
                if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
                {
                    if (searchModel.CardNo.Length > 2 && searchModel.CardNo.Substring(0, 2).ToUpper() == "$K")
                    {
                        searchModel.CardNo = searchModel.CardNo.Substring(2, searchModel.CardNo.Length - 2);
                    }
                }
                command.PageSize = command.PageSize == 0 ? 20000 : command.PageSize;
                command.Page = command.Page == 0 ? 1 : command.Page;
                string searchSql = PrepareTurnoverRateSearchStatement(searchModel);

                int total = this.genericMgr.FindAllWithNativeSql<int>("select count(*) from (" + searchSql + ") as r1").First();
                string sortingStatement = " order by  CardNo ";
                searchSql = string.Format("select * from (select RowId=ROW_NUMBER()OVER({0}),r1.* from ({1}) as r1 ) as rt where rt.RowId between {2} and {3}", sortingStatement, searchSql, (command.Page - 1) * command.PageSize, command.PageSize * command.Page);
                IList<object[]> searchResult = this.genericMgr.FindAllWithNativeSql<object[]>(searchSql);
                IList<KanbanCard> opList = new List<KanbanCard>();
                if (searchResult != null && searchResult.Count > 0)
                {
                    #region
                    //c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,c.Qty,c.opref,CONVERT(varchar(10), s.CreateDate, 121) as '扫描日期',COUNT(*) as '扫描次数'
                    opList = (from tak in searchResult
                              select new KanbanCard
                              {
                                  CardNo = (string)tak[1],
                                  OpRefSequence = (string)tak[2],
                                  Item = (string)tak[3],
                                  ReferenceItemCode = (string)tak[4],
                                  ItemDescription = (string)tak[5],
                                  Qty = (decimal)tak[6],
                                  OpRef = (string)tak[7],
                                  //ScanDate = Convert.ToDateTime(tak[8]),
                                  ScanDate = tak[8] == null ? searchModel.StartDate.Value.Date : Convert.ToDateTime(tak[8]),
                                  //ScanDate = tak[8] == null ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[8]) < searchModel.StartDate.Value.Date ? searchModel.StartDate.Value.Date : (Convert.ToDateTime(tak[8]) > searchModel.EndDate.Value.Date ? searchModel.EndDate.Value.Date : Convert.ToDateTime(tak[8]))),
                                  ScanCount = (int?)tak[9],
                                  Container1 = (string)tak[10],
                                  ContainerDescription = (string)tak[11],
                              }).ToList();
                    #endregion

                    #region

                    //var listDays = opList.Select(o => o.ScanDate).Distinct().OrderBy(o => o.Value).ToList();
                    //for (int i = 0; i < listDays.Count(); i++)
                    //{
                    //    columns.Add(new GridColumnSettings
                    //    {
                    //        Member = "RowCellList[" + i + "].ScanCount",
                    //        Title = listDays[i].Value.ToShortDateString(),
                    //        Sortable = false
                    //    });

                    //}
                    var minDate1 = searchModel.StartDate.Value;
                    var maxDate1 = searchModel.EndDate.Value;
                    int i = 0;
                    while (minDate1 <= maxDate1)
                    {
                        columns.Add(new GridColumnSettings
                        {
                            Member = "RowCellList[" + i + "].ScanCount",
                            Title = minDate1.ToShortDateString(),
                            Sortable = false
                        });
                        minDate1 = minDate1.AddDays(1);
                    }

                    IList<KanbanCard> kanbanCardList = new List<KanbanCard>();
                    #region    body
                    var groups = opList.GroupBy(g => new { g.CardNo, g.OpRefSequence, g.Item, g.ReferenceItemCode, g.ItemDescription, g.OpRef, g.Qty, g.Container1, g.ContainerDescription }).ToList();
                    foreach (var g in groups)
                    {
                        KanbanCard k = new KanbanCard();
                        k.CardNo = g.Key.CardNo;
                        k.OpRefSequence = g.Key.OpRefSequence;
                        k.Item = g.Key.Item;
                        k.ReferenceItemCode = g.Key.ReferenceItemCode;
                        k.ItemDescription = g.Key.ItemDescription;
                        k.OpRef = g.Key.OpRef;
                        k.Qty = g.Key.Qty;
                        k.Container1 = g.Key.Container1;
                        k.ContainerDescription = g.Key.ContainerDescription;
                        List<com.Sconit.Entity.KB.RowCellK> rowList = new List<com.Sconit.Entity.KB.RowCellK>();
                        var minDate = searchModel.StartDate.Value;
                        var maxDate = searchModel.EndDate.Value;
                        while (minDate <= maxDate)
                        {
                            com.Sconit.Entity.KB.RowCellK rc = new com.Sconit.Entity.KB.RowCellK();
                            var currentKBs = g.Where(c => c.ScanDate == minDate);
                            var currentKB = currentKBs != null && currentKBs.Count() > 0 ? currentKBs.First() : new KanbanCard();
                            rc.ScanCount = currentKB.ScanCount.HasValue ? currentKB.ScanCount.Value : 0;
                            rowList.Add(rc);
                            minDate = minDate.AddDays(1);
                        }
                        k.RowCellList = rowList;
                        kanbanCardList.Add(k);
                    }

                    #endregion

                    ViewData["columns"] = columns.ToArray();
                    return PartialView(kanbanCardList);

                    #endregion
                }
            }
            ViewData["columns"] = columns.ToArray();
            return Json(null);
            //return PartialView(new List<KanbanCard>());

        }
예제 #3
0
        private string PrepareTurnoverRateSearchStatement(KanbanCardSearchModel searchModel)
        {
            string sql = @"select c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,c.Qty,c.opref,CONVERT(varchar(10), s.CreateDate, 121) as '扫描日期',case when CONVERT(varchar(10), s.CreateDate, 121) is null then 0 else  COUNT(*) end as '扫描次数',det.Container,det.ContainerDesc
                         from KB_KanbanCard c 
                         left join SCM_FlowDet det on det.Flow=c.Flow and det.Item=c.Item
                         left join KB_KanbanScan s on c.CardNo=s.CardNo
                         where 1=1 ";
            if (!string.IsNullOrWhiteSpace(searchModel.Item))
            {
                sql += string.Format(" and c.Item='{0}' ", searchModel.Item);
            }
            if (!string.IsNullOrWhiteSpace(searchModel.BinTo))
            {
                sql += string.Format(" and c.opref='{0}' ", searchModel.BinTo);
            }
            if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
            {
                sql += string.Format(" and c.CardNo='{0}' ", searchModel.CardNo);
            }
            if (!string.IsNullOrWhiteSpace(searchModel.OpRefSequence))
            {
                sql += string.Format(" and c.OpRefSeq='{0}' ", searchModel.OpRefSequence);
            }


            //sql += string.Format(" and ((s.CreateDate >= '{0}' and s.CreateDate <='{1}') or s.CreateDate is null) ", searchModel.StartDate,searchModel.EndDate);
           
                //sql += string.Format("  ", );

            return sql + " group by c.CardNo,c.OpRefSeq,c.Item,c.RefItemCode,c.ItemDesc,CONVERT(varchar(10), s.CreateDate, 121),c.Qty,c.opref,det.Container,det.ContainerDesc ";
        }
예제 #4
0
        public void ExportKanbanCard(KanbanCardSearchModel searchModel)
        {
            StringBuilder sb = new StringBuilder();
            IList<object> param = new List<object>();
            string selectStatement = "  select k from KanbanCard as k where 1=1  ";
            SecurityHelper.AddRegionPermissionStatement(ref selectStatement, "k", "Region");
            sb.Append(selectStatement);
            if (!string.IsNullOrEmpty(searchModel.Region))
            {
                // System.Text.StringBuilder sb = new System.Text.StringBuilder();
                if (!string.IsNullOrEmpty(searchModel.Region))
                {
                    sb.Append(" and k.Region in (");
                    string[] regions = searchModel.Region.Split(',');
                    for (int ir = 0; ir < regions.Length; ir++)
                    {
                        sb.Append("?,");
                        param.Add(regions[ir]);
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                    sb.Append(")");
                }

            }

            if (!string.IsNullOrWhiteSpace(searchModel.Supplier))
            {
                sb.Append(" and Supplier = ?");
                param.Add(searchModel.Supplier);
            }
            if (!string.IsNullOrWhiteSpace(searchModel.Item))
            {
                sb.Append(" and Item = ?");
                param.Add(searchModel.Item);
            }
            if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
            {
                sb.Append(" and CardNo = ?");
                param.Add(searchModel.CardNo);
            }
            if (searchModel.KBCalc != null)
            {
                sb.Append(" and KBCalc = ?");
                param.Add(searchModel.KBCalc);
            }
            if (searchModel.FreezeDate != null)
            {
                sb.Append(" and FreezeDate <=?");
                param.Add(searchModel.FreezeDate);
            }
            if (searchModel.StartDate != null & searchModel.EndDate != null)
            {
                sb.Append(" and CreateDate between ? and ?");
                param.Add(searchModel.StartDate);
                param.Add(searchModel.EndDate);
            }
            else if (searchModel.StartDate != null & searchModel.EndDate == null)
            {
                sb.Append(" and CreateDate >= ?");
                param.Add(searchModel.StartDate);
            }
            else if (searchModel.StartDate == null & searchModel.EndDate != null)
            {
                sb.Append(" and CreateDate <= ?");
                param.Add(searchModel.EndDate);
            }
            IList<KanbanCard> kanbanCardList = this.genericMgr.FindAll<KanbanCard>(sb.ToString(), param.ToArray());
            ExportToXLS<KanbanCard>("ExportKanbanCard", "xls", kanbanCardList);

        }
예제 #5
0
 public ActionResult TurnoverRateIndex(GridCommand command, KanbanCardSearchModel searchModel)
 {
     return View();
 }
예제 #6
0
        private SearchStatementModel PrepareSearchStatement(GridCommand command, KanbanCardSearchModel searchModel)
        {
            string whereStatement = " where 1=1 ";

            IList<object> param = new List<object>();
            //区域权限
            SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "k", "Region");
            if (!string.IsNullOrEmpty(searchModel.Region))
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                if (!string.IsNullOrEmpty(searchModel.Region))
                {
                    sb.Append(" and k.Region in (");
                    string[] regions = searchModel.Region.Split(',');
                    for (int ir = 0; ir < regions.Length; ir++)
                    {
                        sb.Append("?,");
                        param.Add(regions[ir]);
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                    whereStatement += sb.ToString() + ")";
                }

            }
            //HqlStatementHelper.AddEqStatement("Region", searchModel.Region, "k", ref whereStatement, param);
            HqlStatementHelper.AddEqStatement("Supplier", searchModel.Supplier, "k", ref whereStatement, param);
            HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "k", ref whereStatement, param);
            HqlStatementHelper.AddEqStatement("CardNo", searchModel.CardNo, "k", ref whereStatement, param);
            HqlStatementHelper.AddLikeStatement("OpRef", searchModel.BinTo, HqlStatementHelper.LikeMatchMode.Start, "k", ref whereStatement, param);
            //if (searchModel.KitCount != null && searchModel.KitCount.Value > 1)
            //{
            //    HqlStatementHelper.AddEqStatement("KitCount", searchModel.KitCount.Value, "k", ref whereStatement, param);
            //}
            // HqlStatementHelper.AddEqStatement("NeedReprint", searchModel.NeedReprint, "k", ref whereStatement, param);
            HqlStatementHelper.AddEqStatement("OpRefSequence", searchModel.OpRefSequence, "k", ref whereStatement, param);

            if (searchModel.StartDate != null & searchModel.EndDate != null)
            {
                HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "k", ref whereStatement, param);
            }
            else if (searchModel.StartDate != null & searchModel.EndDate == null)
            {
                HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "k", ref whereStatement, param);
            }
            else if (searchModel.StartDate == null & searchModel.EndDate != null)
            {
                HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "k", ref whereStatement, param);
            }

            string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
            TempData["SortStatement"] = sortingStatement;
            SearchStatementModel searchStatementModel = new SearchStatementModel();
            searchStatementModel.SelectCountStatement = selectCountStatement;
            searchStatementModel.SelectStatement = selectStatement;
            searchStatementModel.WhereStatement = whereStatement;
            searchStatementModel.SortingStatement = sortingStatement;
            searchStatementModel.Parameters = param.ToArray<object>();

            return searchStatementModel;
        }
예제 #7
0
        public string PrintcardNos(string cardNos, KanbanCardSearchModel searchModel)
        {
            IList<object> data = new List<object>();
            IList<KanbanCard> kanbanCardList = null;
            string sortStatement = TempData["SortStatement"] != null ? (string)TempData["SortStatement"] : " order by CreateDate asc ";
            TempData["SortStatement"] = sortStatement;
            try
            {
                if (!string.IsNullOrEmpty(cardNos))
                {
                    //根据查询条件打印
                    if (cardNos.Equals("ALL", StringComparison.OrdinalIgnoreCase))
                    {
                        string whereStatement = " where 1=1 ";

                        IList<object> param = new List<object>();
                        //区域权限
                        SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "k", "Region");
                        if (!string.IsNullOrEmpty(searchModel.Region))
                        {
                            System.Text.StringBuilder sb = new System.Text.StringBuilder();
                            if (!string.IsNullOrEmpty(searchModel.Region))
                            {
                                sb.Append(" and k.Region in (");
                                string[] regions = searchModel.Region.Split(',');
                                for (int ir = 0; ir < regions.Length; ir++)
                                {
                                    sb.Append("?,");
                                    param.Add(regions[ir]);
                                }
                                sb = sb.Remove(sb.Length - 1, 1);
                                whereStatement += sb.ToString() + ")";
                            }
                        }
                        HqlStatementHelper.AddEqStatement("Supplier", searchModel.Supplier, "k", ref whereStatement, param);
                        HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "k", ref whereStatement, param);
                        HqlStatementHelper.AddEqStatement("CardNo", searchModel.CardNo, "k", ref whereStatement, param);
                        HqlStatementHelper.AddLikeStatement("OpRef", searchModel.BinTo, HqlStatementHelper.LikeMatchMode.Start, "k", ref whereStatement, param);
                        HqlStatementHelper.AddEqStatement("OpRefSequence", searchModel.OpRefSequence, "k", ref whereStatement, param);

                        if (searchModel.StartDate != null & searchModel.EndDate != null)
                        {
                            HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "k", ref whereStatement, param);
                        }
                        else if (searchModel.StartDate != null & searchModel.EndDate == null)
                        {
                            HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "k", ref whereStatement, param);
                        }
                        else if (searchModel.StartDate == null & searchModel.EndDate != null)
                        {
                            HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "k", ref whereStatement, param);
                        }

                        kanbanCardList = this.genericMgr.FindAll<KanbanCard>("from KanbanCard k " + whereStatement + sortStatement, param.ToArray());
                    }
                    //根据选择的行数打印
                    else
                    {
                        string[] array = cardNos.Split(',');

                        string selectStatement = string.Empty;
                        IList<object> selectPartyPara = new List<object>();
                        foreach (var para in array)
                        {
                            if (selectStatement == string.Empty)
                            {
                                selectStatement = " from KanbanCard where CardNo in (?";
                            }
                            else
                            {
                                selectStatement += ",?";
                            }
                            selectPartyPara.Add(para);
                        }
                        selectStatement += ")";

                        kanbanCardList = genericMgr.FindAll<KanbanCard>(selectStatement + sortStatement, selectPartyPara.ToArray());
                    }
                }
                else
                {
                    throw new BusinessException("看板单号不能为空.");
                }

                string printUrl = string.Empty;
                if (kanbanCardList.Count() > 0)
                {
                    if (printUrl == string.Empty)
                    {
                        IList<PrintKanBanCard> printList = Mapper.Map<IList<KanbanCard>, IList<PrintKanBanCard>>(kanbanCardList);
                        data.Add(printList);
                        printUrl = reportGen.WriteToFile("KanBanCard.xls", data);
                    }
                }
                return printUrl;
            }
            catch (BusinessException ex)
            {
                SaveBusinessExceptionMessage(ex);


            }

            return string.Empty;
        }
예제 #8
0
        public ActionResult _AjaxList(GridCommand command, KanbanCardSearchModel searchModel)
        {
            if (!string.IsNullOrWhiteSpace(searchModel.CardNo))
            {
                if (searchModel.CardNo.Length > 2 && searchModel.CardNo.Substring(0, 2).ToUpper() == "$K")
                {
                    searchModel.CardNo = searchModel.CardNo.Substring(2, searchModel.CardNo.Length - 2);
                }
            }
            SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
            //GridModel<KanbanCard> gridModelKanbanCardList = GetAjaxPageData<KanbanCard>(searchStatementModel, command);

            //if (searchModel.FreezeDate!=null && searchModel.FreezeDate.HasValue && gridModelKanbanCardList.Data != null && gridModelKanbanCardList.Data.Count() > 0)
            //{
            //    foreach (KanbanCard kanbanCard in gridModelKanbanCardList.Data)
            //    {
            //        kanbanCard.IsFreeze = kanbanCard.FreezeDate <= searchModel.FreezeDate;
            //    }
            //}
            return PartialView(GetAjaxPageData<KanbanCard>(searchStatementModel, command));
        }
예제 #9
0
        public ActionResult List(GridCommand command, KanbanCardSearchModel searchModel)
        {

            SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
            ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
            return View();
        }
예제 #10
0
 public ActionResult _AjaxList(GridCommand command, KanbanCardSearchModel searchModel)
 {
     SearchStatementModel searchStatementModel = this.PrepareSearchStatement(command, searchModel);
     return PartialView(GetAjaxPageData<KanbanCard>(searchStatementModel, command));
 }
예제 #11
0
        private SearchStatementModel PrepareSearchStatement(GridCommand command, KanbanCardSearchModel searchModel)
        {
            string whereStatement = string.Format(" where k.IsLost={0} ",true);

            IList<object> param = new List<object>();
            //区域权限
            SecurityHelper.AddRegionPermissionStatement(ref whereStatement, "k", "Region");
            if (!string.IsNullOrEmpty(searchModel.Region))
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                if (!string.IsNullOrEmpty(searchModel.Region))
                {
                    sb.Append(" and k.Region in (");
                    string[] regions = searchModel.Region.Split(',');
                    for (int ir = 0; ir < regions.Length; ir++)
                    {
                        sb.Append("?,");
                        param.Add(regions[ir]);
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                    whereStatement += sb.ToString() + ")";
                }

            }
            HqlStatementHelper.AddLikeStatement("BinTo", searchModel.BinTo, HqlStatementHelper.LikeMatchMode.Start, "k", ref whereStatement, param);
            string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);

            SearchStatementModel searchStatementModel = new SearchStatementModel();
            searchStatementModel.SelectCountStatement = selectCountStatement;
            searchStatementModel.SelectStatement = selectStatement;
            searchStatementModel.WhereStatement = whereStatement;
            searchStatementModel.SortingStatement = sortingStatement;
            searchStatementModel.Parameters = param.ToArray<object>();

            return searchStatementModel;
        }
예제 #12
0
        public void ExportLostView(KanbanCardSearchModel searchModel)
        {
            
            IList<object> param = new List<object>();
            string hql =string.Format( "  select k from KanbanCard as k where IsLost={0} ",true);
            SecurityHelper.AddRegionPermissionStatement(ref selectStatement, "k", "Region");
            if (!string.IsNullOrEmpty(searchModel.Region))
            {
                // System.Text.StringBuilder sb = new System.Text.StringBuilder();
                if (!string.IsNullOrEmpty(searchModel.Region))
                {
                    hql+=" and k.Region in (";
                    string[] regions = searchModel.Region.Split(',');
                    for (int ir = 0; ir < regions.Length; ir++)
                    {
                        hql+="?,";
                        param.Add(regions[ir]);
                    }
                    hql = hql.Substring(0, hql.Length - 1) + ")";
                }

            }
            if (!string.IsNullOrWhiteSpace(searchModel.BinTo))
            {
                hql += " and k.LocBin=? ";
                param.Add(searchModel.BinTo);
            }

            IList<KanbanCard> kanbanCardList = this.genericMgr.FindAll<KanbanCard>(hql, param.ToArray());
            ExportToXLS<KanbanCard>("ExportKanbanCard", "xls", kanbanCardList);

        }