public string getJSONData(int IdDeclare, string flt, int N, int LenP, string sort, string order, string Query, Dictionary <string, object> Params = null, string MainSQL = null)
{
if (string.IsNullOrEmpty(MainSQL))
{
MainSQL = getSQL(IdDeclare, Query);
}
if (!string.IsNullOrEmpty(sort) & !string.IsNullOrEmpty(order))
{
//Добавляем свою сортировку
int n = MainSQL.ToUpper().IndexOf("ORDER BY");
if (n == -1)
{
throw new Exception("not find ORDER BY section!");
}
MainSQL = MainSQL.Substring(0, n) + " order by " + DBClient.sortCreate(sort, order);
}
Dictionary <string, string> formats = new Dictionary <string, string>();
string sql = "select D.*, P.ParamValue from t_rpDeclare D(nolock) inner join t_sysParams P(nolock) on 'GridFind' + D.DecName = P.ParamName where IdDeclare = @IdDeclare";
DataTable decTab = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(sql, DBClient.CnStr);
da.SelectCommand.Parameters.AddWithValue("@IdDeclare", IdDeclare);
da.Fill(decTab);
string s = decTab.Rows[0]["ParamValue"].ToString();
XmlDocument xm = new XmlDocument();
XmlElement xRoot;
xm.LoadXml(s);
xRoot = xm.DocumentElement;
foreach (XmlNode xNod in xRoot.SelectNodes("COLUMN"))
{
XmlElement xCol = (XmlElement)xNod;
string FName = xCol.Attributes["FieldName"].Value;
string DisplayFormat = xCol.Attributes["DisplayFormat"].Value;
if (!String.IsNullOrEmpty(DisplayFormat))
{
formats.Add(FName, DisplayFormat);
}
}
//Итоги 03.08.2016
string SumFields = xRoot.GetAttribute("SumFields");
//string FROZENCOLS = xRoot.GetAttribute("FROZENCOLS");
string LabelText = xRoot.GetAttribute("LabelText");
string LabelField = xRoot.GetAttribute("LabelField");
return(getJSONData(MainSQL, flt, N, LenP, formats, Params, SumFields, LabelText, LabelField));
}
/*
* public string getJSONRow(DataRow rw)
* {
* DataTable res = rw.Table;
* string s = "{";
* for (int j = 0; j < res.Columns.Count; j++)
* {
* string fs = @"""" + res.Columns[j].ColumnName + @""":";
* string val = DBClient.strEscape(rw[j].ToString());
*
* if (rw[j] != DBNull.Value)
* {
* if (res.Columns[j].DataType == typeof(System.Double))
*
* val = ((Double)rw[j]).ToString("#,##0.00");
*
* if (res.Columns[j].DataType == typeof(System.DateTime))
* val = ((DateTime)rw[j]).ToString("dd.MM.yyyy HH:mm");
* }
*
*
* fs = fs + @"""" + val + @"""";
*
*
* if (j == res.Columns.Count - 1)
* s = s + fs + "}";
* else
* s = s + fs + ",";
* }
* return s;
* }
*/
public string getJSONRow(DataRow rw, Dictionary <string, string> formats)
{
if (formats == null)
{
formats = new Dictionary <string, string>();
}
DataTable res = rw.Table;
string s = "{";
for (int j = 0; j < res.Columns.Count; j++)
{
string fs = @"""" + res.Columns[j].ColumnName + @""":";
string val = DBClient.strEscape(rw[j].ToString());
//01.08.2016 картинка
if (res.Columns[j].ColumnName == "image_bmp" & !String.IsNullOrEmpty(val))
{
val = @"<img src='data:image/gif;base64," + val + @"'/>";
}
//05.10.2016
if (res.Columns[j].ColumnName == "Color")
{
if (rw[j] != DBNull.Value)
{
Color cl = Color.FromArgb((int)rw[j]);
val = "#" + ((int)cl.R).ToString("X") + ((int)cl.G).ToString("X") + ((int)cl.B).ToString("X");
}
else
{
val = "#FFFFFF";
}
}
if (rw[j] != DBNull.Value)
{
if (res.Columns[j].DataType == typeof(System.Double))
{
if (formats.ContainsKey(res.Columns[j].ColumnName))
{
val = ((Double)rw[j]).ToString(formats[res.Columns[j].ColumnName]);
}
else
{
val = ((Double)rw[j]).ToString("#,##0.00");
}
}
if (res.Columns[j].DataType == typeof(System.DateTime))
{
if (formats.ContainsKey(res.Columns[j].ColumnName))
{
val = ((DateTime)rw[j]).ToString(formats[res.Columns[j].ColumnName]);
}
else
{
val = ((DateTime)rw[j]).ToString("dd.MM.yyyy");
}
}
}
fs = fs + @"""" + val + @"""";
if (j == res.Columns.Count - 1)
{
s = s + fs + "}";
}
else
{
s = s + fs + ",";
}
}
return(s);
}