コード例 #1
0
        /// <summary>
        /// get all rows for export excel
        /// </summary>
        /// <param name="crud"></param>
        /// <param name="cond"></param>
        /// <param name="exportSqlFirst">true:先考慮ExcelSql, false: 只考慮Sql</param>
        /// <returns></returns>
        public JArray GetAllRows(ReadDto crud, JObject cond, bool exportSqlFirst)
        {
            //convert sql to model
            var sql = (exportSqlFirst && !string.IsNullOrEmpty(crud.ExportSql))
                ? crud.ExportSql : crud.ReadSql;
            var sqlModel = _Sql.SqlToDto(sql, crud.UseSquare);

            //if (sqlModel0 == null)
            //    return null;

            //prepare sql where, also set _sqlArgs
            //var where = GetWhere(crud.TableAlias, crud.Items, crud.FindCols);
            //var sqlModel = sqlModel0.Value;
            var where = GetWhere(crud, cond);
            if (where != "")
            {
                sqlModel.Where = (sqlModel.Where == "") ? "Where " + where : sqlModel.Where + " And " + where;
            }

            //get rows count if need
            //JArray rows = null;
            using (var db = GetDb())
            {
                //SqlArgModel dbModel = null;

                //set dbModel, and check crud.ColList
                var flag = false;
                if (sqlModel.Select.Contains("DISTINCT") || sqlModel.Select.Contains("distinct"))
                {
                    flag            = true;
                    sqlModel.Select = sqlModel.Select.Replace("DISTINCT", "").Replace("distinct", "");
                }

                //get data
                sql = "Select " + (flag ? "distinct " : " ") +
                      "Top 3000 " +
                      sqlModel.Select + " " +
                      sqlModel.From + " " +
                      sqlModel.Where + " " +
                      sqlModel.Group + " " +
                      sqlModel.Order;
                return(db.GetJsons(sql, _sqlArgs));
            }
        }
コード例 #2
0
 /// <summary>
 /// crud read export to Excel file
 /// </summary>
 /// <param name="ctrl"></param>
 /// <param name="docx"></param>
 /// <param name="readDto"></param>
 /// <param name="findJson"></param>
 /// <param name="srcRowNo"></param>
 /// <param name="dbStr"></param>
 public static async Task DocxByReadAsync(string ctrl, SpreadsheetDocument docx, ReadDto readDto,
                                          JObject findJson, int srcRowNo, string dbStr = "")
 {
     DocxByRows(await new CrudRead(dbStr).GetExportRowsAsync(ctrl, readDto, findJson), docx, srcRowNo);
 }
コード例 #3
0
        /// <summary>
        /// get page rows for dataTables(json)
        /// </summary>
        /// <param name="crud"></param>
        /// <param name="dt"></param>
        /// <param name="findJson">if not null, will query by this and not from dtIn.findJson</param>
        /// <returns>jquery dataTables object</returns>
        public JObject GetPage(ReadDto crud, DtDto dt, JObject findJson = null)
        {
            //adjust
            if (dt.length < 10)
            {
                dt.length = 10;
            }

            //set instance variables from input args
            _dtIn = dt;
            if (findJson == null)
            {
                findJson = string.IsNullOrEmpty(dt.findJson) ? null : _Json.StrToJson(dt.findJson);
            }

            //convert sql to model
            var sqlDto = _Sql.SqlToDto(crud.ReadSql, crud.UseSquare);

            if (sqlDto == null)
            {
                return(null);
            }

            //prepare sql where & set sql args by user input condition
            //var sqlModel = sqlModel0.Value;
            var where = GetWhere(crud, findJson, _dtIn.search.value);
            if (where != "")
            {
                sqlDto.Where = (sqlDto.Where == "")
                    ? "Where " + where : sqlDto.Where + " And " + where;
            }

            #region get rows count if need
            JArray rows     = null;
            var    db       = GetDb();
            var    rowCount = dt.recordsFiltered;
            var    sql      = "";
            var    group    = (sqlDto.Group == "") ? "" : " " + sqlDto.Group; //remove last space
            if (rowCount < 0)
            {
                sql = "Select Count(*) as _count " +
                      sqlDto.From + " " +
                      sqlDto.Where +
                      group;
                var row = db.GetJson("\n" + sql, _sqlArgs); //for log 斷行
                if (row == null)
                {
                    rowCount = 0;
                    goto lab_exit;
                }

                //case of ok
                rowCount = Convert.ToInt32(row["_count"]);
            }
            #endregion

            //sorting
            var orderColumn = (dt.order == null || dt.order.Count == 0)
                ? -1 : dt.order[0].column;
            if (orderColumn >= 0)
            {
                sqlDto.Order = "Order By " +
                               sqlDto.Columns[orderColumn].Trim() +
                               (dt.order[0].dir == OrderTypeEnum.Asc ? "" : " Desc");
            }

            //set dbModel, consider crud.ColList
            sql = sqlDto.Select + " " +
                  sqlDto.From + " " +
                  sqlDto.Where + group;

            //get data
            sql  = string.Format(_Fun.ReadPageSql, sql, sqlDto.Order, dt.start, dt.length).Replace("  ", " ");  //2012
            rows = db.GetJsons(sql, _sqlArgs);

lab_exit:
            var result = JObject.FromObject(new
            {
                dt.draw,
                data            = rows,
                recordsTotal    = 0,
                recordsFiltered = rowCount,
            });

            //close db & return
            db.Dispose();
            return(result);
        }
コード例 #4
0
        /// <summary>
        /// TODO: change to return SqlArgModel !!
        /// can directly call(no need to set Crud.Sql !!), return where string
        /// set sql args same time
        /// date field must change format
        /// </summary>
        /// <param name="cond">query condition</param>
        /// <param name="inputSearch">quick search string</param>
        /// <returns></returns>
        public string GetWhere(ReadDto crud, JObject cond, string inputSearch = "")
        {
            #region variables
            var groupLen = (crud.OrGroups == null || crud.OrGroups.Count == 0) ? 0 : crud.OrGroups.Count;
            var orWheres = new string[groupLen == 0 ? 1 : groupLen];
            var where = "";
            var thisWhere = "";
            var and       = "";
            var okDates   = new List <string>();  //date field be done(date always has start/end)
            var items     = crud.Items;
            #endregion

            #region 1.where add condition
            if (items != null && items.Length > 0 && cond != null)
            {
                var table = string.IsNullOrEmpty(crud.TableAs) ? "" : (crud.TableAs + ".");
                foreach (var prop in cond)
                {
                    //skip if empty
                    object value = prop.Value;
                    //var checkvalues = value.ToString().Replace(" ", "").Split(',');
                    if (_Str.IsEmpty(value))
                    {
                        continue;
                    }

                    #region if query field not existed(and field name is not underline), skip & log
                    var item = items.Where(a => a.Fid == prop.Key).FirstOrDefault();
                    if (item == null)
                    {
                        //1.underline field(reserve field), 2.done date field
                        var key = prop.Key;
                        var len = key.Length;
                        //field name not underline
                        if (key.Substring(0, 1) == "_")
                        {
                            continue;
                        }

                        //field name tail is 2, for date field
                        if (key.Substring(len - 1, 1) == "2")
                        {
                            //skip if date field is done
                            var key2 = key.Substring(0, len - 1);
                            if (okDates.Contains(key2))
                            {
                                continue;
                            }

                            //find item
                            item = items.Where(a => a.Fid == key2).FirstOrDefault();
                            if (item == null)
                            {
                                _Log.Error("CrudRead.cs GetWhere() failed: no Fid = " + key2);
                                continue;
                            }

                            //continue
                        }
                        else
                        {
                            //else case, skip & log error
                            _Log.Error("CrudRead.cs GetWhere() failed: no Fid = " + prop.Key);
                            continue;
                        }
                    }
                    #endregion

                    /*
                     * //check empty ??
                     * var checkFlag = 0;
                     * var checkValue = value.ToString().Replace(" ", "").Split(',');
                     * for (var i = 0; i < checkValue.Length; i++)
                     * {
                     *  if (checkValue[i] == "")
                     *  {
                     *      checkFlag = 1;
                     *      break;
                     *  }
                     *  else
                     *      checkFlag = 0;
                     * }
                     * if (checkFlag == 1)
                     *  continue;
                     */

                    #region set where & add argument
                    //var item = item0.Value;
                    var col = _Str.IsEmpty(item.Col) ? (table + item.Fid) : item.Col;
                    if (item.Op == ItemOpEstr.In)
                    {
                        //"in" has different args type !!
                        //change carrier sign to comma for TextArea field
                        var value2 = value.ToString().Replace(" ", "").Replace("\n", ",");
                        var values = value2.Split(',');
                        var names  = new List <string>();
                        for (var i = 0; i < values.Length; i++)
                        {
                            if (_Str.IsEmpty(values[i]))
                            {
                                continue;
                            }

                            var fid = item.Fid + i;
                            AddArg(fid, values[i]);
                            names.Add("@" + fid);
                        }
                        if (names.Count == 0)
                        {
                            continue;
                        }
                        thisWhere = col + " in (" + string.Join(",", names) + ")";
                    }
                    else if (item.Op == ItemOpEstr.Like2)
                    {
                        AddArg(item.Fid, "%" + value.ToString() + "%");
                        thisWhere = col + " Like @" + item.Fid;
                    }
                    else if (item.Op == ItemOpEstr.LikeList)
                    {
                        var itemWhere = "";
                        var or        = "";
                        var values    = value.ToString().Replace(" ", "").Split(',');
                        for (var i = 0; i < values.Length; i++)
                        {
                            if (_Str.IsEmpty(values[i]))
                            {
                                continue;
                            }

                            var fid = item.Fid + i;
                            itemWhere += or + col + " Like @" + fid;
                            or         = " Or ";
                            AddArg(fid, values[i] + "%");
                        }
                        if (itemWhere == "")
                        {
                            continue;
                        }
                        thisWhere = "(" + itemWhere + ")";
                    }
                    else if (item.Op == ItemOpEstr.LikeCols || item.Op == ItemOpEstr.Like2Cols)
                    {
                        var pre       = (item.Op == ItemOpEstr.Like2Cols) ? "%" : "";
                        var itemWhere = "";
                        var or        = "";
                        var cols      = col.Replace(" ", "").Split(',');
                        foreach (var col2 in cols)
                        {
                            if (_Str.IsEmpty(col2))
                            {
                                continue;
                            }

                            itemWhere += or + col2 + " Like @" + item.Fid;
                            or         = " Or ";
                        }
                        if (itemWhere == "")
                        {
                            continue;
                        }

                        AddArg(item.Fid, pre + value + "%");
                        thisWhere = "(" + itemWhere + ")";
                    }
                    else if (item.Op == ItemOpEstr.Is)
                    {
                        if (value.ToString() != "1")
                        {
                            thisWhere = col + " is Null";
                        }
                        else if (value.ToString() != "0")
                        {
                            thisWhere = col + " is not Null";
                        }
                        else
                        {
                            thisWhere = col + " is " + value;
                        }
                    }
                    else if (item.Op == ItemOpEstr.IsNull)
                    {
                        if (value.ToString() != "1")
                        {
                            continue;
                        }
                        thisWhere = col + " is Null";
                    }
                    else if (item.Op == ItemOpEstr.NotNull)
                    {
                        if (value.ToString() != "1")
                        {
                            continue;
                        }
                        thisWhere = col + " is not Null";
                    }
                    else if (item.Op == ItemOpEstr.UserDefined)
                    {
                        //add () sign
                        thisWhere = "(" + col + " " + value.ToString() + ")";
                    }
                    else if (item.Op == ItemOpEstr.InRange)
                    {
                        var fid2    = item.Fid + "2";
                        var hasNum1 = !_Str.IsEmpty(cond[item.Fid]);
                        var hasNum2 = !_Str.IsEmpty(cond[fid2]);

                        okDates.Add(item.Fid);

                        if (hasNum1 && hasNum2)
                        {
                            thisWhere = string.Format("({0} >= @{1} And {0} <= @{2})", col, item.Fid, fid2);

                            //add arg
                            AddArg(item.Fid, value.ToString());
                            AddArg(fid2, cond[fid2].ToString());
                        }
                        else if (hasNum1)
                        {
                            thisWhere = string.Format("({0} >= @{1})", col, item.Fid);

                            //add arg
                            AddArg(item.Fid, value.ToString());
                        }
                        else if (hasNum2)
                        {
                            thisWhere = string.Format("({0} <= @{1})", col, fid2);

                            //add arg
                            AddArg(fid2, cond[fid2].ToString());
                        }
                    }

                    /*
                     * //date field(consider performance)
                     * else if (item.Type == EnumItemType.Date)
                     * {
                     *  var date1 = _Locale.FrontToBack(value.ToString());
                     *  var date2 = date1.AddDays(1);
                     *  where += and + GetWhereByDate(item, col, date1, date2);
                     * }
                     */
                    //2 date fields, fid tail must be 2 !!
                    else if (item.Type == QitemTypeEnum.Date)
                    {
                        //if date2 is done, just skip
                        var len = item.Fid.Length;
                        //var fid = item.Fid.Substring(0, len - 1);
                        //if (item.Fid.Substring(len - 1, 1) == "2" && okDates.Contains(fid))
                        //    continue;

                        //log date2 is done
                        okDates.Add(item.Fid);

                        //get where
                        var fid2     = item.Fid + "2";
                        var hasDate1 = !_Str.IsEmpty(cond[item.Fid]);
                        var hasDate2 = !_Str.IsEmpty(cond[fid2]);
                        if (hasDate1 && hasDate2)  //case of has 2nd field, then query start/end
                        {
                            //thisWhere = "(" + col + " Between @" + item.Fid + " And @" + fid2 + ")";
                            thisWhere = string.Format("({0} is Null Or {0} Between @{1} And @{2})", col, item.Fid, fid2);

                            //add arg
                            AddArg(item.Fid, _Str.GetLeft(_Date.CsToDt(value.ToString()).ToString(), " "));
                            AddArg(fid2, _Str.GetLeft(_Date.CsToDt(cond[fid2].ToString()).ToString(), " ") + " 23:59:59");
                        }
                        else if (hasDate1)  //has start date, then query this date after
                        {
                            //get where
                            //thisWhere = "(" + col + " >= @" + item.Fid + " And " + col + " < @" + fid2 + ")";
                            //thisWhere = "(" + col + " >= @" + item.Fid + ")";
                            thisWhere = string.Format("({0} is Null Or {0} >= @{1})", col, item.Fid);

                            //Datetime only read date part, type is string
                            var date1 = _Date.CsToDt(value.ToString());
                            AddArg(item.Fid, _Str.GetLeft(date1.ToString(), " "));
                            //AddArg(fid2, _Str.RemovePart(date1.AddDays(1).ToString(), " "));
                        }
                        else if (hasDate2)  //has end date, then query this date before
                        {
                            //get where
                            //thisWhere = "(" + col + " >= @" + item.Fid + " And " + col + " < @" + fid2 + ")";
                            //thisWhere = "(" + col + " <= @" + fid2 + ")";
                            thisWhere = string.Format("({0} is Null Or {0} <= @{1})", col, fid2);

                            //Datetime field only get date part, type is string
                            var date1 = _Date.CsToDt(value.ToString());
                            AddArg(fid2, _Str.GetLeft(date1.ToString(), " ") + " 23:59:59");
                        }
                    }
                    //2 date fields, if input one date then must in range, if input 2 dates, then must be intersection with start/end
                    else if (item.Type == QitemTypeEnum.Date2)
                    {
                        //if Date2 field not set "Other", log error & skip
                        //var item2 = new ReadItemCrud();
                        var fid2 = item.Fid + "2";
                        var col2 = item.Other;
                        if (String.IsNullOrEmpty(col2))
                        {
                            _Log.Error("CrudRead.cs GetWhere() failed: no Other field for Date2 (" + item.Fid + ")");
                            continue;
                        }

                        /*
                         * else
                         * {
                         *  //fid2 must existed須存在
                         *  item2 = (items.FirstOrDefault(a => a.Fid == fid2));
                         *  if (item2 == null)
                         *  {
                         *      _Log.Error("CrudRead.cs GetWhere() failed: Other field not existed (" + fid2 + ")");
                         *      continue;
                         *  }
                         * }
                         */

                        //log date2 is done
                        okDates.Add(item.Fid);

                        //get where
                        //var col2 = _Str.IsEmpty(item2.Col) ? (table + item2.Fid) : item2.Col;
                        var hasDate1 = !_Str.IsEmpty(cond[item.Fid]);
                        var hasDate2 = !_Str.IsEmpty(cond[fid2]);
                        if (hasDate1 && hasDate2)  //case of 2nd field, then query start/end
                        {
                            //thisWhere = "(" + col + " <= @" + fid2 + " And " + col2 + " >= @" + item.Fid + ")";
                            thisWhere = string.Format("(({0} is Null Or {0} <= @{1}) And ({2} is Null Or {2} >= @{3}))", col, fid2, col2, item.Fid);

                            //add arg
                            AddArg(fid2, _Str.GetLeft(_Date.CsToDt(cond[fid2].ToString()).ToString(), " ") + " 23:59:59");
                            AddArg(item.Fid, _Str.GetLeft(_Date.CsToDt(value.ToString()).ToString(), " "));
                        }
                        else if (hasDate1)  //only start date, then query bigger than this date
                        {
                            //get where
                            //"(" + col2 + " >= @" + item.Fid + ")";
                            thisWhere = string.Format("({0} is Null or {0} >= @{1})", col2, item.Fid);

                            //get date part of Datetime
                            var date1 = _Date.CsToDt(value.ToString());
                            AddArg(item.Fid, _Str.GetLeft(date1.ToString(), " "));
                            //AddArg(fid2, _Str.RemovePart(date1.AddDays(1).ToString(), " "));
                        }
                        else if (hasDate2)  //only end date, then query small than this date
                        {
                            //get where
                            //thisWhere = "(" + col + " <= @" + fid2 + ")";
                            thisWhere = string.Format("({0} is Null Or {0} <= @{1})", col, fid2);

                            //get date part of Datetime
                            var date1 = _Date.CsToDt(value.ToString());
                            AddArg(fid2, _Str.GetLeft(date1.ToString(), " ") + " 23:59:59");
                            //AddArg(fid2, _Str.RemovePart(date1.AddDays(1).ToString(), " "));
                        }
                    }
                    else if (item.Op == ItemOpEstr.Equal)
                    {
                        thisWhere = col + " = @" + item.Fid;
                        AddArg(item.Fid, value);
                    }
                    else if (item.Op == ItemOpEstr.Like)
                    {
                        thisWhere = col + " like @" + item.Fid;
                        AddArg(item.Fid, value + "%");
                    }
                    else if (item.Op == ItemOpEstr.NotLike)
                    {
                        thisWhere = col + " not like @" + item.Fid;
                        AddArg(item.Fid, value + "%");
                    }
                    else
                    {
                        //直接讓它產生查詢錯誤!!
                        thisWhere = col + " " + item.Op + " @" + item.Fid;
                        AddArg(item.Fid, value);
                    }
                    #endregion

                    #region consider OrGroups
                    var findGroup = -1;
                    if (groupLen > 0)
                    {
                        for (var i = 0; i < groupLen; i++)
                        {
                            var group = crud.OrGroups[i];
                            if (group.Contains(item.Fid))
                            {
                                findGroup = i;
                                break;
                            }
                        }
                    }
                    if (findGroup >= 0)
                    {
                        orWheres[findGroup] += thisWhere + " Or ";
                    }
                    else
                    {
                        where += and + thisWhere;
                        and    = " And ";
                    }
                    #endregion

                    //set others variables
                    //wheres.Add(col);
                } //foreach

                //add orWheres[] into where
                if (groupLen > 0)
                {
                    foreach (var orWhere in orWheres)
                    {
                        if (!_Str.IsEmpty(orWhere))
                        {
                            where += and + "(" + orWhere.Substring(0, orWhere.Length - 4) + ")";
                            and    = " And ";
                        }
                    }
                }
            }
            #endregion

            #region 2.where add quick search
            //var search = (_dtIn.search == null) ? "" : _dtIn.search.value;
            var search = inputSearch;
            if (!_Str.IsEmpty(search))
            {
                //by finding string
                var itemWhere = "";
                and = "";
                foreach (var col2 in crud.FindCols)
                {
                    //_stCache.readFids.Add(_list.findCols[i]);
                    itemWhere += and + col2 + " Like @" + _FindId;
                    and        = " Or ";

                    //add wheres[]
                    //wheres.Add(col2);
                }

                //add where
                itemWhere = "(" + itemWhere + ")";
                if (where == "")
                {
                    where = itemWhere;
                }
                else
                {
                    where += " And " + itemWhere;
                }

                //add argument
                AddArg(_FindId, "%" + search + "%");
            }
            #endregion

            return(where);
        }
コード例 #5
0
ファイル: _WebExcel.cs プロジェクト: bruce68tw/Base
 /// <summary>
 /// readDto to screen
 /// </summary>
 /// <param name="ctrl">controller name</param>
 /// <param name="readDto"></param>
 /// <param name="findJson"></param>
 /// <param name="fileName">output fileName</param>
 /// <param name="tplPath"></param>
 /// <param name="srcRowNo"></param>
 public static async Task ExportByReadAsync(string ctrl, ReadDto readDto, JObject findJson,
                                            string fileName, string tplPath, int srcRowNo)
 {
     await ExportByRowsAsync(await new CrudRead().GetExportRowsAsync(ctrl, readDto, findJson), fileName, tplPath, srcRowNo);
 }
コード例 #6
0
 /// <summary>
 /// 匯出Excel到畫面
 /// </summary>
 /// <param name="crud"></param>
 /// <param name="findJson"></param>
 /// <param name="sheetName"></param>
 /// <param name="headers"></param>
 /// <param name="cols"></param>
 /// <param name="dbStr"></param>
 public static void ScreenByCrud(ReadDto crud, JObject findJson, string fileStem, string template = "", int srcRowNo = 1, string dbStr = "")
 {
     ScreenByRows(new CrudRead(dbStr).GetAllRows(crud, findJson, true), fileStem, template, srcRowNo);
 }