public override void Write(int?chunkId, int?subChunkId, System.Data.IDataReader reader, string tableName)
{
if (reader == null)
{
return;
}
if (tableName.ToLower().StartsWith("_chunks"))
{
tableName = SourceSchema + "." + tableName;
}
else
{
tableName = DestinationSchema + "." + tableName;
}
var fields = new string[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
fields[i] = reader.GetName(i);
}
var q = $"COPY {tableName} ({string.Join(",", fields)}) from STDIN (FORMAT BINARY)";
using (var inStream = _connection.BeginBinaryImport(q))
{
while (reader.Read())
{
inStream.StartRow();
for (var i = 0; i < reader.FieldCount; i++)
{
var value = reader.GetValue(i);
if (value is null)
{
inStream.WriteNull();
}
else
{
var fd = GetFieldType(reader.GetFieldType(i), reader.GetName(i));
inStream.Write(value, fd);
}
}
}
inStream.Complete();
}
}
public override void Write(int?chunkId, int?subChunkId, System.Data.IDataReader reader, string tableName)
{
var folder = _cdmFolder;
folder = Path.Combine(folder, tableName);
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
if (chunkId.HasValue)
{
_fileName = Path.Combine(folder, chunkId.Value.ToString(CultureInfo.InvariantCulture));
}
var isFirstRow = true;
using (var writer = new StreamWriter(_fileName))
{
while (reader.Read())
{
if (isFirstRow)
{
var header = new StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
header.Append(reader.GetName(i));
if (i != reader.FieldCount - 1)
{
header.Append("\t");
}
}
writer.WriteLine(header);
isFirstRow = false;
}
var row = new StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
row.Append(reader.GetValue(i));
if (i != reader.FieldCount - 1)
{
row.Append("\t");
}
}
writer.WriteLine(row);
}
}
}
public static V Enum <V>(System.Data.IDataReader reader, MapPoint mapPoint, V defaultValue)
{
try
{
if (mapPoint.IsInResultSet() && !reader.IsDBNull(mapPoint.Index))
{
return((V)System.Enum.Parse(typeof(V), reader.GetValue(mapPoint.Index).ToString()));
}
}
catch (Exception)
{
throw;
}
return(defaultValue);
}
protected override LuUserType MapObject(System.Data.IDataReader r)
{
var entity = new LuUserType();
try
{
var ordinal = r.GetOrdinal("ID");
if (!r.IsDBNull(ordinal))
{
entity.Id = ((int)(r.GetValue(ordinal)));
}
}
catch (Exception) { }
try
{
var ordinal = r.GetOrdinal("USER_TYPE");
if (!r.IsDBNull(ordinal))
{
entity.UserType = ((string)(r.GetValue(ordinal)));
}
}
catch (Exception) { }
try
{
var ordinal = r.GetOrdinal("ACTIVE_FLAG");
if (!r.IsDBNull(ordinal))
{
entity.ActiveFlag = ((char)(r.GetValue(ordinal)));
}
}
catch (Exception) { }
return(entity);
}
private T Reader2Item(System.Data.IDataReader reader)
{
if (!reader.Read())
{
return(null);
}
var row = new T();
row.DataFilling = true;
for (var i = 0; i < reader.FieldCount; i++)
{
row[reader.GetName(i)] = reader.GetValue(i);
}
row.DataFilling = false;
return(row);
}
private static IEnumerable <string> ReaderToStringList(System.Data.IDataReader rd)
{
using (rd)
while (rd.Read())
{
var v = rd.GetValue(0);
if (v == null || v == DBNull.Value)
{
yield return(null);
}
else
{
yield return(v.ToString());
}
}
}
internal void CollectAll(System.Data.IDataReader dr)
{
var _pg = dr.GetValue(0);
var _str = _pg as string;
if (_str != null)
{
list.Add(_DatabaseCommon.FactoryFormABC_Input.InputConverter.CreateFromRecord(cms.UseBufferedReader(_str), 0, locator));
}
else
{
var _tr = _pg as System.IO.TextReader ?? new System.IO.StringReader(_pg.ToString());
try { list.Add(_DatabaseCommon.FactoryFormABC_Input.InputConverter.CreateFromRecord(cms.UseBufferedReader(_tr), 0, locator)); }
finally { _tr.Dispose(); }
}
}
internal void CollectOne(System.Data.IDataReader dr)
{
var _pg = dr.GetValue(0);
var _str = _pg as string;
if (_str != null)
{
one = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.CreateFromRecord(cms.UseBufferedReader(_str), 0, locator);
}
else
{
var _tr = _pg as System.IO.TextReader ?? new System.IO.StringReader(_pg.ToString());
try { one = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.CreateFromRecord(cms.UseBufferedReader(_tr), 0, locator); }
finally { _tr.Dispose(); }
}
}
public object Get(UserCsvListRequestTep request)
{
var context = TepWebContext.GetWebContext(PagePrivileges.AdminOnly);
var csv = new System.Text.StringBuilder();
var filename = DateTime.UtcNow.ToString("yy-MM-dd");
try {
context.Open();
context.LogInfo(this, string.Format("/user/csv GET"));
filename = context.GetConfigValue("SiteNameShort") + "-" + filename;
string sql = string.Format("SELECT usr.id,usr.username,usr.email,usr.firstname,usr.lastname,usr.level,usr.affiliation,usr.country,(SELECT log_time FROM usrsession WHERE id_usr=usr.id ORDER BY log_time ASC LIMIT 1) AS registration_date FROM usr;");
csv.Append("Id,Username,Email,FisrtName,LastName,Level,Affiliation,Country,Registration date" + Environment.NewLine);
System.Data.IDbConnection dbConnection = context.GetDbConnection();
System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection);
while (reader.Read())
{
csv.Append(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}{9}",
reader.GetValue(0) != DBNull.Value ? reader.GetString(0) : "",
reader.GetValue(1) != DBNull.Value ? reader.GetString(1) : "",
reader.GetValue(2) != DBNull.Value ? reader.GetString(2) : "",
reader.GetValue(3) != DBNull.Value ? reader.GetString(3) : "",
reader.GetValue(4) != DBNull.Value ? reader.GetString(4) : "",
reader.GetValue(5) != DBNull.Value ? reader.GetString(5) : "",
reader.GetValue(6) != DBNull.Value ? reader.GetString(6) : "",
reader.GetValue(7) != DBNull.Value ? reader.GetString(7) : "",
reader.GetValue(8) != DBNull.Value ? reader.GetString(8) : "",
Environment.NewLine));
}
context.CloseQueryResult(reader, dbConnection);
context.Close();
} catch (Exception e) {
context.LogError(this, e.Message, e);
context.Close();
throw e;
}
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.csv", filename));
return(csv.ToString());
}
/// <summary>
/// 从阅读取中获取当前数据行
/// </summary>
/// <param name="reader">数据库表游标</param>
/// <returns>用列名为key。值为value的结果集</returns>
private static Dictionary <string, object> GetDataByReader(System.Data.IDataReader reader)
{
var result = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
//读取列名，并最小化
var name = reader.GetName(i).ToLower();
if (!result.ContainsKey(name))
{
var value = reader.GetValue(i);
result.Add(name, value);
}
}
return(result);
}
} // End Function RemoveSingleLineSqlComments
public virtual T GetClass <T>(System.Data.IDbCommand cmd, T tThisClassInstance)
{
System.Type t = typeof(T);
lock (cmd)
{
using (System.Data.IDataReader idr = ExecuteReader(cmd))
{
lock (idr)
{
while (idr.Read())
{
int iFieldCount = idr.FieldCount;
Setter_t <T>[] mems = new Setter_t <T> [iFieldCount];
for (int i = 0; i < iFieldCount; ++i)
{
string strName = idr.GetName(i);
mems[i] = LinqHelper.GetSetter <T>(strName);
} // Next i
for (int i = 0; i < idr.FieldCount; ++i)
{
Setter_t <T> setter = mems[i];
if (setter != null)
{
object objVal = idr.GetValue(i);
setter(tThisClassInstance, objVal);
}
} // Next i
break;
} // Whend
idr.Close();
} // End Lock idr
} // End Using idr
} // End lock cmd
return(tThisClassInstance);
} // End Function GetClass
/// <summary>
/// Reperimento dei ruoli che hanno la visibilità sul nodo di titolario
/// </summary>
/// <param name="idNodoTitolario"></param>
/// <returns></returns>
public static string[] getRuoliNodoTitolario(string idNodoTitolario)
{
List <string> list = new List <string>();
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
string commandText = string.Format("SELECT g.group_id FROM security s, groups g WHERE s.personorgroup = g.system_id AND s.thing = {0}", idNodoTitolario);
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
while (reader.Read())
{
list.Add(reader.GetValue(0).ToString());
}
}
}
return(list.ToArray());
}
public static bool EnableHTML5SocketOption()
{
bool retVal = false;
using (DocsPaDB.DBProvider dbProvider = new DBProvider())
{
DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("GET_TIPO_COMPONENTI");
string commandText = queryDef.getSQL();
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
while (reader.Read())
{
if (reader.GetValue(reader.GetOrdinal("CHA_TIPO_COMPONENTI")).ToString() == "4")
{
retVal = true;
}
}
}
}
return(retVal);
}
public static Guid Guid(System.Data.IDataReader reader, MapPoint mapPoint, Guid defaultValue)
{
try
{
if (mapPoint.IsInResultSet() && !reader.IsDBNull(mapPoint.Index))
{
if (mapPoint.Type == System.Data.SqlDbType.UniqueIdentifier)
{
return(reader.GetGuid(mapPoint.Index));
}
else
{
return(new Guid(reader.GetValue(mapPoint.Index).ToString()));
}
}
}
catch (Exception)
{
throw;
}
return(defaultValue);
}
private List <T> Reader2List(System.Data.IDataReader reader)
{
string[] fields = new string[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
fields[i] = reader.GetName(i);
}
var list = new List <T>();
while (reader.Read())
{
var row = new T();
row.DataFilling = true;
for (var i = 0; i < reader.FieldCount; i++)
{
row[fields[i]] = reader.GetValue(i);
}
row.DataFilling = false;
list.Add(row);
}
return(list);
}
public List <KeyValuePair <string, int> > GetTopUsedServices(int count, List <int> userIds = null)
{
List <KeyValuePair <string, int> > result = new List <KeyValuePair <string, int> >();
string sql = string.Format("SELECT r.sname,SUM(r.snb) as nb FROM " +
"(SELECT service.name as sname, count(wpsjob.process) as snb FROM wpsjob INNER JOIN service ON service.identifier = wpsjob.process {1}group by wpsjob.process order by snb desc) as r " +
"GROUP BY r.sname " +
"ORDER BY nb desc limit {0};", count, userIds != null ? "AND wpsjob.id_usr IN (" + string.Join(",", userIds) + ") " : "");
System.Data.IDbConnection dbConnection = Context.GetDbConnection();
System.Data.IDataReader reader = Context.GetQueryResult(sql, dbConnection);
while (reader.Read())
{
if (reader.GetValue(0) != DBNull.Value)
{
var service = reader.GetString(0);
var nb = reader.GetInt32(1);
result.Insert(0, new KeyValuePair <string, int>(service, nb));
}
}
Context.CloseQueryResult(reader, dbConnection);
return(result);
}
/// <summary>
/// 从数据读取器中读取数据并填充到一个对象中
/// </summary>
/// <param name="ObjInstance">对象实例</param>
/// <param name="info">数据库绑定信息对象</param>
/// <param name="reader">数据读取器</param>
private int InnerReadValues(object ObjInstance, TableBindInfo info, System.Data.IDataReader reader)
{
// 检查参数
if (ObjInstance == null)
{
throw new ArgumentNullException("ObjectInstance");
}
if (info == null)
{
throw new ArgumentNullException("info");
}
if (reader == null)
{
throw new ArgumentNullException("reader");
}
int FieldCount = 0;
// 依次读取各个属性值
foreach (FieldBindInfo field in info.Fields)
{
// 绑定的属性是只读的
if (field.Property.CanWrite == false)
{
continue;
}
// 没有找到绑定的字段
if (field.FieldIndex < 0)
{
continue;
}
// 读取数据字段值
object v = reader.GetValue(field.FieldIndex);
v = field.FromDataBase(v);
// 设置对象属性值
field.Property.SetValue(ObjInstance, v, null);
FieldCount++;
} //foreach
return(FieldCount);
}
/// <summary>
/// Gets the new users.
/// </summary>
/// <returns>The new users.</returns>
/// <param name="context">Context.</param>
/// <param name="startdate">Startdate.</param>
/// <param name="enddate">Enddate.</param>
/// <param name="skipids">Skipids.</param>
/// <param name="allowedids">Allowedids.</param>
public static NameValueCollection GetNewUsers(IfyContext context, string startdate, string enddate, string skipids, string allowedids)
{
var ids = new NameValueCollection();
string sql = string.Format("SELECT DISTINCT usr.id,usr.level FROM usr {0}{1}{2};",
!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate) ? " WHERE id NOT IN (SELECT id_usr FROM usrsession WHERE usrsession.log_time < '" + startdate + "' ) AND id IN (SELECT id_usr FROM usrsession WHERE usrsession.log_time <= '" + enddate + "')" : "",
string.IsNullOrEmpty(skipids) ? "" : (string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate) ? " WHERE " : " AND ") + "id NOT IN (" + skipids + ")",
string.IsNullOrEmpty(allowedids) ? "" : ((string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate)) && string.IsNullOrEmpty(skipids) ? " WHERE " : " AND ") + " id IN (" + allowedids + ")");
System.Data.IDbConnection dbConnection = context.GetDbConnection();
System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection);
while (reader.Read())
{
if (reader.GetValue(0) != DBNull.Value)
{
var id = reader.GetInt32(0);
var level = reader.GetInt32(1);
ids.Set(id + "", level + "");
}
}
context.CloseQueryResult(reader, dbConnection);
return(ids);
}
public static IEnumerable <Dictionary <string, object> > AsEnumerable(this System.Data.IDataReader source)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
while (source.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < source.FieldCount; i++)
{
object value = source.GetValue(i);
// return an empty string for dbnull value of field type string
if (source.GetFieldType(i) == typeof(string) && source.IsDBNull(i))
{
value = string.Empty;
}
row.Add(source.GetName(i), value);
}
yield return(row);
}
}
//
// il modificatore sealed impedisce ad altre classi di ereditare da tale classe.
/// <summary>
///
/// </summary>
/// <param name="codiceAmministrazione"></param>
/// <returns></returns>
public static ContatoriEsibizione GetByCodice(string codiceAmministrazione, string idGruppo, string idPeople)
{
DocsPaVO.utente.Amministrazione[] amministrazioni = (DocsPaVO.utente.Amministrazione[])
BusinessLogic.Amministrazione.AmministraManager.GetAmministrazioni().ToArray(typeof(DocsPaVO.utente.Amministrazione));
if (amministrazioni.Count(e => e.codice.ToLowerInvariant() == codiceAmministrazione.ToLowerInvariant()) == 0)
{
throw new ApplicationException(string.Format("Amministrazione con codice '{0}' non trovata", codiceAmministrazione));
}
else
{
ContatoriEsibizione retValue = new ContatoriEsibizione();
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_GET_COUNT_ITEMS_ESIBIZIONE");
queryDef.setParam("codiceAmministrazione", codiceAmministrazione);
queryDef.setParam("idPeople", idPeople);
string q1 = " SELECT dpa_corr_globali.system_id FROM dpa_corr_globali WHERE dpa_corr_globali.id_gruppo = " + idGruppo;
string idRuoloInUo = string.Empty;
dbProvider.ExecuteScalar(out idRuoloInUo, q1);
queryDef.setParam("idRuoloInUo", idRuoloInUo);
string commandText = queryDef.getSQL();
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
if (reader != null)
{
while (reader.Read())
{
string stato = reader.GetString(reader.GetOrdinal("STATO"));
string cha_certificata = string.Empty;
try
{
cha_certificata = reader.GetString(reader.GetOrdinal("CERTIFICATA"));
}
catch (Exception e)
{
cha_certificata = string.Empty;
}
// Campo totale della Count
int totale = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("TOTALE")).ToString());
if (stato == DocsPaConservazione.StatoIstanzaEsibizione.NUOVA)
{
if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0"))
{
retValue.Nuove = totale;
}
if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1")
{
retValue.Nuove_Certificata = totale;
}
}
else
if (stato == DocsPaConservazione.StatoIstanzaEsibizione.CHIUSA)
{
if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0"))
{
retValue.Chiuse = totale;
}
if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1")
{
retValue.Chiuse_Certificata = totale;
}
}
else
if (stato == DocsPaConservazione.StatoIstanzaEsibizione.RIFIUTATA)
{
if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0"))
{
retValue.Rifiutate = totale;
}
if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1")
{
retValue.Rifiutate_Certificata = totale;
}
}
else
if (stato == DocsPaConservazione.StatoIstanzaEsibizione.IN_ATTESA_DI_CERTIFICAZIONE)
{
if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0"))
{
retValue.InAttesaDiCertificazione = totale;
}
if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1")
{
retValue.InAttesaDiCertificazione_Certificata = totale;
}
}
else
if (stato == DocsPaConservazione.StatoIstanzaEsibizione.IN_TRANSIZIONE)
{
if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0"))
{
retValue.Transizione = totale;
}
if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1")
{
retValue.Transizione_Certificata = totale;
}
}
}
}
}
}
return(retValue);
}
}
public ArrayList getInsertQueries(string operation, DocsPaVO.documento.SchedaDocumento schedaDoc, DocsPaDB.DBProvider db)
{
//DocsPaDB.DBProvider db=null;
logger.Debug("DocsPaAS400: getInsertQueries");
ArrayList res = new ArrayList();
if (schedaDoc.protocollo == null || schedaDoc.protocollo.numero == null || schedaDoc.protocollo.numero.Equals(""))
{
logger.Debug("Documento non protocollato");
return(res);
}
string folderCode = getFolderCode(schedaDoc);
if (folderCode != null && !folderCode.Equals(""))
{
logger.Debug("Folder code=" + folderCode);
}
else
{
logger.Debug("Folder code nullo");
return(res);
}
try
{
string key = "";
string keyQuery = getSelectKeyQuery();
logger.Debug(keyQuery);
string temp = "";
//db=new DocsPaDB.DBProvider();
//db.ExecuteScalar(out temp,keyQuery);
using (System.Data.IDataReader reader = db.ExecuteReader(keyQuery))
{
if (reader.Read())
{
temp = reader.GetValue(reader.GetOrdinal("K")).ToString();
}
}
if (temp == null || temp.Equals(""))
{
key = "0";
}
else
{
int val = Int32.Parse(temp) + 1;
key = val.ToString();
}
logger.Debug("lkey:" + key);
System.Collections.ArrayList queries = getQueryArray(key, operation, folderCode, schedaDoc);
for (int i = 0; i < queries.Count; i++)
{
string query = (string)queries[i];
logger.Debug(query);
res.Add(query);
}
string insertLogQuery = getInsertLogQuery(key);
logger.Debug(insertLogQuery);
res.Add(insertLogQuery);
}
catch (Exception e)
{
throw e;
}
finally
{
}
return(res);
}
public T GetFieldValue <T>(int ordinal)
{
return(OKHOSTING.Data.Convert.ChangeType <T>(NativeReader.GetValue(ordinal)));
}
private CartitemInfo getDataReader(iSqlDataReader dar)
{
try
{
int i = -1;
CartitemInfo info = new CartitemInfo();
info.CartID = dar.IsDBNull(++i) ? 0 : dar.GetInt32(i);
info.ProductID = dar.IsDBNull(++i) ? 0 : dar.GetInt32(i);
info.Productname = dar.IsDBNull(++i) ? string.Empty : dar.GetString(i);
info.Quantity = dar.IsDBNull(++i) ? -1 : dar.GetInt32(i);
info.Amount = dar.IsDBNull(++i) ? 0 : Math.Round(dar.GetFloat(i));
info.TemporaryID = dar.IsDBNull(++i) ? 0 : long.Parse(dar.GetValue(i).ToString());
info.Id = dar.IsDBNull(++i) ? 0 : dar.GetInt32(i);
return info;
}
catch (Exception ex)
{
throw ex;
}
}
public void GenerateXlsxReport(System.Data.Common.DbConnection conn)
{
SetDbParams(conn);
using (ExcelPackage package = (templateFile != null ? new ExcelPackage(newFile, templateFile) : new ExcelPackage(newFile)))
{
foreach (var tab in def.tabs)
{
var ws = GetWs(package, tab);
var riadok = 1;
var stlpec = 1;
var start = 1;
// set start position for Label - that would be Global or Local
if (templateFile != null)
{
ExcelNamedRange label = null;
try
{
label = ws.Names["Label"];
}
catch
{
//Console.WriteLine("{0} Exception caught.", e);
try
{
label = package.Workbook.Names["Label"];
}
catch
{
SimpleLog.WriteLog("Label field not found in this workbook/template");
}
}
if (label != null)
{
riadok = label.Start.Row;
stlpec = label.Start.Column;
}
}
var nadpis = "";
if (!(tab.title == null || tab.title == ""))
{
if (tab.title.Trim().ToUpper().Substring(0, 6) == "SELECT")
{
nadpis = conn.QuerySingle <string>(tab.title, QuerryArguments(tab.title));
}
else
{
nadpis = tab.title;
}
}
// Main Select
// https://github.com/ericmend/oracleClientCore-2.0/blob/master/test/dotNetCore.Data.OracleClient.test/OracleClientCore.cs
System.Data.IDataReader reader = conn.ExecuteReader(tab.query, QuerryArguments(tab.query));
List <TableFields> rowConfig = GetDeclaration(reader, tab.fields);
outDef.tabs.Add(new Tab {
name = tab.name, title = tab.title, query = tab.query, fields = rowConfig
});
int r = 0;
int activeColCount = 0;
while (reader.Read())
{
r++;
//Initial section for sheet
if (r == 1)
{
if (nadpis != null && nadpis != "")
{
ws.Cells[riadok, stlpec].Value = nadpis;
if (templateFile == null)
{
using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, stlpec - 1 + rowConfig.Where(o => o.order != 0).Count()])
{
rr.Merge = true;
//rr.Style.Font.SetFromFont(new Font("Britannic Bold", 12, FontStyle.Italic));
rr.Style.Font.Size = 12;
rr.Style.Font.Bold = true;
rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63));
rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
//r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
}
}
riadok++;
}
// set Data position
if (templateFile != null)
{
ExcelNamedRange label = null;
try
{
label = ws.Names["Data"];
}
catch
{
try
{
label = package.Workbook.Names["Data"];
}
catch
{
SimpleLog.WriteLog("Data field not found in this workbook/template");
}
}
if (label != null)
{
riadok = label.Start.Row - 1; // Header je nad riadkom (above row)
stlpec = label.Start.Column;
}
}
//Add the headers
for (int i = 0; i < rowConfig.Count; i++)
{
if (rowConfig[i].order != 0)
{
activeColCount++;
if (templateFile == null || tab.printHeader)
{
ws.Cells[riadok, activeColCount + stlpec - 1].Value = rowConfig[i].title;
}
ws.Names.Add(rowConfig[i].name, ws.Cells[riadok, activeColCount + stlpec - 1]);
}
}
//Ok now format the values;
//ws.Cells[1, 1, 1, tab.fields.Count].Style.Font.Bold = true; //Font should be bold
//ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
//ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.BackgroundColor.SetColor(Color.Aqua);
if (templateFile == null || tab.printHeader)
{
using (var range = ws.Cells[riadok, stlpec, riadok, activeColCount + stlpec - 1])
{
range.Style.Font.Color.SetColor(Color.White);
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
//range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
range.Style.Font.Bold = true;
range.Style.WrapText = true;
//Only need to grab the first cell of the merged range
//ws.Cells[$"A{row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//ws.Cells[$"A{row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
}
riadok++;
// set line for data
start = riadok;
}
//data section, FETCH RECORDS
for (int i = 0; i < rowConfig.Count(); i++)
{
var colId = rowConfig[i].colId;
if (rowConfig[i].order != 0 && !reader.IsDBNull(colId))
{
var a = reader.GetValue(colId);
switch (rowConfig[i].type)
{
case "String":
var pom = reader.GetString(colId);
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, pom.Substring(0, pom.Length /*- correctStringChars */));
break;
case "Integer":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetInt32(colId));
break;
case "DateTime":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId));
break;
case "Date":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId));
break;
case "Decimal":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDecimal(colId));
break;
case "Byte[]":
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetByte(colId));
break;
default:
ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetValue(colId).ToString());
break;
}
;
}
}
riadok++;
}
// no rows
if (r == 0)
{
if (nadpis != null)
{
ws.Cells[riadok, stlpec].Value = nadpis;
using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, 8 + stlpec])
{
rr.Merge = true;
rr.Style.Font.Size = 12;
rr.Style.Font.Bold = true;
rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63));
rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
}
riadok++;
}
ws.Cells[riadok, stlpec].Value = "No rows in querry result";
SimpleLog.WriteLog("No rows in Query from " + tab.name);
}
else
{
foreach (var row in rowConfig.Where(o => o.format != null && o.format != "auto"))
{
ws.Cells[start, row.order + stlpec - 1, riadok, row.order + stlpec - 1].Style.Numberformat.Format = row.format;
}
//ws.Cells[1, 1, Rows, 1].Style.Numberformat.Format = "#,##0";
//ws.Cells[1, 3, Rows, 3].Style.Numberformat.Format = "YYYY-MM-DD";
//ws.Cells[1, 4, Rows, 5].Style.Numberformat.Format = "#,##0.00";
// add comp fields
// worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);
// Autofit
if (templateFile == null)
{
ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFitColumns();
foreach (var row in rowConfig.Where(o => o.order != 0))
{
if (ws.Column(row.order).Width < row.minsize)
{
ws.Column(row.order).Width = row.minsize;
}
}
}
//Create an autofilter(global settings) for the range
if (def.autofilter)
{
ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFilter = true;
}
}
if (enabledTimestamp && def.timestamp)
{
ws.Cells[riadok + 2, stlpec].Value = "Created : " + DateTime.Now.ToString("dd.MM.yyyy H:mm:ss");
}
}
package.Workbook.Calculate();
// Set document properties
package.Workbook.Properties.Comments = "Created with EpSqlGen Copyright © 2018 Miroslav Dubovsky";
package.Workbook.Properties.Created = DateTime.Now;
package.Workbook.Properties.Title = outFileName;
var pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Author");
if (pomProp != null)
{
package.Workbook.Properties.Author = pomProp;
}
pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Company");
if (pomProp != null)
{
package.Workbook.Properties.Company = pomProp;
}
if (def.version != null)
{
package.Workbook.Properties.Subject = "Template " + defFileName + ", version:" + def.version;
}
else
{
package.Workbook.Properties.Subject = "Template " + defFileName;
}
package.Save();
}
if (!enabledConsoleLog)
{
Console.Write(JsonConvert.SerializeObject(new { output_file = newFile.FullName }, Formatting.Indented));
}
else
{
SimpleLog.WriteLog("Generated file: " + newFile.FullName);
}
}
public Fileversion(System.Data.IDataReader reader)
{
m_reader = reader;
this.Path = reader.GetValue(0).ToString();
this.More = true;
}
public BlockWithSources(System.Data.IDataReader rd)
: base(rd.GetValue(0).ToString(), Convert.ToInt64(rd.GetValue(1)))
{
m_rd = rd;
Done = !m_rd.Read();
}
/// <summary>
///
/// </summary>
/// <param name="codiceAmministrazione"></param>
/// <returns></returns>
public static Contatori GetByCodice(string codiceAmministrazione)
{
DocsPaVO.utente.Amministrazione[] amministrazioni = (DocsPaVO.utente.Amministrazione[])
BusinessLogic.Amministrazione.AmministraManager.GetAmministrazioni().ToArray(typeof(DocsPaVO.utente.Amministrazione));
if (amministrazioni.Count(e => e.codice.ToLowerInvariant() == codiceAmministrazione.ToLowerInvariant()) == 0)
{
throw new ApplicationException(string.Format("Amministrazione con codice '{0}' non trovata", codiceAmministrazione));
}
else
{
Contatori retValue = new Contatori();
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_GET_COUNT_ITEMS_CONSERVAZIONE");
queryDef.setParam("codiceAmministrazione", codiceAmministrazione);
string commandText = queryDef.getSQL();
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
while (reader.Read())
{
string stato = reader.GetString(reader.GetOrdinal("STATO"));
int totale = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("TOTALE")).ToString());
if (stato == DocsPaConservazione.StatoIstanza.DA_INVIARE)
{
retValue.DaInviare = totale;
}
else if (stato == DocsPaConservazione.StatoIstanza.INVIATA)
{
retValue.Inviate = totale;
}
else if (stato == DocsPaConservazione.StatoIstanza.RIFIUTATA)
{
retValue.Rifiutate = totale;
}
else if (stato == DocsPaConservazione.StatoIstanza.IN_LAVORAZIONE)
{
retValue.InLavorazione = totale;
}
else if (stato == DocsPaConservazione.StatoIstanza.FIRMATA)
{
retValue.Firmate = totale;
}
else if (stato == DocsPaConservazione.StatoIstanza.CONSERVATA)
{
retValue.Conservate = totale;
}
else if (stato == DocsPaConservazione.StatoIstanza.CHIUSA)
{
retValue.Chiuse = totale;
}
}
}
}
// Reperimento contantore delle notifiche
//retValue.Notifiche = DocsPaConservazione.Notifiche.GetCountNotifiche(codiceAmministrazione);
// MEV CS 1.5
// aggiungo le notifiche di leggibilità al contatore
retValue.Notifiche = DocsPaConservazione.Notifiche.GetCountNotifiche(codiceAmministrazione) + DocsPaConservazione.Notifiche.GetCountNotificheLeggibilita(codiceAmministrazione);
return(retValue);
}
}
public VtDocs.BusinessServices.Entities.Spotlight.SpotlightResponse SpotlightDocumenti(VtDocs.BusinessServices.Entities.Spotlight.SpotlightRequest request)
{
VtDocs.BusinessServices.Entities.Spotlight.SpotlightResponse response = new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResponse();
try
{
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
string filterString = string.Format("{0}*", request.Filter).Replace("'", "''");
DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("SPOTLIGHT_GET_COUNT_DOCUMENTI");
queryDef.setParam("filter", filterString);
queryDef.setParam("idPeople", request.InfoUtente.idPeople);
queryDef.setParam("idGruppo", request.InfoUtente.idGruppo);
string commandText = queryDef.getSQL();
string countField;
if (dbProvider.ExecuteScalar(out countField, commandText))
{
response.TotalRecordCount = Convert.ToInt32(countField);
queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("SPOTLIGHT_GET_DOCUMENTI");
queryDef.setParam("filter", filterString);
queryDef.setParam("idPeople", request.InfoUtente.idPeople);
queryDef.setParam("idGruppo", request.InfoUtente.idGruppo);
queryDef.setParam("start", request.Start.ToString());
queryDef.setParam("limit", request.Limit.ToString());
commandText = queryDef.getSQL();
List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultRecord> records = new List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultRecord>();
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
while (reader.Read())
{
List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell> cells = new List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell>();
cells.Add
(
new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell
{
FieldName = "IdProfile",
FieldValue = reader.GetValue(reader.GetOrdinal("IdProfile")).ToString()
}
);
cells.Add
(
new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell
{
FieldName = "Oggetto",
FieldValue = reader.GetString(reader.GetOrdinal("Oggetto"))
}
);
cells.Add
(
new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell
{
FieldName = "DocName",
FieldValue = reader.GetString(reader.GetOrdinal("DocName"))
}
);
records.Add(
new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultRecord
{
Cells = cells.ToArray()
});
}
}
response.Records = records.ToArray();
response.Success = true;
}
else
{
throw new ApplicationException(dbProvider.LastExceptionMessage);
}
}
}
catch (Exception ex)
{
response.Success = false;
if (request.TrowOnError)
{
throw DocsPaUtils.Exceptions.SoapExceptionFactory.Create(ex);
}
else
{
response.Exception = ex.Message;
}
}
return(response);
}
protected virtual bool OnMutated(IDataMutateContext context, TStatement statement, System.Data.IDataReader reader)
{
if (context is DataIncrementContextBase increment)
{
if (reader.Read())
{
increment.Result = reader.IsDBNull(0) ? 0 : (long)Convert.ChangeType(reader.GetValue(0), TypeCode.Int64);
}
else
{
return(false);
}
}
return(true);
}
private static void LargeDataToXML(
string table_schema
, string table_name
, System.Xml.XmlWriter writer
, System.Data.IDataReader dr)
{
writer.WriteStartDocument(true);
writer.WriteStartElement("table");
// writer.WriteStartElement(table_name);
writer.WriteAttributeString(null, "table_schema", null, table_schema);
writer.WriteAttributeString(null, "table_name", null, table_name);
writer.WriteAttributeString("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
// writer.WriteAttributeString("xsi", "schemaLocation", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
int fc = dr.FieldCount;
string[] columnNames = new string[fc];
System.Type[] columnTypes = new System.Type[fc];
for (int i = 0; i < dr.FieldCount; ++i)
{
columnNames[i] = dr.GetName(i);
columnTypes[i] = dr.GetFieldType(i);
} // Next i
while (dr.Read())
{
writer.WriteStartElement("row");
for (int i = 0; i < fc; ++i)
{
writer.WriteStartElement(columnNames[i]);
object obj = dr.GetValue(i);
if (obj != System.DBNull.Value)
{
if (object.ReferenceEquals(columnTypes[i], typeof(System.DateTime)))
{
System.DateTime dt = (System.DateTime)obj;
writer.WriteValue(dt.ToString("yyyy-MM-dd'T'HH':'mm':'ss'.'fff",
System.Globalization.CultureInfo.InvariantCulture));
}
else
{
writer.WriteValue(System.Convert.ToString(obj, System.Globalization.CultureInfo.InvariantCulture));
}
}
else
{
writer.WriteAttributeString("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true");
}
writer.WriteEndElement();
} // Next i
writer.WriteEndElement();
} // Whend
writer.WriteEndElement();
} // End Sub LargeDataToXML
} // WriteColumnDefinition
private static async System.Threading.Tasks.Task WriteAsXmlAsync(
string table_schema
, string table_name
, XmlRenderType_t format
, System.Xml.XmlWriter writer
, System.Data.IDataReader dr)
{
await writer.WriteStartDocumentAsync(true);
bool dataAsAttributes = format.HasFlag(XmlRenderType_t.DataInAttributes);
bool dataTableOnly = format.HasFlag(XmlRenderType_t.DataTable);
if (!dataTableOnly)
{
await writer.WriteStartElementAsync(null, "dataset", null);
}
do
{
await writer.WriteStartElementAsync(null, "table", null);
if (dataTableOnly)
{
if (table_schema != null)
{
await writer.WriteAttributeStringAsync(null, "table_schema", null, table_schema);
}
if (table_name != null)
{
await writer.WriteAttributeStringAsync(null, "table_name", null, table_name);
}
}
if (!dataAsAttributes)
{
await writer.WriteAttributeStringAsync("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
}
int fc = dr.FieldCount;
if (format.HasFlag(XmlRenderType_t.WithColumnDefinition))
{
await WriteColumnDefinition(writer, dr, format);
}
string[] columnNames = new string[fc];
System.Type[] columnTypes = new System.Type[fc];
for (int i = 0; i < dr.FieldCount; ++i)
{
columnNames[i] = dr.GetName(i);
columnTypes[i] = dr.GetFieldType(i);
} // Next i
while (dr.Read())
{
await writer.WriteStartElementAsync(null, "row", null);
for (int i = 0; i < fc; ++i)
{
if (!dataAsAttributes)
{
await writer.WriteStartElementAsync(null, columnNames[i], null);
}
object obj = dr.GetValue(i);
if (obj != System.DBNull.Value)
{
string value = null;
if (object.ReferenceEquals(columnTypes[i], typeof(System.DateTime)))
{
System.DateTime dt = (System.DateTime)obj;
value = dt.ToString("yyyy-MM-dd'T'HH':'mm':'ss'.'fff", System.Globalization.CultureInfo.InvariantCulture);
}
else
{
value = System.Convert.ToString(obj, System.Globalization.CultureInfo.InvariantCulture);
}
if (dataAsAttributes)
{
await writer.WriteAttributeStringAsync(null, columnNames[i], null, value);
}
else
{
writer.WriteValue(value);
}
} // End if (obj != System.DBNull.Value)
else
{
if (!dataAsAttributes)
{
await writer.WriteAttributeStringAsync("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true");
}
}
if (!dataAsAttributes)
{
await writer.WriteEndElementAsync(); // column
}
} // Next i
await writer.WriteEndElementAsync(); // row
} // Whend
await writer.WriteEndElementAsync(); // table
await writer.FlushAsync();
if (dataTableOnly)
{
break;
}
} while (dr.NextResult());
if (!dataTableOnly)
{
await writer.WriteEndElementAsync(); // dataset
await writer.FlushAsync();
}
} // End Sub WriteAsXmlAsync
