public static DataTable ConvertDataReaderToDataTable(DbDataReader reader)
{
try
{
DataTable table = new DataTable();
int fieldCount = reader.FieldCount;
for (int fieldIndex = 0; fieldIndex < fieldCount; ++fieldIndex)
{
table.Columns.Add(reader.GetName(fieldIndex), reader.GetFieldType(fieldIndex));
}
table.BeginLoadData();
object[] rowValues = new object[fieldCount];
while (reader.Read())
{
reader.GetValues(rowValues);
table.LoadDataRow(rowValues, true);
}
reader.Close();
table.EndLoadData();
return table;
}
catch (Exception ex)
{
throw new Exception("DataReader转换为DataTable时出错!", ex);
}
}
public void DisplayRows(DbDataReader nt)
{
this.listView1.Clear();
for (int i = 0; i < nt.FieldCount; i++)
{
listView1.Columns.Add(nt.GetName(i));
}
int rowCount = 0;
while (nt.NextResult())
{
rowCount++;
string contents = "";
if (nt[0] != null)
{
contents = nt[0].ToString();
}
ListViewItem lvi = new ListViewItem(contents);
for (int i = 1; i < nt.FieldCount; i++)
{
contents = "";
if (nt[i] != null)
{
contents = nt[i].ToString();
}
lvi.SubItems.Add(contents);
}
this.listView1.Items.Add(lvi);
}
this.toolStripStatusLabel1.Text = rowCount.ToString() + " row(s) returned.";
}
public static void Write(XmlDocument target, DbDataReader source )
{
/*
Style:
<root>
<raw><name>name1</name><index>name2</index></raw>
<raw><name>name1</name><index>name2</index></raw>
<raw><name>name1</name><index>name2</index></raw>
</root>
*/
XmlNode head = target.CreateNode(XmlNodeType.Element, "head", "");
XmlNode body = target.CreateNode(XmlNodeType.Element, "body", "");
for (int i = 0; i < source.FieldCount; ++i)
{
string vl = source.GetName(i);
string local = (string)HttpContext.GetGlobalResourceObject("local", vl);
if (local != null) vl = local;
Util.AddNodedText(head, "column", vl, false);
}
while (source.Read())
{
XmlNode raw = target.CreateNode(XmlNodeType.Element, "raw", "");
for (int i = 0; i < source.FieldCount; ++i) Util.AddNodedText(raw, "value", Util.GetString( source, i ), false);
body.AppendChild(raw);
}
target.FirstChild.AppendChild(head);
target.FirstChild.AppendChild(body);
}
public static void Print(string sql, DbDataReader reader)
{
log.Info("Results of [" + sql + "]");
string results = "\n";
if (reader.HasRows)
{
for (int j = 0; j < reader.FieldCount; j++)
{
results += reader.GetName(j);
if (j < reader.FieldCount - 1)
{
results += "|";
}
}
results += "\n";
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
results += reader.GetValue(i);
if (i < reader.FieldCount - 1)
{
results += "|";
}
}
results += "\n";
}
results = results.Substring(0, results.Length - 1);
log.Info(results);
}
}
// criar instancia a partir dos dados obtidos do banco
public FluxoCaixa(DbDataReader r)
{
for (int i = 0; i < r.FieldCount; i++)
{
switch (r.GetName(i))
{
case "flc_id":
id = Convert.ToInt16(r[i]);
break;
case "flc_data":
data = Convert.ToDateTime(r[i]);
break;
case "flc_descricao":
descricao = Convert.ToString(r[i]);
break;
case "flc_tipo":
tipo = new TipoFluxo(r);
break;
case "flc_valor":
valor = Convert.ToDouble(r[i]);
break;
}
}
}
public Table(DbDataReader reader)
{
Headers = Enumerable.Range(0, reader.FieldCount).Select(column => reader.GetName(column)).ToList();
Rows = new List<List<string>>();
while (reader.Read())
Rows.Add(Enumerable.Range(0, reader.FieldCount).Select(column => reader[column]).Select(value => GetDBValue(value)).ToList());
}
private static string[] GetColumnNames(DbDataReader dr, int numberOfColumns) {
var colNames = new List<string>();
for (int i = 0; i < numberOfColumns; i++) {
colNames.Add(dr.GetName(i));
}
return colNames.ToArray();
}
// criar instancia a partir dos dados obtidos do banco
public Contato(DbDataReader r)
{
for (int i=0; i < r.FieldCount; i++)
{
switch (r.GetName(i))
{
case "nome":
nome = Convert.ToString(r[i]);
break;
case "telefones":
telefones = Convert.ToString(r[i]);
break;
case "endereco":
endereco = Convert.ToString(r[i]);
break;
case "aniversario":
DateTime niverDt = Convert.ToDateTime(r[i]);
aniversario = String.Format("{0:dd/MM/yyyy}", niverDt);
break;
case "outros":
outros = Convert.ToString(r[i]);
break;
}
}
}
public DynamicEntityValue(DbDataReader reader)
{
for(int i = 0; i < reader.FieldCount; i++)
{
this._nameValues.Add(reader.GetName(i), reader.GetValue(i));
}
}
public static DataRow ImportDataReader(this DataTable table, DbDataReader dr)
{
var row = table.NewRow();
for (var i = 0; i < dr.FieldCount; i++) {
var columnName = dr.GetName(i);
row[columnName] = dr[i];
}
table.Rows.Add(row);
return row;
}
/// <summary>
/// Initializes a new instance of the EFCachingDataReaderCacheWriter class.
/// </summary>
/// <param name="wrappedReader">The wrapped reader.</param>
/// <param name="maxRows">The maximum number of rows which can be cached.</param>
/// <param name="addToCache">The delegate used to add the result to the cache when the reader finishes.</param>
public EFCachingDataReaderCacheWriter(DbDataReader wrappedReader, int maxRows, Action<DbQueryResults> addToCache)
{
this.wrappedReader = wrappedReader;
this.addToCache = addToCache;
this.maxRows = maxRows;
for (int i = 0; i < wrappedReader.FieldCount; ++i)
{
this.queryResults.ColumnNames.Add(wrappedReader.GetName(i));
}
}
public BasicFieldNameLookup(DbDataReader reader)
{
int length = reader.FieldCount;
string[] fieldNames = new string[length];
for (int i = 0; i < length; ++i)
{
fieldNames[i] = reader.GetName(i);
}
_fieldNames = fieldNames;
}
public FieldNameLookup(DbDataReader reader, int defaultLocaleID)
{
int length = reader.FieldCount;
string[] fieldNames = new string[length];
for (int i = 0; i < length; ++i)
{
fieldNames[i] = reader.GetName(i);
}
_fieldNames = fieldNames;
_defaultLocaleID = defaultLocaleID;
}
public static DataTable CreateTable(DbDataReader reader)
{
DataTable table = new DataTable();
for (int i = 0; i < reader.FieldCount; i++)
{
DataColumn column = new DataColumn(reader.GetName(i), reader.GetFieldType(i));
table.Columns.Add(column);
}
table.AcceptChanges();
return table;
}
public static void ExportExcel(DbDataReader dataReader, string strTitle, string fileFullPath, bool isAutoFit)
{
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
object[] header = new object[dataReader.FieldCount];
bool first = true;
int rowIndex = 0;
Style style = new Style();
while (dataReader.Read())
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
if (first)
{
style.Custom = "@";
style.Font.IsBold = true;
workbook.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
workbook.Worksheets[0].Cells[rowIndex, i].Value = dataReader.GetName(i);
}
style.Font.IsBold = false; if (dataReader[i] is string)
{
style.Custom = "@";
}
else if (dataReader[i] is DateTime)
{
style.Custom = "yyyy-MM-dd";
}
else if (dataReader[i] is decimal)
{
style.Custom = "####.#####";
style.ShrinkToFit = true;
}
workbook.Worksheets[0].Cells[rowIndex + 1, i].SetStyle(style);
workbook.Worksheets[0].Cells[rowIndex + 1, i].Value = dataReader[i];
}
first = false;
rowIndex++;
}
if (isAutoFit)
{
workbook.Worksheets[0].AutoFitColumns();
workbook.Worksheets[0].AutoFitRows(true);
}
if (fileFullPath.ToLower().EndsWith(".xlsx"))
workbook.Save(fileFullPath, FileFormatType.Excel2007Xlsx);
else
workbook.Save(fileFullPath, FileFormatType.Default);
}
public virtual IRelationalValueBufferFactory CreateValueBufferFactory(
IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader)
{
Check.NotNull(relationalValueBufferFactoryFactory, nameof(relationalValueBufferFactoryFactory));
Check.NotNull(dataReader, nameof(dataReader));
var readerColumns
= Enumerable
.Range(0, dataReader.FieldCount)
.Select(i => new
{
Name = dataReader.GetName(i),
Ordinal = i
})
.ToList();
var types = new Type[_selectExpression.Projection.Count];
var indexMap = new int[_selectExpression.Projection.Count];
for (var i = 0; i < _selectExpression.Projection.Count; i++)
{
var aliasExpression = _selectExpression.Projection[i] as AliasExpression;
if (aliasExpression != null)
{
var columnName
= aliasExpression.Alias
?? aliasExpression.TryGetColumnExpression()?.Name;
if (columnName != null)
{
var readerColumn
= readerColumns.SingleOrDefault(c =>
string.Equals(columnName, c.Name, StringComparison.OrdinalIgnoreCase));
if (readerColumn == null)
{
throw new InvalidOperationException(Strings.FromSqlMissingColumn(columnName));
}
types[i] = _selectExpression.Projection[i].Type;
indexMap[i] = readerColumn.Ordinal;
}
}
}
return relationalValueBufferFactoryFactory.Create(types, indexMap);
}
static int GetName(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
System.Data.Common.DbDataReader obj = (System.Data.Common.DbDataReader)ToLua.CheckObject(L, 1, typeof(System.Data.Common.DbDataReader));
int arg0 = (int)LuaDLL.luaL_checknumber(L, 2);
string o = obj.GetName(arg0);
LuaDLL.lua_pushstring(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
public static string ConvertRowToString(DbDataReader row, StringBuilder headers)
{
var output = new List<string>();
for (var i = 0; i < row.FieldCount; i++)
{
output.Add("" + row.GetValue(i));
}
if (headers.Length == 0)
{
for (var i = 0; i < row.FieldCount; i++)
{
headers.Append(row.GetName(i) + ", ");
}
}
return String.Join(", ", output.ToArray());
}
public Lembrete(DbDataReader r)
{
for (int i=0; i < r.FieldCount; i++)
{
switch (r.GetName(i))
{
case "id":
id = Convert.ToInt16(r[i]);
break;
case "descricao":
descricao = Convert.ToString(r[i]);
break;
}
}
}
// criar instancia a partir dos dados obtidos do banco
public PaginaDiario(DbDataReader r)
{
for (int i = 0; i < r.FieldCount; i++)
{
switch (r.GetName(i))
{
case "data":
data = Convert.ToDateTime(r[i]);
break;
case "texto":
texto = Convert.ToString(r[i]);
break;
}
}
}
public void WriteColumns2File(DbDataReader reader, string file)
{
if (reader == null)
return;
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(file))
{
for (int i = 0; i < reader.VisibleFieldCount; i++)
{
string index = (i + 1).ToString("D2");
string columnName = reader.GetName(i).ToUpper();
string type = reader.GetFieldType(i).Name;
string comment = "说明";
writer.WriteLine(string.Format("{0}\t|\t{1}\t|\t{2}\t|\t{3}", index, columnName, type, comment));
}
}
}
protected PosName[] GetColumnNames(DbDataReader dataReader, PocoData pocoData)
{
if (_columnNames != null)
return _columnNames;
var cols = Enumerable.Range(0, dataReader.FieldCount)
.Select(x => new PosName { Pos = x, Name = dataReader.GetName(x) })
.Where(x => !string.Equals("poco_rn", x.Name))
.ToList();
if (cols.Any(x => x.Name.StartsWith(PropertyMapperNameConvention.SplitPrefix, StringComparison.OrdinalIgnoreCase)))
{
return (_columnNames = cols.ConvertFromNewConvention(pocoData).ToArray());
}
return (_columnNames = cols.ConvertFromOldConvention(pocoData.Members).ToArray());
}
public DataTable(DbDataReader reader)
{
this.fields = new List<string>();
this.data = new List<object[]>();
for (int i = 0; i < reader.FieldCount; i++)
{
fields.Add(reader.GetName(i));
}
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
for (int i = 0; i < fields.Count; i++)
{
values[i] = reader.GetValue(reader.GetOrdinal(fields[i]));
}
data.Add(values);
}
}
public DataResult(DbDataReader reader)
{
ColumnNames =
Enumerable.Range(0, reader.FieldCount)
.Select(x => reader.GetName(x))
.ToList();
Rows = new List<RowData>();
while (reader.Read()) {
Dictionary<string, string> rowData = new Dictionary<string, string>();
for (var i = 0; i < reader.FieldCount; i++) {
if (reader[i].GetType() == typeof(DateTime)) {
// Use ISO time
rowData[ColumnNames[i]] = ((DateTime)reader[i]).ToString("s");
} else {
rowData[ColumnNames[i]] = reader[i].ToString();
}
}
Rows.Add(rowData);
}
}
// criar instancia a partir dos dados obtidos do banco
public TipoFluxo(DbDataReader r)
{
for (int i = 0; i < r.FieldCount; i++)
{
switch (r.GetName(i))
{
case "tfc_id":
id = Convert.ToInt16(r[i]);
break;
case "tfc_descricao":
descricao = Convert.ToString(r[i]);
break;
case "tfc_fluxo":
fluxo = (TipoFluxo.Fluxos)Convert.ToInt16(r[i]);
break;
}
}
}
/// <summary>
/// This may be useful if you want to serialize a full table as json. Usually, a IDictionary object is serialized as JSON object (eg Newtonsoft.Json)
/// It can be used with dynmaic as well (http://stackoverflow.com/questions/18290852/generate-dynamic-object-from-dictionary-with-c-reflection)
/// </summary>
/// <typeparam name="T">The type of the IDictionary, usually Dictionary<string,object></typeparam>
/// <param name="table">The DbDataReader to convert to a Dictionary List</param>
/// <param name="inputList">An existing list to append the items. Can be null to create a new list</param>
/// <returns>A List containing the Objects</returns>
public static List <T> AsDictionaryList <T>(this System.Data.Common.DbDataReader table, List <T>?inputList = null) where T : IDictionary <string, object?>, new()
{
if (inputList == null)
{
inputList = new List <T>();
}
int columnCount = table.FieldCount;
while (table.Read())
{
T item = new T();
foreach (var col in Enumerable.Range(0, columnCount))
{
item.Add(table.GetName(col), table.IsDBNull(col) ? null : table[col]);
}
inputList.Add(item);
}
return(inputList);
}
// criar instancia a partir dos dados obtidos do banco
public Compromisso(DbDataReader r)
{
for (int i=0; i < r.FieldCount; i++)
{
switch (r.GetName(i))
{
case "cpm_id":
id = Convert.ToInt32(r[i]);
break;
case "cpm_inicio":
inicio = Convert.ToDateTime(r[i]);
break;
case "cpm_fim":
fim = Convert.ToDateTime(r[i]);
break;
case "cpm_descricao":
descricao = Convert.ToString(r[i]);
break;
}
}
}
public String ViewTableTxt(String TableName)
{
ESData.GetInst.Open_Conn();
String txt = "TabeName:" + TableName + "\n";
System.Data.Common.DbDataReader dr = ESData.GetInst.Reader(String.Format("select * from {0};", TableName));
for (int i = 0; i < dr.FieldCount; i++)
{
txt += dr.GetName(i) + "\t ";
}
txt += "\n"; //if (dr.HasRows)
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
try{ txt += dr[i].ToString() + "\t "; }
catch { MessageBox.Show(dr[0].ToString()); }
}
txt += "\n";
}
dr.Close();
dr.Dispose();
return(txt);
}
private FeatureDataTable CreateTableFromReader(DbDataReader reader, int geomIndex)
{
var res = new FeatureDataTable {TableName = Table};
for (var c = 0; c < geomIndex; c++)
{
var fieldType = reader.GetFieldType(c);
if (fieldType == null)
throw new Exception("Unable to retrieve field type for column " + c);
res.Columns.Add(DequoteIdentifier(reader.GetName(c)), fieldType);
}
return res;
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["excel"] != null)
{
ExportExcel();
return;
}
string url = Request.QueryString["url"];
//Response.Write(url);
string html = System.IO.File.ReadAllText(Server.MapPath("~" + url));
string recordid = Request.QueryString["recordid"];
string path = Request.QueryString["altPath"] ?? Request.QueryString["path"]; // <conn>.<schema>.<object>
string type = Request.QueryString["type"] ?? "application";
string[] pathArray = path.Contains('_') ? path.Split('_') : path.Split('.');
System.Data.Common.DbDataReader reader = null;
String TypeString = Request.QueryString["Type"];
if (TypeString.Equals("timesheet"))
{
System.Collections.IDictionary param = new Dictionary <string, object>();
var UserID = Request.QueryString["userID"];
var OrgUnit = Request.QueryString["orgID"];
var start = Request.QueryString["start"];
var end = Request.QueryString["end"];
var rank = Request.QueryString["rank"];
param.Add("ID", null);
param.Add("OrganizationUnitID", null);
param.Add("StartDate", null);
param.Add("EndDate", null);
param.Add("Rank", null);
if (UserID.Length != 0)
{
param["ID"] = UserID;
}
if (OrgUnit.Length != 0)
{
param["OrganizationUnitID"] = OrgUnit;
}
if (start.Length != 0)
{
param["StartDate"] = start;
}
if (end.Length != 0)
{
param["EndDate"] = end;
}
if (rank.Length != 0)
{
param["Rank"] = rank;
}
param.Add("CreatedBy", 1);
param.Add("Offset", 0);
param.Add("Limit", 0);
int typeID = 0;
string p = Request.QueryString["Path"].Split('.')[0];
try
{
typeID = Convert.ToInt32(Request.QueryString["typeID"]);
}
catch (Exception) { }
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[p].ToString());
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "att.spGetMonthlyTimeSheet";
foreach (var ent in param.Keys)
{
command.Parameters.Add(new SqlParameter(ent.ToString(), param[ent]));
}
command.CommandTimeout = 200;
reader = command.ExecuteReader();
};
//reader = plus.Data.DAL.Read("AMS", "att.spGetMonthlyTimeSheet",param);
Dictionary <int, String> maps = new Dictionary <int, string>();
maps.Add(2, "PR");
maps.Add(3, "AB");
maps.Add(4, "HD");
maps.Add(5, "ALH");
maps.Add(6, "AL");
StringBuilder sb = new StringBuilder();
sb.Append("<thead>");
sb.Append("<tr>");
sb.Append("<th>کود</th><th>نام مکمل</th><th>نام پدر</th><th>وظیفه</th><th>بست</th><th>اداره</th>");
for (int i = 0; i < reader.FieldCount; i++)
{
if (i > 6)
{
sb.Append("<th>" + reader.GetName(i).Split('_')[2] + "</th>");
}
}
sb.Append("</tr></thead>");
sb.Append("<tbody>");
while (reader.Read())
{
sb.Append("<tr>");
for (int i = 1; i < reader.FieldCount; i++)
{
if (i < 7 || (typeID == 0 || typeID == 1))
{
sb.Append("<td>" + reader[i].ToString() + "</td>");
}
else
{
string s = reader[i].ToString();
if (maps[typeID].Equals(s))
{
sb.Append("<td>" + reader[i].ToString() + "</td>");
}
else
{
sb.Append("<td> </td>");
}
}
}
sb.Append("</tr>");
}
sb.Append("<tbody>");
html = html.Replace("$body", sb.ToString());
Response.Write(html);
conn.Close();
return;
}
else if (TypeString.Equals("attreport"))
{
int typeID = 0;
try
{
typeID = Convert.ToInt32(Request.QueryString["typeID"]);
}
catch (Exception) { }
System.Collections.IDictionary param = new Dictionary <string, object>();
var UserID = Request.QueryString["userID"];
var OrgUnit = Request.QueryString["orgID"];
var start = Request.QueryString["start"];
var end = Request.QueryString["end"];
var rank = Request.QueryString["rank"];
param.Add("UserID", null);
param.Add("OrganizationUnit", null);
param.Add("StartDate", null);
param.Add("EndDate", null);
param.Add("RankID", null);
param.Add("AttID", null);
param.Add("HRCode", null);
param.Add("CreatedBy", null);
param.Add("Type", null);
if (UserID.Length != 0)
{
param["UserID"] = UserID;
}
if (OrgUnit.Length != 0)
{
param["OrganizationUnit"] = OrgUnit;
}
if (start.Length != 0)
{
param["StartDate"] = start;
}
if (end.Length != 0)
{
param["EndDate"] = end;
}
if (rank.Length != 0)
{
param["RankID"] = rank;
}
if (typeID != 0)
{
param["Type"] = typeID;
}
reader = plus.Data.DAL.Read("AMS", "att.spGenerateTimeSheet", param);
StringBuilder sb = new StringBuilder();
sb.Append("<thead>");
sb.Append("<tr>");
sb.Append("<th> کود کادری </th><th> نام مکمل </th><th> نام پدر </th><th> وظیفه </th><th> بست </th><th> ریاست </th><th> شیفت </th><th> تاریخ </th><th> وقت دخول </th><th> وقت خروج </th><th> حاضری </th>");
sb.Append("</tr>");
sb.Append("</thead>");
sb.Append("<tbody>");
while (reader.Read())
{
sb.Append("<tr>");
for (int i = 1; i < reader.FieldCount; i++)
{
if (!reader.GetName(i).Equals("AttType"))
{
sb.Append("<td>" + reader[i].ToString() + "</td>");
}
}
sb.Append("</tr>");
}
sb.Append("</tbody>");
html = html.Replace("$body", sb.ToString());
Response.Write(html);
return;
}
if (type.Equals("idcard"))
{
//string keyCol = Request.QueryString["PID"] ?? "ID";
System.Data.DataSet ds = plus.Data.DAL.GetDataSet("hcm", "select FullName, FatherName, HRCode, Position, IssueDate, PhotoPath from rec.vCardPrint where ID=" + recordid);
string imgPath = ds.Tables[0].Rows[0]["PhotoPath"].ToString();
string xName = ds.Tables[0].Rows[0]["FullName"].ToString();
string xNameEng = (ds.Tables[0].Rows[0]["FullName"].ToString()) + "s";
string xSO = ds.Tables[0].Rows[0]["Fathername"].ToString();
string xDuty = ds.Tables[0].Rows[0]["Position"].ToString();
string xCode = ds.Tables[0].Rows[0]["HRCode"].ToString();
string xIssueDate = ds.Tables[0].Rows[0]["IssueDate"].ToString();
//Labels
string lblName = "اسم:";
string lblSO = "ولد/بنت:";
string lblDuty = "وظیفه:";
string lblID = "شماره شناخت:";
string lblIssueDate = "تاریخ صدور:";
string lblNameEng = "Name:";
string lblSOEng = "S/O:";
string lblDutyEng = "Duty:";
string lblIDEng = "ID Number:";
string lblIssueDateEng = "IssueDate:";
//Get the Data
string Name = xName; //"نادر";
string SO = xSO; //"روشن";
string Duty = xDuty; //"انجنیر نرم افزار";
string ID = xCode; //"SC000122";
string IssueDate = xIssueDate; //"1396-12-2";
string NameEng = xNameEng; //"Nadir";
string SOEng = xSO; //"Roshan";
string DutyEng = xDuty; //"Software Engineer";
string IDEng = xCode; //"SC000122";
string IssueDateEng = xIssueDate; //"2017-12-2";
//imgPath = imgPath.Contains('\\') ? imgPath.Replace('\\', '/') : imgPath.Replace('\\','/');
string photo = Server.MapPath("~/hcm/userPhoto" + imgPath);
//Set X and Y axis for Labels and Data
PointF lblNameLoc = new PointF(535f, 130f);
PointF lblSOLoc = new PointF(535f, 190f);
PointF lblDutyLoc = new PointF(535f, 250f);
PointF lblIDLoc = new PointF(535f, 310f);
PointF lblIssueDateLoc = new PointF(535f, 365f);
PointF lblNameEngLoc = new PointF(265f, 130f);
PointF lblSOEngLoc = new PointF(265f, 190f);
PointF lblDutyEngLoc = new PointF(265f, 250f);
PointF lblIDEngLoc = new PointF(265f, 310f);
PointF lblIssueDateEngLoc = new PointF(265f, 365f);
PointF nameLoc = new PointF(360f, 130f);
PointF SOLoc = new PointF(360f, 190f);
PointF DutyLoc = new PointF(360f, 250f);
PointF IDLoc = new PointF(360f, 310f);
PointF IssueDateLoc = new PointF(360f, 365f);
PointF photoLoc = new PointF(600f, 140f);
PointF nameEngLoc = new PointF(450f, 130f);
PointF SOEngLoc = new PointF(450f, 190f);
PointF DutyEngLoc = new PointF(450f, 250f);
PointF IDEngLoc = new PointF(450f, 310f);
PointF IssueDateEngLoc = new PointF(450f, 365f);
PointF photoEngLoc = new PointF(20f, 140f);
//Dari Text Direction
StringFormat rtl = new StringFormat(StringFormatFlags.DirectionRightToLeft);
//Cards path
string imageDariFilePath = Server.MapPath("~/page/templ/idcard/dari/Administration.jpg"); //@"~/hcm/page/idcard/dari/Administration.jpg"; //template path dari
string imageEngFilePath = Server.MapPath("~/page/templ/idcard/dari/Administration.jpg"); //@"~/hcm/page/idcard/eng/Administration.jpg"; //template path eng
string imageDariOutput = Server.MapPath("~/page/templ/idcard/Generated/" + Name + ".jpg"); //@"~/hcm/page/idcard/Generated/" + Name + ".jpg"; //Path after generated dari
string imageEngOutput = Server.MapPath("~/page/templ/idcard/Generated/" + NameEng + ".jpg"); //@"~/hcm/page/idcard/Generated/" + NameEng + ".jpg"; //Path after generated eng
//Resize Image
System.Drawing.Image imgResized = ScaleImage(System.Drawing.Image.FromFile(photo), 70, 80);
//Convert Image to bitmap
Bitmap daribitmap = (Bitmap)System.Drawing.Image.FromFile(imageDariFilePath);
Bitmap engbitmap = (Bitmap)System.Drawing.Image.FromFile(imageEngFilePath);
Bitmap bitmapPhoto = (Bitmap)imgResized;
//Write the labels and data on image using graphics class
using (Graphics gdari = Graphics.FromImage(daribitmap))
using (Graphics geng = Graphics.FromImage(engbitmap))
{
using (Font arialFont = new Font("Times New Roman", 8))
{
//Dari Card-----------------------------------------------------------------
//Draw labels
gdari.DrawString(lblName, arialFont, Brushes.Black, lblNameLoc, rtl);
gdari.DrawString(lblSO, arialFont, Brushes.Black, lblSOLoc, rtl);
gdari.DrawString(lblDuty, arialFont, Brushes.Black, lblDutyLoc, rtl);
gdari.DrawString(lblID, arialFont, Brushes.Black, lblIDLoc, rtl);
gdari.DrawString(lblIssueDate, arialFont, Brushes.Black, lblIssueDateLoc, rtl);
//Draw data
//gdari.CompositingMode = CompositingMode.SourceOver;
//bitmapPhoto.MakeTransparent();
gdari.DrawImage(bitmapPhoto, photoLoc);
gdari.DrawString(Name, arialFont, Brushes.Black, nameLoc, rtl);
gdari.DrawString(SO, arialFont, Brushes.Black, SOLoc, rtl);
gdari.DrawString(Duty, arialFont, Brushes.Black, DutyLoc, rtl);
gdari.DrawString(ID, arialFont, Brushes.Black, IDLoc, rtl);
gdari.DrawString(IssueDate, arialFont, Brushes.Black, IssueDateLoc, rtl);
// English Card--------------------------------------------------------------
//Draw labels
geng.DrawString(lblNameEng, arialFont, Brushes.Black, lblNameEngLoc);
geng.DrawString(lblSOEng, arialFont, Brushes.Black, lblSOEngLoc);
geng.DrawString(lblDutyEng, arialFont, Brushes.Black, lblDutyEngLoc);
geng.DrawString(lblIDEng, arialFont, Brushes.Black, lblIDEngLoc);
geng.DrawString(lblIssueDateEng, arialFont, Brushes.Black, lblIssueDateEngLoc);
//Draw data
//geng.CompositingMode = CompositingMode.SourceOver;
//bitmapPhoto.MakeTransparent();
geng.DrawImage(bitmapPhoto, photoEngLoc);
geng.DrawString(NameEng, arialFont, Brushes.Black, nameEngLoc);
geng.DrawString(SOEng, arialFont, Brushes.Black, SOEngLoc);
geng.DrawString(DutyEng, arialFont, Brushes.Black, DutyEngLoc);
geng.DrawString(IDEng, arialFont, Brushes.Black, IDEngLoc);
geng.DrawString(IssueDateEng, arialFont, Brushes.Black, IssueDateEngLoc);
}
}
// Save the generated card
using (MemoryStream memorydari = new MemoryStream())
using (MemoryStream memoryeng = new MemoryStream())
{
using (FileStream fsdari = new FileStream(imageDariOutput, FileMode.Create, FileAccess.ReadWrite))
using (FileStream fseng = new FileStream(imageEngOutput, FileMode.Create, FileAccess.ReadWrite))
{
daribitmap.Save(memorydari, ImageFormat.Jpeg);
engbitmap.Save(memoryeng, ImageFormat.Jpeg);
byte[] bytesdari = memorydari.ToArray();
byte[] byteseng = memoryeng.ToArray();
fsdari.Write(bytesdari, 0, bytesdari.Length);
fseng.Write(byteseng, 0, byteseng.Length);
}
}
//html = html.Replace("{imgPath}", Convert.ToString(imageDariOutput));
}
else
{
if (type.Equals("DailyUserReport"))
{
string reportdate = Request.QueryString["reportdate"];
string reporttype = Request.QueryString["reporttype"];
string shamsidate = Request.QueryString["shamsidate"];
string issent = Request.QueryString["issent"];
string rolename = Request.QueryString["rolename"];
reader = plus.Data.DAL.Read(pathArray[0],
string.Format("SELECT * FROM {0}.{1} WHERE RoleID=@RoleID AND ReportDate = @ReportDate and ReportType=@ReportType and Issent=@Issent order by Total DESC", pathArray[1], pathArray[2])
, "RoleID", recordid, "ReportDate", reportdate, "ReportType", reporttype, "Issent", issent);
String tbody = " <page size='A4' class='Print'>";
tbody += "<table class='table' style='width:90%;'>";
String thead = "";
thead += "<thead>";
thead += "<tr>";
thead += "<th colspan='10' style='border:none; font-size:16px;'>گزارش روزانه کارمندان بخش $RoleName بابت تاریخ $rptdate </th>";
thead += "</tr>";
thead += "<tr>";
thead += "<th> شماره</th>";
thead += "<th> اسم کارمند</th>";
thead += "<th> دیپارتمنت</th>";
thead += "<th> تاریخ</th>";
thead += "<th> نوع گزارش</th>";
thead += "<th>حالت اسناد</th>";
thead += "<th>تعداد</th>";
thead += "</tr>";
thead += "</thead>";
thead = thead.Replace("$RoleName", rolename);
thead = thead.Replace("$rptdate", shamsidate);
tbody += thead;
tbody += "<tbody>";
int counter = 1;
while (reader.Read())
{
string trow = "<tr>";
trow += "<td>$NO</td>";
trow += "<td>$FullName</td>";
trow += "<td>$RoleName</td>";
trow += "<td>$ReportDate_Shamsi</td>";
trow += "<td>$RptTypeName</td>";
trow += "<td>$SENTName</td>";
trow += "<td>$Total</td>";
trow += "</tr>";
trow = trow.Replace("$NO", counter.ToString());
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i);
trow = trow.Replace("$" + colName, Convert.ToString(reader[colName]));
}
tbody += trow;
counter++;
}
tbody += "</tbody></table> <br/>";
tbody += "<div style='margin-right:20px; margin-left:20px;'>";
System.Data.Common.DbDataReader summaryReader = plus.Data.DAL.Read(pathArray[0],
string.Format("exec [rpt].[spGetGrandTotalDailyUsersReport] {0}, '{1}', {2}, {3}", recordid, reportdate, reporttype, issent));
String sthead = "";
String summary = "<table class='table' style='width:90%;'>";
String stbody = "<tbody>";
sthead += "<thead>";
sthead += "<tr>";
sthead += "<th colspan='10' style='border:none; font-size:16px;'>مجموع عمومی:</th>";
sthead += "</tr>";
sthead += "</thead>";
summary += sthead;
while (summaryReader.Read())
{
stbody += "<tr>";
stbody += "<th>@GrandTotal</th>";
stbody += "</tr>";
for (int i = 0; i < summaryReader.FieldCount; i++)
{
string colName = summaryReader.GetName(i);
stbody = stbody.Replace("@" + colName, Convert.ToString(summaryReader[colName]));
}
}
summary += stbody;
summary += "</table>";
tbody += summary;
tbody += "</div>";
tbody += "</page>";
html = html.Replace("$body", tbody);
}
else if (!type.Equals("closedlist"))
{
string keyCol = Request.QueryString["keyCol"] ?? "ID";
reader = plus.Data.DAL.Read(pathArray[0],
string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", pathArray[1], pathArray[2], keyCol)
, "RecordID", recordid);
if (type.Equals("application"))
{
while (reader.Read())
{
// loop through columns
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i); // firstName
if (!colName.Equals("BirthDate"))
{
html = html.Replace("{" + colName + "}", Convert.ToString(reader[colName]));
}
else
{
html = html.Replace("{" + colName + "}", Convert.ToDateTime(reader[colName]).ToString("dd MMM yyyy"));
}
}
}
}
else if (type.Equals("progresslist"))
{
string subpath = "preport_rpt_vUserReport";
string[] subPathArray = subpath.Split('_');
string subKeyCol = "ID";
System.Data.Common.DbDataReader subReader = plus.Data.DAL.Read(subPathArray[0],
string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", subPathArray[1], subPathArray[2], subKeyCol)
, "RecordID", recordid);
String tbody = " <page size='A4' class='Print'>";
tbody += "<table class='table' style='font-size:10px !important;'>";
String thead = "";
thead += "<thead>";
thead += "<tr>";
thead += "<th colspan='10' style='border:none; font-size:14px;'>گزارش نمبر $CODE از فعالیت و کارکرد مورخ $SHAMSI محترم $Employee کارمند بخش $RoleName</th>";
thead += "</tr>";
thead += "<tr>";
thead += "<th> شماره</th>";
thead += "<th> کود متقاضی</th>";
thead += "<th> نام متقاضی</th>";
thead += "<th>نام پدر</th>";
thead += "<th>نام پدرکلان</th>";
thead += "<th>محل تولد</th>";
thead += "<th> تاریخ تولد</th>";
thead += "<th> تاریخ اجرأت کارمند</th>";
thead += "<th> زمان اجرأت کارمند</th>";
thead += "<th> مرحله ارسالی</th>";
thead += "</tr>";
thead += "</thead>";
while (subReader.Read())
{
for (int i = 0; i < subReader.FieldCount; i++)
{
string subColName = subReader.GetName(i);
thead = thead.Replace("$" + subColName, Convert.ToString(subReader[subColName]));
}
}
tbody += thead;
tbody += "<tbody>";
int counter = 1;
while (reader.Read())
{
string trow = "<tr>";
trow += "<td>$NO</td>";
trow += "<td>$Code</td>";
trow += "<td>$FullName</td>";
trow += "<td>$FatherNameLocal</td>";
trow += "<td>$GrandFatherNameLocal</td>";
trow += "<td>$BirthLocation</td>";
trow += "<td>$BirthDate</td>";
trow += "<td>$ProcessedDate</td>";
trow += "<td>$ProcessedTime</td>";
trow += "<td>$ProcessName</td>";
trow += "</tr>";
trow = trow.Replace("$NO", counter.ToString());
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i);
trow = trow.Replace("$" + colName, Convert.ToString(reader[colName]));
}
tbody += trow;
counter++;
}
tbody += "</tbody></table> <br/>";
tbody += "<div style='margin-right:10px; margin-left:10px;'>";
String sthead = "";
String summary = "<table class='table' style='font-size:16px; width:90%;'>";
String stbody = "<tbody>";
sthead += "<tr>";
sthead += "<th>امضأ و نام مکمل کارمند</th>";
sthead += "<th>امضأ و نام مکمل آمر بخش</th>";
sthead += "<th>امضأ و نام مکمل تسلیم شونده</th>";
sthead += "</tr>";
sthead += "</thead>";
summary += sthead;
stbody += "<tr style='height:100px;'>";
stbody += "<th></th>";
stbody += "<th></th>";
stbody += "<th></th>";
stbody += "</tr>";
summary += stbody;
summary += "</table>";
tbody += summary;
tbody += "</div>";
tbody += "</page>";
html = html.Replace("$body", tbody);
}
else if (type.Equals("postprogresslist"))
{
string subpath = "preport_rpt_vUserReport";
string[] subPathArray = subpath.Split('_');
string subKeyCol = "ID";
System.Data.Common.DbDataReader subReader = plus.Data.DAL.Read(subPathArray[0],
string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", subPathArray[1], subPathArray[2], subKeyCol)
, "RecordID", recordid);
String tbody = " <page size='A4' class='Print'>";
tbody += "<table class='table' style='font-size:10px !important;'>";
String thead = "";
thead += "<thead>";
thead += "<tr>";
thead += "<th colspan='10' style='border:none; font-size:14px;'>گزارش نمبر $CODE از فعالیت و کارکرد مورخ $SHAMSI محترم $Employee کارمند بخش $RoleName</th>";
thead += "</tr>";
thead += "<tr>";
thead += "<th> شماره</th>";
thead += "<th> نمبر پاسپورت</th>";
thead += "<th> نام متقاضی</th>";
thead += "<th>نام پدر</th>";
thead += "<th>نام پدرکلان</th>";
thead += "<th>محل تولد</th>";
thead += "</tr>";
thead += "</thead>";
while (subReader.Read())
{
for (int i = 0; i < subReader.FieldCount; i++)
{
string subColName = subReader.GetName(i);
thead = thead.Replace("$" + subColName, Convert.ToString(subReader[subColName]));
}
}
tbody += thead;
tbody += "<tbody>";
int counter = 1;
while (reader.Read())
{
string trow = "<tr>";
trow += "<td>$NO</td>";
trow += "<td>$PassportNumber</td>";
trow += "<td>$FullName</td>";
trow += "<td>$FatherNameLocal</td>";
trow += "<td>$GrandFatherNameLocal</td>";
trow += "<td>$BirthLocation</td>";
trow += "</tr>";
trow = trow.Replace("$NO", counter.ToString());
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i);
trow = trow.Replace("$" + colName, Convert.ToString(reader[colName]));
}
tbody += trow;
counter++;
}
tbody += "</tbody></table> <br/>";
tbody += "<div style='margin-right:10px; margin-left:10px;'>";
String sthead = "";
String summary = "<table class='table' style='font-size:16px; width:90%;'>";
String stbody = "<tbody>";
sthead += "<tr>";
sthead += "<th>امضأ و نام مکمل کارمند</th>";
sthead += "<th>امضأ و نام مکمل آمر بخش</th>";
sthead += "<th>امضأ و نام مکمل تسلیم شونده</th>";
sthead += "</tr>";
sthead += "</thead>";
summary += sthead;
stbody += "<tr style='height:100px;'>";
stbody += "<th></th>";
stbody += "<th></th>";
stbody += "<th></th>";
stbody += "</tr>";
summary += stbody;
summary += "</table>";
tbody += summary;
tbody += "</div>";
tbody += "</page>";
html = html.Replace("$body", tbody);
}
else if (type.Equals("printedlist"))
{
string subpath = "RegistryServices_stc_vStockOut";
string[] subPathArray = subpath.Split('_');
string subKeyCol = "ID";
System.Data.Common.DbDataReader subReader = plus.Data.DAL.Read(subPathArray[0],
string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", subPathArray[1], subPathArray[2], subKeyCol)
, "RecordID", recordid);
String tbody = " <page size='A4' class='Print'>";
tbody += "<table class='table'>";
String thead = "";
thead += "<thead>";
thead += "<tr>";
thead += "<th colspan='10' style='border:none; font-size:16px;'> گزارش پاسپورت های چاپ شده از نمبر ($StartSerial) الی نمبر ($EndSerial) <br/> که به تاریخ $Date در جمع $FullName ثبت گردیده</th>";
thead += "</tr>";
thead += "<tr>";
thead += "<th> شماره</th>";
thead += "<th> نمبر پاسپورت</th>";
thead += "<th> نام متقاضی</th>";
thead += "<th> پاسپورت</th>";
thead += "<th>درخواست</th>";
thead += "<th> جریمه</th>";
thead += "<th> مدت</th>";
thead += "<th> پرداخت</th>";
thead += "<th>قیمت</th>";
thead += "<th> تاریخ صدور</th>";
thead += "<th> حالت</th>";
thead += "</tr>";
thead += "</thead>";
while (subReader.Read())
{
for (int i = 0; i < subReader.FieldCount; i++)
{
string subColName = subReader.GetName(i);
thead = thead.Replace("$" + subColName, Convert.ToString(subReader[subColName]));
}
}
tbody += thead;
tbody += "<tbody>";
int counter = 1;
while (reader.Read())
{
string trow = "<tr>";
trow += "<td>$NO</td>";
trow += "<td>$PassportNumber</td>";
trow += "<td>$FullName</td>";
trow += "<td>$PassportType</td>";
trow += "<td>$ApplicationType</td>";
trow += "<td>$FineName</td>";
trow += "<td>$DurationName</td>";
trow += "<td>$PaymentType</td>";
trow += "<td>$PaymentPrice</td>";
trow += "<td>$IssueDate_Shamsi</td>";
trow += "<td>$StatusName</td>";
trow += "</tr>";
trow = trow.Replace("$NO", counter.ToString());
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i);
trow = trow.Replace("$" + colName, Convert.ToString(reader[colName]));
}
tbody += trow;
counter++;
}
tbody += "</tbody></table> <br/>";
tbody += "<div style='margin-right:20px; margin-left:20px;'>";
System.Data.Common.DbDataReader summaryReader = plus.Data.DAL.Read(subPathArray[0],
string.Format("exec [rpt].[spGetPassportCountReportByStockOutId] {0}, {1}, {2}", 1, recordid, "Status")
, "RecordID", recordid);
String sthead = "";
String summary = "<table class='table' style='width:90%;'>";
String stbody = "<tbody>";
sthead += "<thead>";
sthead += "<tr>";
sthead += "<th colspan='10' style='border:none; font-size:16px;'>خلص گزارش پاسپورت های چاپ شده نظر به مقدار پرداخت</th>";
sthead += "</tr>";
sthead += "<tr>";
sthead += "<th>نوعیت پرداخت</th>";
sthead += "<th> مقدار پرداخت</th>";
sthead += "<th> باطل شده</th>";
sthead += "<th>تائید شده</th>";
sthead += "<th>مجموع چاپ شده</th>";
sthead += "</tr>";
sthead += "</thead>";
summary += sthead;
while (summaryReader.Read())
{
stbody += "<tr>";
stbody += "<th>@PaymentType</th>";
stbody += "<th>@PaymentPrice</th>";
stbody += "<th>@SPOILED</th>";
stbody += "<th>@PRINTED</th>";
stbody += "<th>@TOTAL</th>";
stbody += "</tr>";
for (int i = 0; i < summaryReader.FieldCount; i++)
{
string colName = summaryReader.GetName(i);
stbody = stbody.Replace("@" + colName, Convert.ToString(summaryReader[colName]));
}
}
summary += stbody;
summary += "</table>";
tbody += summary;
tbody += "</div>";
tbody += "</page>";
html = html.Replace("$body", tbody);
}
}
else if (type.Equals("closedlist"))
{
string start = Request.QueryString["start"];
string end = Request.QueryString["end"];
string starts = Request.QueryString["starts"];
string ends = Request.QueryString["ends"];
string postofficename = Request.QueryString["postofficename"];
reader = plus.Data.DAL.Read(pathArray[0],
string.Format("SELECT * FROM {0}.{1} WHERE ClosedDate BETWEEN '{2}' AND '{3}' AND PostOfficeID = @PostOffice", pathArray[1], pathArray[2], start, end)
, "PostOffice", recordid);
String tbody = " <page size='A4' class='Print'>";
tbody += "<table class='table'>";
String thead = "";
thead += "<thead>";
thead += "<tr>";
thead += "<th colspan='10' style='border:none; font-size:16px;'>گزارش درخواست های مربوط پسته خانه $PostOffice که از تاریخ $starts الی تاریخ $ends تکمیل و نهائی گردیده اند</th>";
thead += "</tr>";
thead += "<tr>";
thead += "<th> شماره</th>";
thead += "<th> نمبر پاسپورت</th>";
thead += "<th> نام متقاضی</th>";
thead += "<th> نام پدر</th>";
thead += "<th> نام پدر کلان</th>";
thead += "<th>محل تولد</th>";
thead += "<th>پوسته خانه</th>";
thead += "<th>نمبر تلفون</th>";
thead += "<th>ملاحضات</th>";
thead += "</tr>";
thead += "</thead>";
thead = thead.Replace("$PostOffice", postofficename);
thead = thead.Replace("$starts", starts);
thead = thead.Replace("$ends", ends);
tbody += thead;
tbody += "<tbody>";
int counter = 1;
while (reader.Read())
{
string trow = "<tr>";
trow += "<td>$NO</td>";
trow += "<td>$PassportNumber</td>";
trow += "<td>$FullName</td>";
trow += "<td>$FatherNameLocal</td>";
trow += "<td>$GrandFatherNameLocal</td>";
trow += "<td>$LocationName</td>";
trow += "<td>$PostOffice</td>";
trow += "<td>$Mobile</td>";
trow += "<td style='width:150px;'></td>";
trow += "</tr>";
trow = trow.Replace("$NO", counter.ToString());
for (int i = 0; i < reader.FieldCount; i++)
{
string colName = reader.GetName(i);
trow = trow.Replace("$" + colName, Convert.ToString(reader[colName]));
}
tbody += trow;
counter++;
}
tbody += "</tbody>";
tbody += "</page>";
html = html.Replace("$body", tbody);
}
reader.Close();
Response.Write(html);
}
}
/// <summary>
/// 根据模板将datareaser数据导成过滤指定列的EXCEL格式
/// </summary>
/// <param name="dataReader">数据集</param>
/// <param name="strFilePath">模板文件路径</param>
/// <param name="intStartRow">起始行</param>
/// <param name="strFileFullPath">全路径</param>
/// <param name="arrFilterColumn">过滤指定列，列名（别名）列表</param>
public static void ExportExcelByTemplate(DbDataReader dataReader, string strFilePath, int intStartRow, string strFileFullPath, string[] arrFilterColumn)
{
Workbook excelTemplate = new Workbook();
Workbook excel = new Workbook();
//建立excel并打开模板文件
excelTemplate.Open(strFilePath);
int intSheetRowCount = 0;
int intSheetCount = 0;
excel.Worksheets[intSheetRowCount].Copy(excelTemplate.Worksheets[0]);
Cells cells = excel.Worksheets[intSheetCount].Cells;// sheet.Cells;
Object[] arrObj = new Object[dataReader.FieldCount];//
List<string> list = null;
if (arrFilterColumn != null)
{
list = new List<string>(arrFilterColumn);//过滤指定列，列名（别名）列表
}
while (dataReader.Read())
{
if (intSheetRowCount == IntPageSize)
{
excel.Worksheets.Add();
intSheetCount += 1;
excel.Worksheets[intSheetCount].Copy(excelTemplate.Worksheets[0]);
intSheetRowCount = 0;
cells = excel.Worksheets[intSheetCount].Cells;
}
if (arrFilterColumn != null)
{
ArrayList arrList = new ArrayList();
for (int i = 0; i < dataReader.FieldCount; i++)
{
if (!Array.Exists(list.ToArray(), p =>
{
return p.Equals(dataReader.GetName(i),
StringComparison.OrdinalIgnoreCase);
}))
{
arrList.Add(dataReader.GetValue((i)));
}
}
Object[] arrObjTmp = arrList.ToArray();
cells.ImportObjectArray(arrObjTmp, intStartRow + intSheetRowCount, 0, false);
}
else
{
dataReader.GetValues(arrObj);
cells.ImportObjectArray(arrObj, intStartRow + intSheetRowCount, 0, false);
}
intSheetRowCount += 1;
}
excel.Worksheets.Add();
excel.Save(strFileFullPath, SaveFormat.Auto);
}
private static ColumnMetadata[] GetTableMetadata(DbDataReader reader)
{
var columnMetadata = new ColumnMetadata[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
columnMetadata[i] =
new ColumnMetadata(
reader.GetName(i), reader.GetDataTypeName(i), reader.GetFieldType(i));
}
return columnMetadata;
}
public void ExportExcel()
{
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample1");
ws.View.RightToLeft = true;
System.Data.Common.DbDataReader reader = null;
String TypeString = Request.QueryString["Type"];
String path = Request.QueryString["Path"].Split('.')[0];
if (TypeString.Equals("timesheet"))
{
System.Collections.IDictionary param = new Dictionary <string, object>();
var UserID = Request.QueryString["userID"];
var OrgUnit = Request.QueryString["orgID"];
var start = Request.QueryString["start"];
var end = Request.QueryString["end"];
var rank = Request.QueryString["rank"];
param.Add("ID", null);
param.Add("OrganizationUnitID", null);
param.Add("StartDate", null);
param.Add("EndDate", null);
param.Add("Rank", null);
if (UserID.Length != 0)
{
param["ID"] = UserID;
}
if (OrgUnit.Length != 0)
{
param["OrganizationUnitID"] = OrgUnit;
}
if (start.Length != 0)
{
param["StartDate"] = start;
}
if (end.Length != 0)
{
param["EndDate"] = end;
}
if (rank.Length != 0)
{
param["Rank"] = rank;
}
param.Add("CreatedBy", 1);
param.Add("Offset", 0);
param.Add("Limit", 0);
int typeID = 0;
try
{
typeID = Convert.ToInt32(Request.QueryString["typeID"]);
}
catch (Exception) { }
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[path].ToString());
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "att.spGetMonthlyTimeSheet";
foreach (var ent in param.Keys)
{
command.Parameters.Add(new SqlParameter(ent.ToString(), param[ent]));
}
command.CommandTimeout = 200;
reader = command.ExecuteReader();
};
//reader = plus.Data.DAL.Read("AMS", "att.spGetMonthlyTimeSheet",param);
Dictionary <int, String> maps = new Dictionary <int, string>();
maps.Add(2, "PR");
maps.Add(3, "AB");
maps.Add(4, "HD");
maps.Add(5, "ALH");
maps.Add(6, "AL");
StringBuilder sb = new StringBuilder();
sb.Append("<thead>");
sb.Append("<tr>");
sb.Append("<th></th><th></th><th></th><th></th><th></th><th></th>");
char a = 'A';
ws.Cells[a + "" + 1].Value = "کود"; a++;
ws.Cells[a + "" + 1].Value = "نام مکمل"; a++;
ws.Cells[a + "" + 1].Value = "نام پدر"; a++;
ws.Cells[a + "" + 1].Value = "وظیفه"; a++;
ws.Cells[a + "" + 1].Value = "بست"; a++;
ws.Cells[a + "" + 1].Value = "اداره"; a++;
char b = ' ';
for (int i = 0; i < reader.FieldCount; i++)
{
if (i > 6)
{
if (a > 'Z')
{
b = 'A';
a = 'A';
}
String val = reader.GetName(i).Split('_')[2];
ws.Cells[b + "" + a + "" + 1].Value = val;
a++;
}
}
int g = 2;
while (reader.Read())
{
a = 'A';
b = ' ';
for (int i = 1; i < reader.FieldCount; i++)
{
if (i < 7 || (typeID == 0 || typeID == 1))
{
String val = reader[i].ToString();
ws.Cells[b + "" + a + "" + g].Value = val;
}
else
{
string s = reader[i].ToString();
if (maps[typeID].Equals(s))
{
String val = reader[i].ToString();
ws.Cells[b + "" + a + "" + g].Value = val;
}
else
{
}
}
a++;
if (a > 'Z')
{
a = 'A';
b = 'A';
}
}
g++;
}
conn.Close();
}
else if (TypeString.Equals("attreport"))
{
int typeID = 0;
try
{
typeID = Convert.ToInt32(Request.QueryString["typeID"]);
}
catch (Exception) { }
System.Collections.IDictionary param = new Dictionary <string, object>();
var UserID = Request.QueryString["userID"];
var OrgUnit = Request.QueryString["orgID"];
var start = Request.QueryString["start"];
var end = Request.QueryString["end"];
var rank = Request.QueryString["rank"];
param.Add("UserID", null);
param.Add("OrganizationUnit", null);
param.Add("StartDate", null);
param.Add("EndDate", null);
param.Add("RankID", null);
param.Add("AttID", null);
param.Add("HRCode", null);
param.Add("CreatedBy", null);
param.Add("Type", null);
if (UserID.Length != 0)
{
param["UserID"] = UserID;
}
if (OrgUnit.Length != 0)
{
param["OrganizationUnit"] = OrgUnit;
}
if (start.Length != 0)
{
param["StartDate"] = start;
}
if (end.Length != 0)
{
param["EndDate"] = end;
}
if (rank.Length != 0)
{
param["RankID"] = rank;
}
if (typeID != 0)
{
param["Type"] = typeID;
}
reader = plus.Data.DAL.Read("AMS", "att.spGenerateTimeSheet", param);
StringBuilder sb = new StringBuilder();
char a = 'A';
ws.Cells[a + "" + 1].Value = " کود کادری "; a++;
ws.Cells[a + "" + 1].Value = "نام مکمل"; a++;
ws.Cells[a + "" + 1].Value = "نام پدر"; a++;
ws.Cells[a + "" + 1].Value = "وظیفه"; a++;
ws.Cells[a + "" + 1].Value = "بست"; a++;
ws.Cells[a + "" + 1].Value = "ریاست"; a++;
ws.Cells[a + "" + 1].Value = "شیفت"; a++;
ws.Cells[a + "" + 1].Value = "تاریخ"; a++;
ws.Cells[a + "" + 1].Value = "وقت دخول"; a++;
ws.Cells[a + "" + 1].Value = "وقت خروج"; a++;
ws.Cells[a + "" + 1].Value = "حاضری"; a++;
char b = ' ';
int g = 2;
while (reader.Read())
{
a = 'A';
b = ' ';
for (int i = 1; i < reader.FieldCount; i++)
{
if (!reader.GetName(i).Equals("AttType"))
{
ws.Cells[b + "" + a + "" + g].Value = reader[i].ToString();
}
a++;
if (a > 'Z')
{
a = 'A';
b = 'A';
}
}
g++;
}
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
pck.SaveAs(Response.OutputStream);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Sample1.xlsx");
}
private static DataTable FillColumns(DbDataReader reader)
{
var xdata = new DataTable();
Console.WriteLine("FillColumns " + new { reader.FieldCount });
for (int i = 0; i < reader.FieldCount; i++)
{
var columName = reader.GetName(i);
Console.WriteLine("FillColumns " + new { i, columName });
xdata.Columns.Add(columName);
}
return xdata;
}
public static bool HasName(this DbDataReader reader, string columnName) => Enumerable.Range(0, reader.FieldCount) .Any(i => string.Equals(reader.GetName(i), columnName, StringComparison.OrdinalIgnoreCase));
/// <summary>
/// Requires: a public type with a public, default constructor. Returns a column map initializing the type
/// and all properties of the type with a public setter taking a primitive type and having a corresponding
/// column in the reader.
/// </summary>
internal static CollectionColumnMap CreateColumnMapFromReaderAndClrType(DbDataReader reader, Type type, MetadataWorkspace workspace)
{
Debug.Assert(null != reader);
Debug.Assert(null != type);
Debug.Assert(null != workspace);
// we require a default constructor
ConstructorInfo constructor = type.GetConstructor(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance,
null, Type.EmptyTypes, null);
if (type.IsAbstract || (null == constructor && !type.IsValueType))
{
throw EntityUtil.InvalidOperation(
Strings.ObjectContext_InvalidTypeForStoreQuery(type));
}
// build a LINQ expression used by result assembly to create results
var memberInfo = new List<Tuple<MemberAssignment, int, EdmProperty>>();
foreach (PropertyInfo prop in type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance))
{
// for enums unwrap the type if nullable
var propertyUnderlyingType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
Type propType = propertyUnderlyingType.IsEnum ? propertyUnderlyingType.GetEnumUnderlyingType() : prop.PropertyType;
EdmType modelType;
int ordinal;
if (TryGetColumnOrdinalFromReader(reader, prop.Name, out ordinal) &&
MetadataHelper.TryDetermineCSpaceModelType(propType, workspace, out modelType) &&
(Helper.IsScalarType(modelType)) &&
prop.CanWrite && prop.GetIndexParameters().Length == 0 && null != prop.GetSetMethod(/* nonPublic */true))
{
memberInfo.Add(Tuple.Create(
Expression.Bind(prop, Expression.Parameter(prop.PropertyType, "placeholder")),
ordinal,
new EdmProperty(prop.Name, TypeUsage.Create(modelType))));
}
}
// initialize members in the order in which they appear in the reader
MemberInfo[] members = new MemberInfo[memberInfo.Count];
MemberBinding[] memberBindings = new MemberBinding[memberInfo.Count];
ColumnMap[] propertyMaps = new ColumnMap[memberInfo.Count];
EdmProperty[] modelProperties = new EdmProperty[memberInfo.Count];
int i = 0;
foreach (var memberGroup in memberInfo.GroupBy(tuple => tuple.Item2).OrderBy(tuple => tuple.Key))
{
// make sure that a single column isn't contributing to multiple properties
if (memberGroup.Count() != 1)
{
throw EntityUtil.InvalidOperation(Strings.ObjectContext_TwoPropertiesMappedToSameColumn(
reader.GetName(memberGroup.Key),
String.Join(", ", memberGroup.Select(tuple => tuple.Item3.Name).ToArray())));
}
var member = memberGroup.Single();
MemberAssignment assignment = member.Item1;
int ordinal = member.Item2;
EdmProperty modelProp = member.Item3;
members[i] = assignment.Member;
memberBindings[i] = assignment;
propertyMaps[i] = new ScalarColumnMap(modelProp.TypeUsage, modelProp.Name, 0, ordinal);
modelProperties[i] = modelProp;
i++;
}
NewExpression newExpr = null == constructor ? Expression.New(type) : Expression.New(constructor);
MemberInitExpression init = Expression.MemberInit(newExpr, memberBindings);
InitializerMetadata initMetadata = InitializerMetadata.CreateProjectionInitializer(
(EdmItemCollection)workspace.GetItemCollection(DataSpace.CSpace), init, members);
// column map (a collection of rows with InitializerMetadata markup)
RowType rowType = new RowType(modelProperties, initMetadata);
RecordColumnMap rowMap = new RecordColumnMap(TypeUsage.Create(rowType),
"DefaultTypeProjection", propertyMaps, null);
CollectionColumnMap collectionMap = new SimpleCollectionColumnMap(rowType.GetCollectionType().TypeUsage,
rowType.Name, rowMap, null, null);
return collectionMap;
}
public static dynamic consulta(string query, bool tipoSelect = false, bool eliminando = false)
{
var consulta = new List <Dictionary <string, object> >();
try
{
conexion.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, conexion);
if (!tipoSelect)
{
System.Data.Common.DbDataReader datos = cmd.ExecuteReader();
while (datos.Read())
{
Dictionary <string, object> objeto = new Dictionary <string, object>();
for (int x = 0; x < datos.FieldCount; x++)
{
objeto.Add(datos.GetName(x), datos.GetValue(x));
}
consulta.Add(objeto);
}
// consulta.Clear();
// consulta = new List<Dictionary<string, object>>();
}
else
{
try
{
cmd.ExecuteNonQuery();
conexion.Close();
cmd.Dispose();
return(true);
}
catch (Exception e)
{
if (eliminando)
{
return(false);
}
MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conexion.Close();
return(false);
}
}
conexion.Close();
}
catch (Exception e)
{
if (eliminando)
{
return(false);
}
MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
conexion.Close();
}
return(consulta);
}
