public static DataTable ProcessObjects(object[,] valueArray)
{
var dt = new System.Data.DataTable();
#region Get the COLUMN names
for (int k = 1; k <= valueArray.GetLength(1); k++)
{
dt.Columns.Add((string)valueArray[1, k]); //add columns to the data table.
}
#endregion
#region Load Excel SHEET DATA into data table
object[] singleDValue = new object[valueArray.GetLength(1)];
//value array first row contains column names. so loop starts from 2 instead of 1
for (int i = 2; i <= valueArray.GetLength(0); i++)
{
for (int j = 0; j < valueArray.GetLength(1); j++)
{
if (valueArray[i, j + 1] != null)
{
singleDValue[j] = valueArray[i, j + 1].ToString();
}
else
{
singleDValue[j] = valueArray[i, j + 1];
}
}
dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges);
}
#endregion
return (dt);
}
protected DataRow FindRowByKey(DataObject dataObject)
{
DataRow row;
string tableName = dataObject.TableName;
System.Data.DataTable table = GetDataSet(tableName).Tables[tableName];
Type myType = dataObject.GetType();
string key = table.PrimaryKey[0].ColumnName;
if (key.Equals(tableName + "_ID"))
{
row = table.Rows.Find(dataObject.ObjectId);
}
else
{
MemberInfo[] keymember = myType.GetMember(key);
object val = null;
if (keymember[0] is PropertyInfo)
{
val = ((PropertyInfo)keymember[0]).GetValue(dataObject, null);
}
if (keymember[0] is FieldInfo)
{
val = ((FieldInfo)keymember[0]).GetValue(dataObject);
}
if (val != null)
{
row = table.Rows.Find(val);
}
else
{
return(null);
}
}
return(row);
}
private static DataTable ReadFile(string path)
{
System.Data.DataTable table = new System.Data.DataTable("dataFromFile");
table.Columns.AddRange(new DataColumn[]
{
new DataColumn("col1", typeof(string))
});
using (StreamReader sr = new StreamReader(path))
{
string line;
while ((line = sr.ReadLine()) != null)
{
DataRow tempRw = table.NewRow();
tempRw["col1"] = line;
table.Rows.Add(tempRw);
}
}
return(table);
}
public System.Data.DataTable GetData(string pSheetName)
{
int eachRow = 50000;
System.Data.DataTable dt = null;
ws = wb.Worksheets[pSheetName];
Range rngBegin, rngEnd, rngCell;
int colNum = ws.UsedRange.CurrentRegion.Columns.Count;
int rowNum = ws.UsedRange.CurrentRegion.Rows.Count;
int beginRow = 1;
for (int endRow = 0; endRow < rowNum;)
{
endRow = (endRow + eachRow) > rowNum ? rowNum : (endRow + eachRow);
rngBegin = ws.Cells[beginRow, 1];
rngEnd = ws.Cells[endRow, colNum];
rngCell = ws.Range[rngBegin, rngEnd];
object[,] obj = (object[, ])rngCell.Value;
if (dt == null)
{
dt = ObjectHelper.ObjectToDataTable(obj);
}
else
{
System.Data.DataTable tempDT = dt.Clone();
tempDT = ObjectHelper.ObjectToDataTable(obj, false, tempDT);
foreach (DataRow item in tempDT.Rows)
{
dt.ImportRow(item);
}
}
Marshal.FinalReleaseComObject(rngBegin);
Marshal.FinalReleaseComObject(rngEnd);
Marshal.FinalReleaseComObject(rngCell);
beginRow = beginRow + eachRow;
}
return(dt);
}
DataTable AnalyzeSrcColumn(Worksheet sheet)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("�W��", typeof(string)));
table.Columns.Add(new DataColumn("����", typeof(int)));
string[] columns = CaseLib.GetHeaders(_sheet, 1);
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] == null || columns[i].Trim() == string.Empty)
continue;
DataRow row = table.NewRow();
row["�W��"] = columns[i];
row["����"] = i + 1;
table.Rows.Add(row);
}
return table;
}
DataTable AnalyzeSrcColumn(Worksheet sheet)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("名稱", typeof(string)));
table.Columns.Add(new DataColumn("索引", typeof(int)));
string[] columns = CaseLib.GetHeaders(_sheet, 1);
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] == null || columns[i].Trim() == string.Empty)
{
continue;
}
DataRow row = table.NewRow();
row["名稱"] = columns[i];
row["索引"] = i + 1;
table.Rows.Add(row);
}
return(table);
}
private static DataTable DefineDataTable(
DataObject[] objects)
{
var data = new System.Data.DataTable();
data.Columns.Add("DataObjectId", typeof(long));
data.PrimaryKey = new[] { data.Columns["DataObjectId"] };
data.DefaultView.Sort = "DataObjectId asc";
var row = objects.First();
var unionCols = row.Attributes.OrderBy(x => x.Id)
.ThenBy(x => x.Name)
.Select(x => x.Name);
foreach (var key in unionCols)
{
data.Columns.Add(key, Type);
}
data.Columns.Add(nameof(row.Class), Type);
return(data);
}
private void button6_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Sıra No");
dt.Columns.Add("Ulusal Yeterlilik Kodu");
dt.Columns.Add("ISCO 08");
dt.Columns.Add("Millî Eğitim Bakanlığına Bağlı Mesleki ve Teknik Eğitim Kurumlarınca Verilen Diplomalar (Alan/Dal/Bölüm-FOET Kodu)");
_Application excel = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = excel.Workbooks.Open(@"C:\Users\Lenovo\Desktop\Nota\MYK\Muafiyet_Tablosu_2020-Rev.04.xlsx");
Worksheet ws = wb.Worksheets[1];
for (int j = 2; j < 105; j++)
{
var SiraNo = ws.Cells[j, 1].Value;
var UYK = ws.Cells[j, 2].Value;
var ISCOkodu = ws.Cells[j, 3].Value;
var MEB = (string)ws.Cells[j, 4].Value;
string[] ayirici = new string[] { ")," };
var MeslekFoetListe = MEB.Split(ayirici, StringSplitOptions.None);
foreach (var item in MeslekFoetListe)
{
var MeslekFoet = item;
dt.Rows.Add(SiraNo, UYK, ISCOkodu, MeslekFoet);
}
}
dataGridView1.DataSource = dt;
}
public System.Data.DataTable ExportToExcel()
{
dtOrderNew = new System.Data.DataTable();
dtOrderNew.Columns.Add("STT", typeof(int));
dtOrderNew.Columns.Add("Ngày Nhập/ Xuất", typeof(string));
dtOrderNew.Columns.Add("Ký Hiệu", typeof(string));
dtOrderNew.Columns.Add("Mã Sản Phẩm", typeof(string));
dtOrderNew.Columns.Add("Nhập BTP Chưa in", typeof(int));
dtOrderNew.Columns.Add("Nhập BTP Đã in", typeof(int));
dtOrderNew.Columns.Add("Nhập Thành Phẩm", typeof(string));
dtOrderNew.Columns.Add("Nhập Sản phẩm lỗi", typeof(string));
dtOrderNew.Columns.Add("Xuất BTP Chưa in", typeof(string));
dtOrderNew.Columns.Add("Xuất BTP Đã in", typeof(string));
dtOrderNew.Columns.Add("Xuất Thành Phẩm", typeof(string));
dtOrderNew.Columns.Add("Xuất Sản phẩm lỗi", typeof(string));
DataRow row;
string ngaynhapxuat, kyhieu, masp, nhapchuain, nhapdain, nhaptp, nhapsploi, xuatchuain, xuatdain, xuatptp, xuatsploi;
for (int i = 0; i < dtMain.Rows.Count; i++)
{
ngaynhapxuat = dtMain.Rows[i][0].ToString();
kyhieu = dtMain.Rows[i][1].ToString();
masp = dtMain.Rows[i][2].ToString();
nhapchuain = dtMain.Rows[i][3].ToString();
nhapdain = dtMain.Rows[i][4].ToString();
nhaptp = dtMain.Rows[i][5].ToString();
nhapsploi = dtMain.Rows[i][6].ToString();
xuatchuain = dtMain.Rows[i][7].ToString();
xuatdain = dtMain.Rows[i][8].ToString();
xuatptp = dtMain.Rows[i][9].ToString();
xuatsploi = dtMain.Rows[i][10].ToString();
dtOrderNew.Rows.Add(i + 1, ngaynhapxuat, kyhieu, masp, nhapchuain, nhapdain, nhaptp, nhapsploi, xuatchuain, xuatdain, xuatptp, xuatsploi);
}
return(dtOrderNew);
}
static void Main(string[] args)
{
string applicationType = ConfigurationManager.AppSettings["ApplicationType"];
Console.WriteLine(" Results Data table added to Data Table .....!");
uploadUntaggedBugsDataToDb();
System.Data.DataTable dtTotalAppNames = connectAndGetDataFromDB("ApplicationList_Untagged", ConfigurationManager.AppSettings["ApplicationList_Untagged"], "3");
foreach (DataRow dr in dtTotalAppNames.Rows)
{
string recID = dr["RecID"].ToString();
string currentAppName = dr["AppName"].ToString();
DataSet ds1 = new DataSet("Results");
System.Data.DataTable applicationNameWiseData = connectAndGetDataFromDB("ApplicationNameWiseData_Untagged", ConfigurationManager.AppSettings["ApplicationNameWiseData_Untagged"].Replace("ApplicationName", currentAppName).Replace("RecordID", recID), "3");
Console.WriteLine(" RecID Added in Data table, {0}", recID);
ds1.Tables.Add(applicationNameWiseData);
Dictionary <String, List <String> > dataMapWithMails = SendMasBugsDataToIndividaulByTitle(applicationType, ds1, recID, currentAppName);
sendMail(dataMapWithMails, currentAppName, recID);
}
}
private void initDGV1()
{
System.Data.DataTable dt = new System.Data.DataTable();
DataColumn col1 = new DataColumn("columns", typeof(string));
DataColumn col2 = new DataColumn("04/02", typeof(int));
DataColumn col3 = new DataColumn("04/03", typeof(int));
dt.Columns.Add(col1);
dt.Columns.Add(col2);
dt.Columns.Add(col3);
DataRow dr1 = dt.NewRow();
DataRow dr2 = dt.NewRow();
DataRow dr3 = dt.NewRow();
DataRow dr4 = dt.NewRow();
dr1[0] = "Total";
dr1[1] = 1;
dr1[2] = 10;
dr2[0] = "割れ";
dr2[1] = 5;
dr2[2] = 5;
dr3[0] = "へこみ";
dr3[1] = 10;
dr3[2] = 1;
dr4[0] = "傷";
dr4[1] = 15;
dr4[2] = 8;
dt.Rows.Add(dr1);
dt.Rows.Add(dr2);
dt.Rows.Add(dr3);
dt.Rows.Add(dr4);
dataGridView1.DataSource = dt;
}
/// <summary>
/// Проверка формата колонок.
/// </summary>
/// <param name="errorMes">Список найденных ошибки</param>
/// <returns>Если ошибок нет, то результат true</returns>
private bool CheckFormatColumns(out string errorMes)
{
//Копируем DataTable. Все операции будем производить с копией.
System.Data.DataTable dt2 = sys.DataTable_To_DataTable(dt);
errorMes = "";
int columnCount = dt.Columns.Count;
string[,] arrErrors = new string[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < importDT.Rows.Count; i++)
{
string columnType = importDT.Value("ColumnType");
string columnFormat = importDT.Value("ColumnFormat");
string defaultValue = importDT.Value("DefaultValue");
string tryParse = importDT.Value("TryParse");
string setValue = importDT.Value("SetValue");
string setCharCase = importDT.Value("SetCharCase");
string minValue = importDT.Value("MinValue");
string maxValue = importDT.Value("MaxValue");
string deleteChars = importDT.Value("DeleteChars");
string leaveChars = importDT.Value("LeaveChars");
//Это для ускорения работы, чтобы каждый раз не переводить minValue в дату для стравнения в цикле.
DateTime minValueDate = DateTime.Now; //Это по умолчанию
DateTime maxValueDate = DateTime.Now; //Это по умолчанию
TimeSpan minValueTime = DateTime.Now.TimeOfDay; //Это по умолчанию
TimeSpan maxValueTime = DateTime.Now.TimeOfDay; //Это по умолчанию
if ((minValue != ""))
{
if (columnFormat == "date")
{
minValueDate = DateTime.ParseExact(minValue, "dd.MM.yyyy", null);
}
if (columnFormat == "time")
{
minValueTime = TimeSpan.ParseExact(minValue, "hh:mm:ss", null);
}
}
if ((maxValue != ""))
{
if (columnFormat == "date")
{
maxValueDate = DateTime.ParseExact(maxValue, "dd.MM.yyyy", null);
}
if (columnFormat == "time")
{
maxValueTime = TimeSpan.ParseExact(maxValue, "hh:mm:ss", null);
}
}
if (i >= columnCount)
{
break;
}
//Выходим, если в настроечной таблице, колонок больше, чем в проверяемой. например есть необязательные колонки.
//Их нет, поэтому проверять нечего.
if ((dt2.Columns.Count - 1) < i)
{
break;
}
//Одна колонка importDT соответствует одной колонке dt. Поэтому идем по значениям колонки dt.
for (int j = 0; j < dt2.Rows.Count; j++)
{
string value = dt2.Value(j, i);
string newvalue = "";
arrErrors[j, i] = CheckFormatValue(columnType,
columnFormat,
defaultValue,
tryParse,
setValue,
setCharCase,
minValue,
maxValue,
minValueDate,
maxValueDate,
minValueTime,
maxValueTime,
deleteChars,
leaveChars,
value,
out newvalue);
if (newvalue != "")
{
dt.Rows[j][i] = newvalue;
}
}
}
return(errorMes == "");
}
static void Main(string[] args)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
string clientNumber = "";
try
{
string[] arrayColumnNames = null;
string path = @"C:\Users\UlysesRico.Rea\Documents\NotestoDocument\";
string excelFile = path + "SampleBookforWKClark.xlsx";
//Excel connection strings reference: https://www.connectionstrings.com/excel-2013/
//Extenden Properties: HDR Yes, the first rows contain column names
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow row in dt.Rows)
{
string sheet = row["TABLE_NAME"].ToString();
//I take out the $ sign from sheet
string strSheet = sheet.Replace("$", String.Empty);
OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheet + "]", objConn);
DataTable excelDataTable = new DataTable();
objDA.Fill(excelDataTable);
//Get the columns of the sheets, as they are the same for every tab then it can be fetch once.
//This works just having the condition every tab has the same columns, if not, this array has to be filled every time it steps in the sheet loop.
if (arrayColumnNames == null)
{
arrayColumnNames = new string[excelDataTable.Columns.Count];
int i = 0;
foreach (DataColumn col in excelDataTable.Columns)
{
arrayColumnNames[i] = col.ColumnName;
i++;
}
}
//Read each row from the current Sheet
foreach (DataRow rowSheet in excelDataTable.Rows)
{
clientNumber = rowSheet["Client Number"].ToString();
string clientFilePath = path + clientNumber + ".xlsx";
string strClientFileConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + clientFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection clientFileConnection = new OleDbConnection(strClientFileConnection);
string strCreateTable = "create table [" + strSheet + "] ( [" + arrayColumnNames[0] + "] VarChar (100),[" + arrayColumnNames[1] + "] VarChar (100),[" + arrayColumnNames[2] + "] VarChar (100),[" + arrayColumnNames[3] + "] VarChar (100),[" + arrayColumnNames[4] + "] VarChar (100),[" + arrayColumnNames[5] + "] VarChar (200) )";
string strinsertQuery = "insert into [" + sheet + "] ([" + arrayColumnNames[0] + "],[" + arrayColumnNames[1] + "],[" + arrayColumnNames[2] + "],[" + arrayColumnNames[3] + "],[" + arrayColumnNames[4] + "],[" + arrayColumnNames[5] + "])" + " values (@clientnumber,@clientname,@engType,@workflow,@noteType,@note)";
OleDbCommand cmdCreateTable = new OleDbCommand(strCreateTable, clientFileConnection);
OleDbCommand cmdInsertStm = new OleDbCommand();
cmdInsertStm.Connection = clientFileConnection;
cmdInsertStm.CommandText = strinsertQuery;
cmdInsertStm.Parameters.AddRange(
new OleDbParameter[] {
new OleDbParameter("@clientnumber", rowSheet[arrayColumnNames[0]]),
new OleDbParameter("@clientname", rowSheet[arrayColumnNames[1]]),
new OleDbParameter("@engType", rowSheet[arrayColumnNames[2]]),
new OleDbParameter("@workflow", rowSheet[arrayColumnNames[3]]),
new OleDbParameter("@noteType", rowSheet[arrayColumnNames[4]]),
new OleDbParameter("@note", rowSheet[arrayColumnNames[5]])
}
);
DataTable dtSheets = null;
/*
* Check of the excel file is created
*/
if (File.Exists(clientFilePath))
{
//Check if the sheet exists
if (clientFileConnection.State != ConnectionState.Open)
{
clientFileConnection.Open();
}
dtSheets = clientFileConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
Boolean sheetFound = false;
foreach (DataRow osheet in dtSheets.Rows)
{
if (osheet["TABLE_NAME"].ToString() == sheet)
{
sheetFound = true;
}
}
if (sheetFound)
{
executeCommand(clientFileConnection, cmdInsertStm);
}
else
{
executeCommand(clientFileConnection, cmdCreateTable);
executeCommand(clientFileConnection, cmdInsertStm);
}
}
else
{
executeCommand(clientFileConnection, cmdCreateTable);
executeCommand(clientFileConnection, cmdInsertStm);
}
}
}
//EnForEach of sheets
}
catch (Exception ex)
{
Console.WriteLine("Client : " + clientNumber + "-->Exception:" + ex.ToString());
Console.ReadKey();
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
/// <summary>
/// 將DataTable貼到指定的Worksheet
/// </summary>
public static void PasteDataTable(Worksheet sheet, System.Data.DataTable table, PasteDataTableOptions pasteOptions)
{
int row = pasteOptions.Row < 1?1:pasteOptions.Row;
int col = pasteOptions.Column < 1?1:pasteOptions.Column;
int writeRow = row;
for (int i = 0; i < table.Columns.Count; i++)
{
DataColumn column = table.Columns[i];
sheet.Cells[row, col + i] = column.ColumnName;
}
writeRow++;
if (pasteOptions.IncludeFormula)
{
char prefix = '=';
foreach (DataRow dr in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
if (dr[i].ToString()[0] == prefix)
{
((Range)sheet.Cells[writeRow, col + i]).Formula = dr[i];
}
else
{
sheet.Cells[writeRow, col + i] = dr[i];
}
}
writeRow++;
}
}
else
{
foreach (DataRow dr in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
sheet.Cells[writeRow, col + i] = dr[i];
}
writeRow++;
}
}
//設定外觀樣式
Range writeRange = sheet.get_Range(sheet.Cells[row, col], sheet.Cells[row + table.Rows.Count, col + table.Columns.Count - 1]);
Range headerRange = sheet.get_Range(sheet.Cells[row, col], sheet.Cells[row, col + table.Columns.Count - 1]);
SetBorder(writeRange, XlLineStyle.xlContinuous, XlBorderWeight.xlThin, true, true, true, true);
headerRange.Font.Bold = pasteOptions.ColumnHeaderBold;
if (pasteOptions.ColumnHeaderButtomBorder)
{
SetBorder(headerRange, XlBordersIndex.xlEdgeBottom, XlLineStyle.xlContinuous, XlBorderWeight.xlThin);
}
if (pasteOptions.SheetName != null)
{
sheet.Name = pasteOptions.SheetName;
}
}
public FeatureClassProperties(MSCFeatureClass fc)
{
try
{
this._mColorList = this.InitColorList();
this._FC = fc;
this._QueryData = new DataTable();
this._QueryData.Columns.Add("Type");
this._QueryData.Columns.Add("Value");
if (fc.Query != null)
{
TypedValue[] array = fc.Query.AsArray();
TypedValue[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
TypedValue typedValue = array2[i];
if (typedValue.TypeCode == 62)
{
this.BuildColorRow(typedValue.Value.ToString());
}
else
{
DataRow dataRow = this._QueryData.NewRow();
dataRow["Type"] = new DXFCode((int)typedValue.TypeCode).CodeString;
dataRow["Value"] = typedValue.Value;
this._QueryData.Rows.Add(dataRow);
}
}
}
this._QueryData.RowChanged += new DataRowChangeEventHandler(this._QueryData_RowChanged);
this._FieldData = new DataTable();
this._FieldData.Columns.Add("Name");
this._FieldData.Columns.Add("Type");
this._FieldData.Columns.Add("Value");
this._FieldData.Columns.Add("DisplayValue");
this._FieldData.Columns.Add("Length", typeof(int));
this._FieldData.Columns.Add("ReadOnly", typeof(bool));
this._FieldData.Columns.Add("BaseField", typeof(CadField));
this._FieldData.Columns.Add("CodedValues", typeof(List <MSCCodedValue>));
this._FieldData.Columns.Add("CodedValue", typeof(MSCCodedValue));
this._FieldData.Columns.Add("Domain", typeof(FieldDomain));
this._FieldData.Columns.Add("CanEditType", typeof(bool));
if (fc.Fields != null)
{
foreach (CadField current in fc.Fields)
{
if (current != null && current.Visible)
{
DataRow dataRow2 = this._FieldData.NewRow();
dataRow2["Name"] = current.Name;
dataRow2["Value"] = current.Value.Value;
dataRow2["DisplayValue"] = dataRow2["Value"];
if (current.Value.TypeCode == 1)
{
dataRow2["Length"] = current.Length;
}
else
{
dataRow2["Length"] = 0;
}
dataRow2["Type"] = current.GetTypeString();
if (current.ReadOnly)
{
dataRow2["ReadOnly"] = true;
}
else
{
dataRow2["ReadOnly"] = false;
}
dataRow2["BaseField"] = current;
this._FieldData.Rows.Add(dataRow2);
if (current.Domain != null)
{
dataRow2["Domain"] = current.Domain;
}
dataRow2["CanEditType"] = true;
if (current.Domain != null)
{
dataRow2["CanEditType"] = false;
current.Value = current.Domain.CheckValue(current.Value.Value);
dataRow2["CodedValues"] = current.Domain.CodedValuesDisplayList;
dataRow2["CodedValue"] = current.Domain.GetCodedValue(current.Value.Value);
dataRow2["DisplayValue"] = dataRow2["CodedValue"];
}
}
}
}
this._FieldData.RowChanged += new DataRowChangeEventHandler(this._FieldData_RowChanged);
this.InitializeComponent();
this.lblName.Content = fc.Name;
this.lblType.Content = MSCFeatureClass.GetTypeCodeString(fc.GeometryType);
this.dgQuery.ItemsSource = this.QueryData.DefaultView;
this.BuildQueryPropertyOptions(fc.GeometryType);
this.dgFields.ItemsSource = this._FieldData.DefaultView;
if (fc.ReadOnly || fc.IsSubType || fc.SubTypes.Count > 0)
{
this.dgQuery.IsEnabled = false;
this.dgFields.IsEnabled = false;
this.cbProperty.IsEnabled = false;
this.cbAppend.IsEnabled = false;
this.cbValue.IsEnabled = false;
}
}
catch (Exception ex)
{
ErrorReport.ShowErrorMessage(ex.Message);
}
}
void InitReportTable()
{
ReportDataSetTableAdapters.UnitPriceReportTableAdapter adapter = new Mong.ReportDataSetTableAdapters.UnitPriceReportTableAdapter();
//取得基本報表資料
_table = adapter.GetData(_startDate, _endDate);
//取得工作單號,品號,實際工資表
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT 工作單.單號 AS 單號, 產品品號.品號, Year(工時.日期) AS 年份, Month(工時.日期) AS 月份, " +
"IIF(SUM(工時.工時) IS NULL , 0, SUM(工時.工時)) AS 實際工時," +
"IIF(SUM(工時.工時) IS NULL , 0, SUM(員工.薪水 * 工時.工時)) AS 實際工資 " +
"FROM ((((工作單 INNER JOIN 工作單品號 ON 工作單品號.單號 = 工作單.單號) " +
"INNER JOIN 產品品號 ON 工作單品號.品號 = 產品品號.品號) " +
"LEFT JOIN 工時 ON 工時.工作單號 = 工作單品號.單號 AND 工作單品號.編號 = 工時.工品編號) " +
"LEFT JOIN 員工 ON 工時.員工編號 = 員工.編號) " +
"WHERE 工作單.實際完成日 > #" + _startDate.ToString("yyyy/MM/dd") + "# AND 工作單.實際完成日 < #" + _endDate.ToString("yyyy/MM/dd") + "# " +
"GROUP BY 工作單.單號, 產品品號.品號, Year(工時.日期), Month(工時.日期)" +
"ORDER BY 工作單.單號, 產品品號.品號, Year(工時.日期), Month(工時.日期)";
cmd.Connection = adapter.Connection;
System.Data.DataTable baseTable = new System.Data.DataTable();
baseTable.Columns.Add(new DataColumn("單號", typeof(string)));
baseTable.Columns.Add(new DataColumn("品號", typeof(string)));
baseTable.Columns.Add(new DataColumn("實際工時", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("實際工資", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("年份", typeof(int)));
baseTable.Columns.Add(new DataColumn("月份", typeof(int)));
System.Data.OleDb.OleDbDataAdapter baseAdapter = new System.Data.OleDb.OleDbDataAdapter();
baseAdapter.SelectCommand = cmd;
baseAdapter.Fill(baseTable);
//取得日期範圍
int minYear, minMonth, maxYear, maxMonth;
DateTime minDate, maxDate;
object o;
o = baseTable.Compute("MIN(年份)", string.Empty);
minYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MIN(月份)", string.Empty);
minMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
o = baseTable.Compute("MAX(年份)", string.Empty);
maxYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MAX(月份)", string.Empty);
maxMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
minDate = new DateTime(minYear, minMonth, 1);
maxDate = new DateTime(maxYear, maxMonth, 1);
//取得每月工作時數
Dictionary <DateTime, decimal> workHoursDic = new Dictionary <DateTime, decimal>();
for (DateTime date = new DateTime(minDate.Year, minDate.Month, 1); date <= maxDate; date = date.AddMonths(1))
{
decimal hours = Global.GetWorkingHours(date.Year, date.Month);
workHoursDic.Add(date, hours);
}
//重新計算baseTable的實際工資
DateTime curRowMonth = DateTime.MinValue;
foreach (DataRow row in baseTable.Rows)
{
if (!Convert.IsDBNull(row["年份"]))
{
int year = (int)row["年份"];
int month = (int)row["月份"];
if (year != curRowMonth.Year || month != curRowMonth.Month)
{
curRowMonth = new DateTime(year, month, 1);
}
row["實際工資"] = Math.Round(((decimal)row["實際工資"]) / workHoursDic[curRowMonth], MidpointRounding.AwayFromZero);
}
}
//重新Group
DataTableHelper dtHelper = new DataTableHelper();
System.Data.DataTable groupTable = dtHelper.SelectGroupByInto("GroupTable", baseTable, "品號,SUM(實際工時) 實際工時, SUM(實際工資) 實際工資", null, "品號");
//取得指定日期範圍內的工作單號
cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT DISTINCT(單號) FROM 工作單 WHERE 實際完成日 > #" + _startDate.ToString("yyyy/MM/dd") + "# AND 實際完成日 < #" + _endDate.ToString("yyyy/MM/dd") + "#";
cmd.Connection = adapter.Connection;
System.Data.DataTable wsNumTable = new System.Data.DataTable();
wsNumTable.Columns.Add(new DataColumn("單號", typeof(string)));
System.Data.OleDb.OleDbDataAdapter wsNumAdapter = new System.Data.OleDb.OleDbDataAdapter();
wsNumAdapter.SelectCommand = cmd;
wsNumAdapter.Fill(wsNumTable);
List <string> wsNumList = new List <string>();
foreach (DataRow row in wsNumTable.Rows)
{
wsNumList.Add(row["單號"].ToString());
}
//取得LaborWage資料庫
LaborWageHelper lwHelper = new LaborWageHelper();
LaborWage工作單品號Table lwTable = lwHelper.GetDataGroupByPartNumber(wsNumList);
//填入工資與工時
foreach (ReportDataSet.UnitPriceReportRow row in _table)
{
DataRow[] partRows = groupTable.Select(string.Format("品號 = '{0}'", row.品號));
if (partRows.Length > 0)
{
row._實際工時_內_外_ = (decimal)partRows[0]["實際工時"];
row._實際工資_內_外_ = (decimal)partRows[0]["實際工資"];
}
//填入LaborWage資料
DataRow[] lwRows = lwTable.Select(string.Format("品號 = '{0}'", row.品號));
if (lwRows.Length > 0)
{
decimal wage = (decimal)lwRows[0]["外包工資"];
row._實際工資_內_外_ += wage;
row._實際工時_內_外_ += wage / Settings.HourlyPay;
}
}
}
private void button4_Click(object sender, EventArgs e)
{
metroProgressSpinner1.Visible = true;
Cursor.Current = Cursors.WaitCursor;
filltable();
int row_num = 1;
metroProgressSpinner1.Style = MetroFramework.MetroColorStyle.Yellow;
string qry = "select BrCode,gl_l_name as 'General Ledger Name',sl_l_name as 'Sub Ledger Name','opening_balance'= " +
"CASE WHEN op_dr_bal > 0 THEN op_dr_bal " +
"WHEN op_cr_bal> 0 THEN op_cr_bal*-1 " +
"ELSE 0 " +
"END," +
"DR_BAL as 'Debit',CR_BAL as 'Credit',(op_dr_bal + DR_BAL - op_cr_bal - CR_BAL) as 'Closing Balance',spid " +
"from TblRPTLEDGER ORDER BY brcode,gl_l_name";
SQL.DataTable dtTrial = new SQL.DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(qry, clsConnection.Conn))
{
da.Fill(dtTrial);
}
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
oXL = new Excel.Application();
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
try
{
SQL.DataTable dtbranch =
dtTrial.DefaultView.ToTable(true, "BrCode");
foreach (SQL.DataRow x in dtbranch.Rows)
{
metroProgressSpinner1.Maximum = dtbranch.Rows.Count;
oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
oSheet.Name = x[0].ToString().Replace(" ", "").
Replace(" ", "").Replace("/", "").
Replace("\\", "").Replace("*", "");
string[] colNames = new string[dtTrial.Columns.Count];
int col = 0;
foreach (SQL.DataColumn dc in dtTrial.Columns)
{
colNames[col++] = dc.ColumnName;
}
char lastColumn = (char)(65 + dtTrial.Columns.Count - 1);
oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.Columns.AutoFit();
SQL.DataRow[] dr = dtTrial.Select(string.Format("BrCode='{0}'", x[0].ToString()));
string[,] rowData = new string[dr.Count <SQL.DataRow>(), dtTrial.Columns.Count];
int rowCnt = 0;
int redRows = 2;
foreach (SQL.DataRow row in dr)
{
for (col = 0; col < dtTrial.Columns.Count; col++)
{
rowData[rowCnt, col] = row[col].ToString();
}
if (double.Parse(row["Opening_Balance"].ToString()) == 0)
{
Range range = oSheet.get_Range("A" + redRows.ToString(), "H" + redRows.ToString());
range.Cells.Interior.Color = System.Drawing.Color.LightYellow;
}
redRows++;
rowCnt++;
}
Range range1 = oSheet.get_Range("A" + rowCnt.ToString(), "H" + rowCnt.ToString());
range1.Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value2 = rowData;
metroProgressSpinner1.Value = row_num;
row_num = row_num + 1;
}
//Cursor gets default - wait ends
Cursor.Current = Cursors.Default;
metroProgressSpinner1.Visible = false;
//Filesave pops up
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel File|*.xlsx";
saveFileDialog1.Title = "Save an Excel File";
saveFileDialog1.ShowDialog();
// If the file name is not an empty string open it for saving.
if (saveFileDialog1.FileName != "")
{
object misValue = System.Reflection.Missing.Value;
oWB.SaveAs(saveFileDialog1.FileName, AccessMode: Excel.XlSaveAsAccessMode.xlShared);
oWB.Close(saveFileDialog1.FileName);
oXL.Quit();
MessageBox.Show("Excel file created");
}
}
catch (Exception ex)
{
MessageBox.Show("Exception Occured while creating Excel file " + ex.ToString());
}
finally
{
Marshal.ReleaseComObject(oWB);
}
}
private void button5_Click(object sender, EventArgs e)
{
UniversiteBrowser();
System.Threading.Thread.Sleep(5000);
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("YOP Kodu");
dt.Columns.Add("Universite Adı");
dt.Columns.Add("Fakülte Adı");
dt.Columns.Add("Bölüm Adı");
dt.Columns.Add("Bölüm Tipi");
dt.Columns.Add("Şehir");
dt.Columns.Add("Üniversite Tür");
dt.Columns.Add("Ücret/Burs");
dt.Columns.Add("Öğrenim Türü");
System.Threading.Thread.Sleep(7000);
string[] YOPkodu = new string[1];
string[] UniAdi = new string[2];
string[] UniFakulteAdi = new string[2];
string[] Sehir = new string[1];
string[] UniTuru = new string[1];
string[] UniTuruBursUcret = new string[1];
string[] OgrenimTuru = new string[1];
for (int i = 0; i <= 97; i++)
{
for (int j = 1; j <= 50; j++)
{
var yopKodu = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[2]/a")).Text;
YOPkodu[0] = yopKodu;
var uniAdi = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[3]/strong")).Text;
UniAdi[0] = uniAdi;
var uniBolumAdi = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[4]/strong")).Text;
UniAdi[1] = uniBolumAdi;
var uniFakulteAdi = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[3]/font")).Text;
UniFakulteAdi[0] = uniFakulteAdi;
var uniFakulteCesidiAdi = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[4]/font")).Text;
UniFakulteAdi[1] = uniFakulteCesidiAdi;
var sehirAdi = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[5]")).Text;
Sehir[0] = sehirAdi;
var uniTuru = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[6]")).Text;
UniTuru[0] = uniTuru;
var uniTuruBursUcret = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[7]")).Text;
UniTuruBursUcret[0] = uniTuruBursUcret;
var ogrenimTuru = driver.FindElement(By.XPath($"/html/body/div/div[2]/div[2]/div[2]/div/div/div[2]/div/table/tbody/tr[{j}]/td[8]")).Text;
OgrenimTuru[0] = ogrenimTuru;
dt.Rows.Add(YOPkodu[0], UniAdi[0], UniFakulteAdi[0], UniAdi[1], UniFakulteAdi[1], Sehir[0], UniTuru[0], UniTuruBursUcret[0], OgrenimTuru[0]);
}
dataGridView1.DataSource = dt;
System.Threading.Thread.Sleep(3000);
//Scroll aşağıya doğru hareket ettirmek için.
IJavaScriptExecutor js1 = driver as IJavaScriptExecutor;
js1.ExecuteScript("window.scrollBy(0,4000);");
System.Threading.Thread.Sleep(3000);
driver.FindElement(By.XPath("/html/body/div/div[2]/div[2]/div[2]/div/div/div[3]/div[2]/div/ul/li[9]")).Click();
System.Threading.Thread.Sleep(8000);
}
}
private void button3_Click(object sender, EventArgs e)
{
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(@"C:\Users\Lenovo\Desktop\Nota\WindowsFormsApp1\TürkiyeMTALiseleri.xlsx");
Worksheet ws = wb.Worksheets[1];
string[] kurumKoduListesi = new string[2487];
for (int i = 2; i < 2487; i++)
{
if (ws.Cells[i, 5].Value != null)
{
var kurumKodu = ws.Cells[i, 5].Value.ToString();
kurumKoduListesi[i] = kurumKodu;
}
}
driver = new FirefoxDriver();
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Kurum Kodu");
dt.Columns.Add("Okul Adı");
dt.Columns.Add("Program Türü");
dt.Columns.Add("Alan Adı");
dt.Columns.Add("Dal Adı");
dt.Columns.Add("Yabancı Dil");
dt.Columns.Add("Öğretim Şekli");
for (int i = 2; i < 2487; i++)
{
driver.Navigate().GoToUrl($"http://mtegm.meb.gov.tr/kurumlar/?s=kurumDetay&d=kurum.alandal&kk={kurumKoduListesi[i]}");
System.Threading.Thread.Sleep(5000);
var okulAdiTag = driver.FindElement(By.XPath("/html/body/section[1]/div/div[2]/div/div[3]/div/div[1]/table/tbody/tr[1]/td[2]"));
var okulAdi = "";
foreach (var item in okulAdiTag.FindElements(By.TagName("strong")))
{
okulAdi = item.Text;
}
System.Threading.Thread.Sleep(2000);
var tablo = driver.FindElement(By.XPath("/html/body/section[1]/div/div[2]/div/div[3]/div/div[2]/table/tbody"));
foreach (var row in tablo.FindElements(By.TagName("tr")))
{
//Almak istediğim sütun sayısını belirler.
int cellIndex = 0;
string[] okul = new string[5];
foreach (var cell in row.FindElements(By.TagName("td")))
{
if (cellIndex <= 4)
{
okul[cellIndex] = cell.Text;
}
else
{
continue;
}
cellIndex++;
}
dt.Rows.Add(kurumKoduListesi[i], okulAdi, okul[0], okul[1], okul[2], okul[3], okul[4]);
}
dataGridView1.DataSource = dt;
System.Threading.Thread.Sleep(2000);
}
}
public void CheckEachColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueIndex)
{
for (int i = 0; i < gCols; i++)
{
bool colInserted = false;
// Check each cell in this columns
for (int j = 0; j < block.lineList.Count; j++)
{
TplLine line = block.lineList [j];
TplCell cell = line.cellList [startCellIndex + i];
if (cell.align != GroupAlign.hGroup)
{
continue;
}
if (!colInserted)
{
bool needNew = cell.IsNeedNewCell(holder, cell.hgOption, 0, currentRowIndex, table, valueIndex);
if (needNew)
{
InsertOneColumn(block, i, holder, table, valueIndex, false);
colInserted = true;
}
}
else
{
if ((cell.hgOption & InsertOption.BeforeChange) != 0)
{
continue;
}
// set last grouped value
cell.lastGroupedValue = cell.GetGroupValue(holder, table, valueIndex);
}
}
}
}
public int CheckColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueIndex)
{
// if grouped Value changed.
// if value has be inserted.
// Insert Cell At EachLine
// Update TplLine Info.
int startIndex = IsNeedNewCol(currentRowIndex, holder, table, valueIndex);
if (startIndex < 0)
{
return(0);
}
// Console.WriteLine("---- Start of " + valueIndex);
InsertColumn(block, holder, table, valueIndex, true);
return(1);
}
public bool IsNeedNewCell(GroupDataHolder holder, InsertOption nlOption, int currentColIndex, int currentRowIndex, System.Data.DataTable table,
int valueRowIndex)
{
if (align == GroupAlign.always)
{
return(true);
}
if (align == GroupAlign.none)
{
return((nlOption & InsertOption.always) != 0);
}
object value = GetGroupValue(holder, table, valueRowIndex);
switch (align)
{
case GroupAlign.vGroup:
case GroupAlign.hGroup:
if ((nlOption & InsertOption.BeforeChange) != 0)
{
if (valueRowIndex + 1 >= table.Rows.Count)
{
// last line
return(true);
}
object value1 = GetGroupValue(holder, table, valueRowIndex);
object value2 = GetGroupValue(holder, table, valueRowIndex + 1);
return(!Equals(value1, value2));
}
else
{
if (lastGroupedValue == null || !lastGroupedValue.Equals(value))
{
lastGroupedValue = value;
return(true);
}
else
{
return(false);
}
}
/*case GroupAlign.hGroup:
* // todo: check hGroup Cells. dose need a New Row.
* if (value == null)
* return true;
* bool flag;
* if (lastValueMap.TryGetValue(value, out flag))
* return false;
* else
* lastValueMap.Add(value, true);
* return true;*/
default:
break;
}
return(false);
}
public void GenerateExcel(DataTable DtIN, string path, string name)
{
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.DisplayAlerts = false;
excel.Visible = false;
workBook = excel.Workbooks.Add(Type.Missing);
workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
workSheet.Name = "Reporte Pedidos";
System.Data.DataTable tempDt = DtIN;
//dgExcel.ItemsSource = tempDt.DefaultView;
workSheet.Cells.Font.Size = 11;
int rowcount = 1;
for (int i = 1; i <= tempDt.Columns.Count; i++) //taking care of Headers.
{
string colname = "";
switch (tempDt.Columns[i - 1].ColumnName)
{
case "Name": colname = "Nombre"; break;
case "Items": colname = "Lista de Productos"; break;
case "Status": colname = "Estado"; break;
case "Total": colname = "Total en Pesos"; break;
case "Date": colname = "Fecha"; break;
case "Time": colname = "Hora"; break;
case "dateTime": colname = "Generado al: "; break;
//Parte para item popular
case "Description": colname = "Descripcion del Producto"; break;
case "Price": colname = "Precio en Pesos"; break;
case "TotalInPedidos": colname = "Total Veces encontrado en Pedidos"; break;
default: colname = tempDt.Columns[i - 1].ColumnName; break;
}
workSheet.Cells[1, i] = colname; //tempDt.Columns[i - 1].ColumnName;
}
foreach (System.Data.DataRow row in tempDt.Rows) //taking care of each Row
{
rowcount += 1;
for (int i = 0; i < tempDt.Columns.Count; i++) //taking care of each column
{
workSheet.Cells[rowcount, i + 1] = row[i].ToString();
}
}
cellRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowcount, tempDt.Columns.Count]];
cellRange.EntireColumn.AutoFit();
string[] paths = { string.Format(@"{0}", path), name };
workBook.SaveAs(System.IO.Path.Combine(paths));
//workBook.SaveAs(System.IO.Path.Combine(@"C:\InventoryApp\","Excel book Name "));
workBook.Close();
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Error creando el report: " + ex.Message);
}
}
public void RegisterDataObject(Type objType)
{
if (TableDatasets.ContainsKey(GetTableOrViewName(objType)))
{
return;
}
bool primary = false;
bool relations = false;
MemberInfo primaryIndexMember = null;
string tableName = GetTableOrViewName(objType);
var ds = new DataSet();
var table = new System.Data.DataTable(tableName);
table.Columns.Add(tableName + "_ID", typeof(string));
MemberInfo[] myMembers = objType.GetMembers();
for (int i = 0; i < myMembers.Length; i++)
{
object[] myAttributes = myMembers[i].GetCustomAttributes(typeof(PrimaryKey), true);
if (myAttributes.Length > 0)
{
primary = true;
if (myMembers[i] is PropertyInfo)
{
table.Columns.Add(myMembers[i].Name, ((PropertyInfo)myMembers[i]).PropertyType);
}
else
{
table.Columns.Add(myMembers[i].Name, ((FieldInfo)myMembers[i]).FieldType);
}
table.Columns[myMembers[i].Name].AutoIncrement = ((PrimaryKey)myAttributes[0]).AutoIncrement;
table.Columns[myMembers[i].Name].AutoIncrementSeed = ((PrimaryKey)myAttributes[0]).IncrementValue;
var index = new DataColumn[1];
index[0] = table.Columns[myMembers[i].Name];
primaryIndexMember = myMembers[i];
table.PrimaryKey = index;
continue;
}
myAttributes = myMembers[i].GetCustomAttributes(typeof(DataElement), true);
if (myAttributes.Length > 0)
{
if (myMembers[i] is PropertyInfo)
{
table.Columns.Add(myMembers[i].Name, ((PropertyInfo)myMembers[i]).PropertyType);
}
else
{
table.Columns.Add(myMembers[i].Name, ((FieldInfo)myMembers[i]).FieldType);
}
table.Columns[myMembers[i].Name].AllowDBNull = ((DataElement)myAttributes[0]).AllowDbNull;
if (((DataElement)myAttributes[0]).Unique)
{
table.Constraints.Add(new UniqueConstraint("UNIQUE_" + myMembers[i].Name, table.Columns[myMembers[i].Name]));
}
if (((DataElement)myAttributes[0]).Index)
{
table.Columns[myMembers[i].Name].ExtendedProperties.Add("INDEX", true);
}
if (((DataElement)myAttributes[0]).Varchar > 0)
{
table.Columns[myMembers[i].Name].ExtendedProperties.Add("VARCHAR", ((DataElement)myAttributes[0]).Varchar);
}
myAttributes = GetRelationAttributes(myMembers[i]);
if (myAttributes.Length > 0)
{
relations = true;
}
}
}
if (primary == false)
{
var index = new DataColumn[1];
index[0] = table.Columns[tableName + "_ID"];
table.PrimaryKey = index;
}
if (Connection.IsSQLConnection)
{
Connection.CheckOrCreateTable(table);
}
ds.DataSetName = tableName;
ds.EnforceConstraints = true;
ds.CaseSensitive = false;
ds.Tables.Add(table);
var dth = new DataTableHandler(ds);
dth.HasRelations = relations;
dth.UsesPreCaching = DataObject.GetPreCachedFlag(objType);
TableDatasets.Add(tableName, dth);
}
public void RegisterDataObject(Type objType)
{
if (TableDatasets.ContainsKey(GetTableOrViewName(objType)))
{
return;
}
bool primaryKeySpecified = false;
bool useAutoIncrementColumn = false;
bool relations = false;
MemberInfo primaryIndexMember = null;
string tableName = GetTableOrViewName(objType);
var ds = new DataSet();
var table = new System.Data.DataTable(tableName);
MemberInfo[] myMembers = objType.GetMembers();
for (int i = 0; i < myMembers.Length; i++)
{
//object[] myAttributes = myMembers[i].GetCustomAttributes(true);
//object[] myAttributes = myMembers[i].GetCustomAttributes(typeof(DOL.Database.Attributes.DataElement), true);
object[] myAttributes = myMembers[i].GetCustomAttributes(typeof(PrimaryKey), true);
if (myAttributes.Length > 0)
{
primaryKeySpecified = true;
if (myMembers[i] is PropertyInfo)
{
table.Columns.Add(myMembers[i].Name, ((PropertyInfo)myMembers[i]).PropertyType);
}
else
{
table.Columns.Add(myMembers[i].Name, ((FieldInfo)myMembers[i]).FieldType);
}
table.Columns[myMembers[i].Name].AutoIncrement = ((PrimaryKey)myAttributes[0]).AutoIncrement;
useAutoIncrementColumn = table.Columns[myMembers[i].Name].AutoIncrement;
var index = new DataColumn[1];
index[0] = table.Columns[myMembers[i].Name];
primaryIndexMember = myMembers[i];
table.PrimaryKey = index;
continue;
}
myAttributes = myMembers[i].GetCustomAttributes(typeof(DataElement), true);
if (myAttributes.Length > 0)
{
//if(myAttributes[0] is Attributes.DataElement)
//{
if (myMembers[i] is PropertyInfo)
{
table.Columns.Add(myMembers[i].Name, ((PropertyInfo)myMembers[i]).PropertyType);
}
else
{
table.Columns.Add(myMembers[i].Name, ((FieldInfo)myMembers[i]).FieldType);
}
table.Columns[myMembers[i].Name].AllowDBNull = ((DataElement)myAttributes[0]).AllowDbNull;
if (((DataElement)myAttributes[0]).Unique)
{
table.Constraints.Add(new UniqueConstraint("UNIQUE_" + myMembers[i].Name, table.Columns[myMembers[i].Name]));
}
if (((DataElement)myAttributes[0]).IndexColumns != string.Empty)
{
table.Columns[myMembers[i].Name].ExtendedProperties.Add("INDEX", true);
table.Columns[myMembers[i].Name].ExtendedProperties.Add("INDEXCOLUMNS", ((DataElement)myAttributes[0]).IndexColumns);
}
else if (((DataElement)myAttributes[0]).Index)
{
table.Columns[myMembers[i].Name].ExtendedProperties.Add("INDEX", true);
}
if (((DataElement)myAttributes[0]).Varchar > 0)
{
table.Columns[myMembers[i].Name].ExtendedProperties.Add("VARCHAR", ((DataElement)myAttributes[0]).Varchar);
}
//if(myAttributes[0] is Attributes.PrimaryKey)
myAttributes = GetRelationAttributes(myMembers[i]);
//if(myAttributes[0] is Attributes.Relation)
if (myAttributes.Length > 0)
{
relations = true;
}
}
}
if (useAutoIncrementColumn == false)
{
// We define the Tablename_ID column that will always contain a generated unique ID
DataColumn idColumn = table.Columns.Add(tableName + "_ID", typeof(string));
if (primaryKeySpecified)
{
// if another primary key is defined on this table but the TableName_ID column is still being used then force
// the creation of a unique index on the the TableName_ID column
table.Constraints.Add(new UniqueConstraint(idColumn.ColumnName, idColumn));
}
}
if (primaryKeySpecified == false)
{
var index = new DataColumn[1];
index[0] = table.Columns[tableName + "_ID"];
table.PrimaryKey = index;
}
if (Connection.IsSQLConnection)
{
Connection.CheckOrCreateTable(table);
}
else
{
if (!System.IO.File.Exists(Connection.connString + tableName + ".xml"))
{
Connection.SaveDataSet(tableName, ds);
}
}
ds.DataSetName = tableName;
ds.EnforceConstraints = true;
ds.CaseSensitive = false;
ds.Tables.Add(table);
var dth = new DataTableHandler(ds);
dth.HasRelations = relations;
dth.UsesPreCaching = DataObject.GetPreCachedFlag(objType);
TableDatasets.Add(tableName, dth);
//if (dth.UsesPreCaching && Connection.IsSQLConnection)
//{
// // not useful for xml connection
// if (Log.IsDebugEnabled)
// Log.Debug("Precaching of " + table.TableName + "...");
// var objects = SQLSelectObjects<TObject>("");
// object key;
// for (int i = 0; i < objects.Length; i++)
// {
// key = null;
// if (primaryIndexMember == null)
// {
// key = objects[i].ObjectId;
// }
// else
// {
// if (primaryIndexMember is PropertyInfo)
// {
// key = ((PropertyInfo) primaryIndexMember).GetValue(objects[i], null);
// }
// else if (primaryIndexMember is FieldInfo)
// {
// key = ((FieldInfo) primaryIndexMember).GetValue(objects[i]);
// }
// }
// if (key != null)
// {
// dth.SetPreCachedObject(key, objects[i]);
// }
// else
// {
// if (Log.IsErrorEnabled)
// Log.Error("Primary key is null! " + ((primaryIndexMember != null) ? primaryIndexMember.Name : ""));
// }
// }
// if (Log.IsDebugEnabled)
// Log.Debug("Precaching of " + table.TableName + " finished!");
//}
}
DataTable InitDstDataTable()
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add(new DataColumn("�W��", typeof(string)));
table.Columns.Add(new DataColumn("�ӷ�", typeof(string)));
table.Columns.Add(new DataColumn("����", typeof(int)));
table.Columns["����"].DefaultValue = 0;
string[] cols = new string[] { "�t�C�s��","�t�C�N��", "�~��", "�~�W", "���u", "�u��", "�t���~" };
string[] keys = new string[] { "�s��", "�N��", "�~��", "�~�W", "���u", "�u��", "���зǤu��" };
_dstRowList.Clear();
_dstRowKeywordList.Clear();
for(int i=0;i<cols.Length;i++)
{
string col = cols[i];
DataRow row = table.NewRow();
row["�W��"] = col;
table.Rows.Add(row);
_dstRowList.Add(col, row);
_dstRowKeywordList.Add(col, keys[i]);
}
return table;
}
public void DataTableToExcel(System.Data.DataTable tmDataTable, string strFileName)
{
WaitingForm wtForm = new WaitingForm();
waitingBool = true;
wtForm.Show();
if (strFileName == null)
{
return;
}
int RowNum = tmDataTable.Rows.Count;
int ColumnNum = tmDataTable.Columns.Count;
int RowIndex = 2;
int ColumnIndex = 0;
Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
//打开Excel应用
xlapp.DefaultFilePath = "";
xlapp.DisplayAlerts = true;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //创建一个Excel文件
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//拿到那个工作表
//foreach (DataColumn dc in tmDataTable.Columns)
//{
// ColumnIndex++;
// worksheet.Cells[RowIndex, ColumnIndex] = dc.ColumnName;
//}
//给两列写列名
worksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
worksheet.Columns.ColumnWidth = 20;
Microsoft.Office.Interop.Excel.Range mergeRange;
mergeRange = worksheet.get_Range("A1", "B1");
mergeRange.Merge(0);
if (comboBox4.Text == "")
{
worksheet.Cells[1, 1] = comboBox1.Text + "-" + comboBox2.Text + "-" + comboBox3.Text;
}
else
{
worksheet.Cells[1, 1] = comboBox1.Text + "-" + comboBox2.Text + "-" + comboBox3.Text + "-" + comboBox4.Text;
}
worksheet.Cells[2, 1] = "值";
worksheet.Cells[2, 2] = "时间";
//添加寄存器对应的监控点名称
for (int i = 0; i < RowNum; i++)
{
RowIndex++;
ColumnIndex = 0;
for (int j = 1; j < 3; j++)
{
ColumnIndex++;
worksheet.Cells[RowIndex, ColumnIndex] = tmDataTable.Rows[i][j].ToString();
}
}
waitingBool = false;
workbook.SaveCopyAs(strFileName + ".xlsx");
MessageBox.Show("Excle表格导出成功,保存为" + strFileName);
//退出关闭EXCLE.EXE线程
xlapp.Quit();
IntPtr t = new IntPtr(xlapp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
public static void CreateLerData(Database db, FeatureCollection fc)
{
string pathLag = "X:\\AutoCAD DRI - 01 Civil 3D\\Lag-Ler2.0.csv";
System.Data.DataTable dtLag = CsvReader.ReadCsvToDataTable(pathLag, "Lag");
if (dtLag == null)
{
throw new System.Exception("Lag file could not be read!");
}
HashSet <UtilityOwner> ownersRegister = new HashSet <UtilityOwner>();
HashSet <LedningType> ledninger = new HashSet <LedningType>();
HashSet <LedningstraceType> ledningstrace = new HashSet <LedningstraceType>();
HashSet <LedningskomponentType> ledningskomponenter = new HashSet <LedningskomponentType>();
#region Redirect objects to collections
//Redirect objects to collections
//int i = 0;
foreach (FeatureMember fm in fc.featureCollection)
{
//i++; prdDbg($"Switching item {i}.");
System.Windows.Forms.Application.DoEvents();
switch (fm.item)
{
case UtilityOwner uo:
ownersRegister.Add(uo);
break;
case Graveforesp gvfsp:
break;
case UtilityPackageInfo upi:
break;
case Kontaktprofil kp:
break;
case Informationsressource ir:
break;
case LedningType lt:
ledninger.Add(lt);
//prdDbg(lt.gmlid);
break;
case LedningstraceType ltr:
ledningstrace.Add(ltr);
//prdDbg(ltr.gmlid);
break;
case LedningskomponentType lk:
ledningskomponenter.Add(lk);
//prdDbg(lk.gmlid);
break;
default:
prdDbg(fm.item.GMLTypeID);
throw new System.Exception($"Unexpected type encountered {fm.item.GetType().Name}!");
}
}
#endregion
#region Populate Company Name
//Populate Company Name
foreach (LedningType ledning in ledninger)
{
var owner = ownersRegister.FirstOrDefault(x => x.ledningsejer == ledning.ledningsejer);
//if (owner == default) throw new System.Exception($"Ledning {ledning.id} kan ikke finde ejer!");
if (owner == default)
{
prdDbg($"Ledning {ledning.id} kan ikke finde ejer!");
}
else
{
ledning.LedningsEjersNavn = owner.companyName;
}
}
foreach (LedningstraceType trace in ledningstrace)
{
var owner = ownersRegister.FirstOrDefault(x => x.ledningsejer == trace.ledningsejer);
//if (owner == default) throw new System.Exception($"Ledning {trace.id} kan ikke finde ejer!");
if (owner == default)
{
prdDbg($"Ledning {trace.id} kan ikke finde ejer!");
}
else
{
trace.LedningsEjersNavn = owner.companyName;
}
}
foreach (LedningskomponentType komp in ledningskomponenter)
{
var owner = ownersRegister.FirstOrDefault(x => x.ledningsejer == komp.ledningsejer);
//if (owner == default) throw new System.Exception($"Ledning {komp.id} kan ikke finde ejer!");
if (owner == default)
{
prdDbg($"Ledning {komp.id} kan ikke finde ejer!");
}
else
{
komp.LedningsEjersNavn = owner.companyName;
}
}
#endregion
#region Create property sets
//Dictionary to translate between type name and psName
Dictionary <string, string> psDict = new Dictionary <string, string>();
//Create property sets
HashSet <Type> allUniqueTypes = ledninger.Select(x => x.GetType()).Distinct().ToHashSet();
allUniqueTypes.UnionWith(ledningstrace.Select(x => x.GetType()).Distinct().ToHashSet());
allUniqueTypes.UnionWith(ledningskomponenter.Select(x => x.GetType()).Distinct().ToHashSet());
foreach (Type type in allUniqueTypes)
{
string psName = type.Name.Replace("Type", "");
//Store the ps name in dictionary referenced by the type name
//PS name is not goood! It becomes Elledning which is not unique
//But it is unique!!
//Data with different files will still follow the class definition in code
//Which assures that all pssets are the same
psDict.Add(type.Name, psName);
PropertySetDefinition propSetDef = new PropertySetDefinition();
propSetDef.SetToStandard(db);
propSetDef.SubSetDatabaseDefaults(db);
propSetDef.Description = type.FullName;
bool isStyle = false;
var appliedTo = new StringCollection()
{
RXClass.GetClass(typeof(Polyline)).Name,
RXClass.GetClass(typeof(Polyline3d)).Name,
RXClass.GetClass(typeof(DBPoint)).Name,
RXClass.GetClass(typeof(Hatch)).Name,
};
propSetDef.SetAppliesToFilter(appliedTo, isStyle);
var properties = type.GetProperties();
foreach (PropertyInfo prop in properties)
{
bool include = prop.CustomAttributes.Any(x => x.AttributeType == typeof(Schema.PsInclude));
if (include)
{
var propDefManual = new PropertyDefinition();
propDefManual.SetToStandard(db);
propDefManual.SubSetDatabaseDefaults(db);
propDefManual.Name = prop.Name;
propDefManual.Description = prop.Name;
switch (prop.PropertyType.Name)
{
case nameof(String):
propDefManual.DataType = Autodesk.Aec.PropertyData.DataType.Text;
propDefManual.DefaultData = "";
break;
case nameof(System.Boolean):
propDefManual.DataType = Autodesk.Aec.PropertyData.DataType.TrueFalse;
propDefManual.DefaultData = false;
break;
case nameof(Double):
propDefManual.DataType = Autodesk.Aec.PropertyData.DataType.Real;
propDefManual.DefaultData = 0.0;
break;
case nameof(Int32):
propDefManual.DataType = Autodesk.Aec.PropertyData.DataType.Integer;
propDefManual.DefaultData = 0;
break;
default:
propDefManual.DataType = Autodesk.Aec.PropertyData.DataType.Text;
propDefManual.DefaultData = "";
break;
}
propSetDef.Definitions.Add(propDefManual);
}
}
using (Transaction tx = db.TransactionManager.StartTransaction())
{
//check if prop set already exists
DictionaryPropertySetDefinitions dictPropSetDef = new DictionaryPropertySetDefinitions(db);
if (dictPropSetDef.Has(psName, tx))
{
tx.Abort();
continue;
}
dictPropSetDef.AddNewRecord(psName, propSetDef);
tx.AddNewlyCreatedDBObject(propSetDef, true);
tx.Commit();
}
}
#endregion
#region Create elements
//List of all (new) layers of new entities
HashSet <string> layerNames = new HashSet <string>();
foreach (LedningType ledning in ledninger)
{
string psName = psDict[ledning.GetType().Name];
ILerLedning iLedning = ledning as ILerLedning;
if (iLedning == null)
{
throw new System.Exception($"Ledning {ledning.id} har ikke implementeret ILerLedning!");
}
ObjectId entityId = iLedning.DrawEntity2D(db);
Entity ent = entityId.Go <Entity>(db.TransactionManager.TopTransaction, OpenMode.ForWrite);
layerNames.Add(ent.Layer);
//Attach the property set
PropertySetManager.AttachNonDefinedPropertySet(db, ent, psName);
//Populate the property set
var psData = GmlToPropertySet.TranslateGmlToPs(ledning);
PropertySetManager.PopulateNonDefinedPropertySet(db, ent, psName, psData);
}
foreach (LedningstraceType trace in ledningstrace)
{
string psName = psDict[trace.GetType().Name];
ILerLedning ledning = trace as ILerLedning;
if (ledning == null)
{
throw new System.Exception($"Trace {trace.id} har ikke implementeret ILerLedning!");
}
ObjectId entityId = ledning.DrawEntity2D(db);
Entity ent = entityId.Go <Entity>(db.TransactionManager.TopTransaction, OpenMode.ForWrite);
layerNames.Add(ent.Layer);
//Attach the property set
PropertySetManager.AttachNonDefinedPropertySet(db, ent, psName);
//Populate the property set
var psData = GmlToPropertySet.TranslateGmlToPs(trace);
PropertySetManager.PopulateNonDefinedPropertySet(db, ent, psName, psData);
}
foreach (LedningskomponentType komponent in ledningskomponenter)
{
string psName = psDict[komponent.GetType().Name];
ILerKomponent creator = komponent as ILerKomponent;
if (creator == null)
{
throw new System.Exception($"Komponent {komponent.id} har ikke implementeret ILerKomponent!");
}
Oid entityId = creator.DrawComponent(db);
Entity ent = entityId.Go <Entity>(db.TransactionManager.TopTransaction, OpenMode.ForWrite);
//Attach the property set
PropertySetManager.AttachNonDefinedPropertySet(db, ent, psName);
//Populate the property set
var psData = GmlToPropertySet.TranslateGmlToPs(komponent);
PropertySetManager.PopulateNonDefinedPropertySet(db, ent, psName, psData);
}
#endregion
#region Read and assign layer's color
//Cache layer table
LayerTable ltable = db.LayerTableId.Go <LayerTable>(db.TransactionManager.TopTransaction);
//Set up all LER layers
foreach (string layerName in layerNames)
{
string colorString = ReadStringParameterFromDataTable(layerName, dtLag, "Farve", 0);
Color color;
if (colorString.IsNoE())
{
Log.log($"Ledning with layer name {layerName} could not get a color!");
color = Color.FromColorIndex(ColorMethod.ByAci, 0);
}
else
{
color = ParseColorString(colorString);
if (color == null)
{
Log.log($"Ledning layer name {layerName} could not parse colorString {colorString}!");
color = Color.FromColorIndex(ColorMethod.ByAci, 0);
}
}
LayerTableRecord ltr = ltable[layerName]
.Go <LayerTableRecord>(db.TransactionManager.TopTransaction, OpenMode.ForWrite);
ltr.Color = color;
}
#endregion
#region Read and assign layer's linetype
LinetypeTable ltt = (LinetypeTable)db.TransactionManager.TopTransaction
.GetObject(db.LinetypeTableId, OpenMode.ForWrite);
//Check if all line types are present
HashSet <string> missingLineTypes = new HashSet <string>();
foreach (string layerName in layerNames)
{
string lineTypeName = ReadStringParameterFromDataTable(layerName, dtLag, "LineType", 0);
if (lineTypeName.IsNoE())
{
continue;
}
else if (!ltt.Has(lineTypeName))
{
missingLineTypes.Add(lineTypeName);
}
}
if (missingLineTypes.Count > 0)
{
Database ltDb = new Database(false, true);
ltDb.ReadDwgFile("X:\\AutoCAD DRI - 01 Civil 3D\\Projection_styles.dwg",
FileOpenMode.OpenForReadAndAllShare, false, null);
Transaction ltTx = ltDb.TransactionManager.StartTransaction();
Oid destDbMsId = SymbolUtilityServices.GetBlockModelSpaceId(db);
LinetypeTable sourceLtt = (LinetypeTable)ltDb.TransactionManager.TopTransaction
.GetObject(ltDb.LinetypeTableId, OpenMode.ForRead);
ObjectIdCollection idsToClone = new ObjectIdCollection();
foreach (string missingName in missingLineTypes)
{
idsToClone.Add(sourceLtt[missingName]);
}
IdMapping mapping = new IdMapping();
ltDb.WblockCloneObjects(idsToClone, destDbMsId, mapping, DuplicateRecordCloning.Replace, false);
ltTx.Commit();
ltTx.Dispose();
ltDb.Dispose();
}
Oid lineTypeId;
foreach (string layerName in layerNames)
{
string lineTypeName = ReadStringParameterFromDataTable(layerName, dtLag, "LineType", 0);
if (lineTypeName.IsNoE())
{
Log.log($"WARNING! Layer name {layerName} does not have a line type specified!.");
//If linetype string is NoE -> CONTINUOUS linetype must be used
lineTypeId = ltt["Continuous"];
}
else
{
//the presence of the linetype is assured in previous foreach.
lineTypeId = ltt[lineTypeName];
}
LayerTableRecord ltr = ltable[layerName]
.Go <LayerTableRecord>(db.TransactionManager.TopTransaction, OpenMode.ForWrite);
ltr.LinetypeObjectId = lineTypeId;
}
#endregion
}
void InitReportTable()
{
ReportDataSetTableAdapters.UnitPriceReportTableAdapter adapter = new Mong.ReportDataSetTableAdapters.UnitPriceReportTableAdapter();
//���o������
_table = adapter.GetData(_startDate, _endDate);
//���o�u�@�渹,�~��,��ڤu���
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT �u�@��.�渹 AS �渹, ���~�~��.�~��, Year(�u��.���) AS �~��, Month(�u��.���) AS ���, " +
"IIF(SUM(�u��.�u��) IS NULL , 0, SUM(�u��.�u��)) AS ��ڤu��," +
"IIF(SUM(�u��.�u��) IS NULL , 0, SUM(���u.�~�� * �u��.�u��)) AS ��ڤu�� " +
"FROM ((((�u�@�� INNER JOIN �u�@��~�� ON �u�@��~��.�渹 = �u�@��.�渹) " +
"INNER JOIN ���~�~�� ON �u�@��~��.�~�� = ���~�~��.�~��) " +
"LEFT JOIN �u�� ON �u��.�u�@�渹 = �u�@��~��.�渹 AND �u�@��~��.�s�� = �u��.�u�~�s��) " +
"LEFT JOIN ���u ON �u��.���u�s�� = ���u.�s��) " +
"WHERE �u�@��.��ڧ����� > #" + _startDate.ToString("yyyy/MM/dd") + "# AND �u�@��.��ڧ����� < #" + _endDate.ToString("yyyy/MM/dd") + "# " +
"GROUP BY �u�@��.�渹, ���~�~��.�~��, Year(�u��.���), Month(�u��.���)" +
"ORDER BY �u�@��.�渹, ���~�~��.�~��, Year(�u��.���), Month(�u��.���)";
cmd.Connection = adapter.Connection;
System.Data.DataTable baseTable = new System.Data.DataTable();
baseTable.Columns.Add(new DataColumn("�渹", typeof(string)));
baseTable.Columns.Add(new DataColumn("�~��", typeof(string)));
baseTable.Columns.Add(new DataColumn("��ڤu��", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("��ڤu��", typeof(decimal)));
baseTable.Columns.Add(new DataColumn("�~��", typeof(int)));
baseTable.Columns.Add(new DataColumn("���", typeof(int)));
System.Data.OleDb.OleDbDataAdapter baseAdapter = new System.Data.OleDb.OleDbDataAdapter();
baseAdapter.SelectCommand = cmd;
baseAdapter.Fill(baseTable);
//���o����d��
int minYear, minMonth, maxYear, maxMonth;
DateTime minDate, maxDate;
object o;
o = baseTable.Compute("MIN(�~��)", string.Empty);
minYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MIN(���)", string.Empty);
minMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
o = baseTable.Compute("MAX(�~��)", string.Empty);
maxYear = Convert.IsDBNull(o) ? DateTime.MinValue.Year : (int)o;
o = baseTable.Compute("MAX(���)", string.Empty);
maxMonth = Convert.IsDBNull(o) ? DateTime.MinValue.Month : (int)o;
minDate = new DateTime(minYear, minMonth, 1);
maxDate = new DateTime(maxYear, maxMonth, 1);
//���o�C��u�@�ɼ�
Dictionary<DateTime, decimal> workHoursDic = new Dictionary<DateTime, decimal>();
for (DateTime date = new DateTime(minDate.Year, minDate.Month, 1); date <= maxDate; date = date.AddMonths(1))
{
decimal hours = Global.GetWorkingHours(date.Year, date.Month);
workHoursDic.Add(date, hours);
}
//���s�p��baseTable����ڤu��
DateTime curRowMonth = DateTime.MinValue;
foreach (DataRow row in baseTable.Rows)
{
if (!Convert.IsDBNull(row["�~��"]))
{
int year = (int)row["�~��"];
int month = (int)row["���"];
if (year != curRowMonth.Year || month != curRowMonth.Month)
curRowMonth = new DateTime(year, month, 1);
row["��ڤu��"] = Math.Round(((decimal)row["��ڤu��"]) / workHoursDic[curRowMonth], MidpointRounding.AwayFromZero);
}
}
//���sGroup
DataTableHelper dtHelper = new DataTableHelper();
System.Data.DataTable groupTable = dtHelper.SelectGroupByInto("GroupTable", baseTable, "�~��,SUM(��ڤu��) ��ڤu��, SUM(��ڤu��) ��ڤu��", null, "�~��");
//���o���w����d���u�@�渹
cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "SELECT DISTINCT(�渹) FROM �u�@�� WHERE ��ڧ����� > #" + _startDate.ToString("yyyy/MM/dd") + "# AND ��ڧ����� < #" + _endDate.ToString("yyyy/MM/dd") + "#";
cmd.Connection = adapter.Connection;
System.Data.DataTable wsNumTable = new System.Data.DataTable();
wsNumTable.Columns.Add(new DataColumn("�渹", typeof(string)));
System.Data.OleDb.OleDbDataAdapter wsNumAdapter = new System.Data.OleDb.OleDbDataAdapter();
wsNumAdapter.SelectCommand = cmd;
wsNumAdapter.Fill(wsNumTable);
List<string> wsNumList = new List<string>();
foreach (DataRow row in wsNumTable.Rows)
wsNumList.Add(row["�渹"].ToString());
//���oLaborWage��Ʈw
LaborWageHelper lwHelper = new LaborWageHelper();
LaborWage�u�@��~��Table lwTable = lwHelper.GetDataGroupByPartNumber(wsNumList);
//��J�u��P�u��
foreach (ReportDataSet.UnitPriceReportRow row in _table)
{
DataRow[] partRows = groupTable.Select(string.Format("�~�� = '{0}'", row.�~��));
if (partRows.Length > 0)
{
row._��ڤu��_��_�~_ = (decimal)partRows[0]["��ڤu��"];
row._��ڤu��_��_�~_ = (decimal)partRows[0]["��ڤu��"];
}
//��JLaborWage���
DataRow[] lwRows = lwTable.Select(string.Format("�~�� = '{0}'", row.�~��));
if (lwRows.Length > 0)
{
decimal wage = (decimal)lwRows[0]["�~�]�u��"];
row._��ڤu��_��_�~_ += wage;
row._��ڤu��_��_�~_ += wage / Settings.HourlyPay;
}
}
}
private void GetAllOrder(int currentPageNumber, int rowPerPage, string strSearch)
{
dtMain = new System.Data.DataTable();
int skipRecord = currentPageNumber - 1;
if (skipRecord != 0)
{
skipRecord = skipRecord * rowPerPage;
}
string query = "select * from (select * from (select CONVERT(NVARCHAR(10), [order].ngayxong, 103) AS [Ngày Nhập/ Xuất], product.kyhieu [Ký Hiệu], product.masp [Mã Sản Phẩm], "
+ "SUM(CASE WHEN LoaiID = 0000001 AND[order].NhacciD is not null Then Soluong else 0 end)[Nhập BTP Chưa in], "
+ "SUM(CASE WHEN LoaiID = 0000002 AND[order].NhacciD is not null Then Soluong ELSE 0 END)[Nhập BTP Đã in], "
+ "SUM(CASE WHEN LoaiID = 0000003 AND[order].NhacciD is not null Then Soluong ELSE 0 END)[Nhập Thành Phẩm], "
+ "SUM(CASE WHEN LoaiID = 000004 AND[order].NhacciD is not null Then Soluong ELSE 0 END)[Nhập Sản phẩm lỗi], "
+ "SUM(CASE WHEN LoaiID = 0000001 AND[order].KHID is not null Then Soluong else 0 end)[Xuất BTP Chưa in], "
+ "SUM(CASE WHEN LoaiID = 0000002 AND[order].KHID is not null Then Soluong ELSE 0 END)[Xuất BTP Đã in], "
+ "SUM(CASE WHEN LoaiID = 0000003 AND[order].KHID is not null Then Soluong ELSE 0 END)[Xuất Thành Phẩm], "
+ "SUM(CASE WHEN LoaiID = 000004 AND[order].KHID is not null Then Soluong ELSE 0 END)[Xuất Sản phẩm lỗi] "
+ "from orderdetail "
+ "join [order] on orderdetail.donhangid = [order].donhangid "
+ "join product on orderdetail.barcode = product.barcode "
+ "where [order].xong = 1 "
+ "GROUP BY CONVERT(NVARCHAR(10), [order].ngayxong, 103), product.kyhieu,product.masp) New ) NewAll " + strSearch + "order by NewAll.[Ngày Nhập/ Xuất]" + " OFFSET " + skipRecord.ToString() + " ROWS FETCH NEXT " + rowPerPage.ToString() + " ROWS ONLY; ";
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand(query, connection))
{
SqlCeDataAdapter sda = new SqlCeDataAdapter(command);
DataTable dt = new DataTable();
sda.Fill(dt);
dtMain.Merge(dt);
dvgOrder.DataSource = dtMain;
dvgOrder.Columns[0].Width = 150;
//dvgOrder.Columns[0].DefaultCellStyle.Format = "dd/MM/yyyy HH:mm:ss";
//dvgOrder.Columns[1].DefaultCellStyle.Format = "dd/MM/yyyy HH:mm:ss";
dvgOrder.Columns[1].Width = 130;
//dgvOrderNhap.Columns["Xong"].ReadOnly = true;
dvgOrder.Columns["Ký Hiệu"].Width = 45;
dvgOrder.Columns["Nhập BTP Chưa in"].Width = 60;
this.dvgOrder.Columns["Nhập BTP Chưa in"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Nhập BTP Chưa in"].DefaultCellStyle.BackColor = Color.Aqua;
dvgOrder.Columns["Nhập BTP Đã in"].Width = 60;
this.dvgOrder.Columns["Nhập BTP Đã in"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Nhập BTP Đã in"].DefaultCellStyle.BackColor = Color.Aqua;
dvgOrder.Columns["Nhập Thành Phẩm"].Width = 55;
this.dvgOrder.Columns["Nhập Thành Phẩm"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Nhập Thành Phẩm"].DefaultCellStyle.BackColor = Color.Aqua;
dvgOrder.Columns["Nhập Sản phẩm lỗi"].Width = 60;
this.dvgOrder.Columns["Nhập Sản phẩm lỗi"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Nhập Sản phẩm lỗi"].DefaultCellStyle.BackColor = Color.Aqua;
dvgOrder.Columns["Xuất BTP Chưa in"].Width = 60;
this.dvgOrder.Columns["Xuất BTP Chưa in"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Xuất BTP Chưa in"].DefaultCellStyle.BackColor = Color.OrangeRed;
dvgOrder.Columns["Xuất BTP Đã in"].Width = 50;
this.dvgOrder.Columns["Xuất BTP Đã in"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Xuất BTP Đã in"].DefaultCellStyle.BackColor = Color.OrangeRed;
dvgOrder.Columns["Xuất Thành Phẩm"].Width = 60;
this.dvgOrder.Columns["Xuất Thành Phẩm"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Xuất Thành Phẩm"].DefaultCellStyle.BackColor = Color.OrangeRed;
dvgOrder.Columns["Xuất Sản phẩm lỗi"].Width = 60;
this.dvgOrder.Columns["Xuất Sản phẩm lỗi"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dvgOrder.Columns["Xuất Sản phẩm lỗi"].DefaultCellStyle.BackColor = Color.OrangeRed;
dvgOrder.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dvgOrder.Sort(dvgOrder.Columns["Ngày Nhập/ Xuất"], ListSortDirection.Descending);
}
}
}
/// <summary>
/// 将datatable数据写入工作表
/// </summary>
/// <param name="pDataDT">datatable</param>
/// <param name="pSheetName">工作表名称</param>
/// <param name="pBeginRow">开始行数</param>
/// <param name="pBeginCol"></param>
/// <param name="pInsertRowNo">从哪一行开始插入</param>
/// <param name="pInsertRows">插入行数</param>
/// <param name="pFormulaCell"></param>
/// <param name="pNumberFormatCell"></param>
public void SetData(System.Data.DataTable pDataDT, string pSheetName, int pBeginRow = 0, int pBeginCol = 0, int pInsertRowNo = 0, int pInsertRows = 0, List <int> pFormulaCell = null, List <int> pNumberFormatCell = null)
{
ws = wb.Sheets[pSheetName];
Range rngCell = null;
Range rngInsertRows = null;
Range startRange = null;
Range endRange = null;
Range range = null;
try
{
int beginRowIndex = pBeginRow == 0 ? 2 : pBeginRow;
int beginColIndex = pBeginCol == 0 ? 1 : pBeginCol;
#region 插入行
if (pInsertRowNo > 0)
{
rngInsertRows = (Range)ws.Rows[pBeginRow + 1, Type.Missing];
foreach (var DataDR in pDataDT.Rows)
{
rngInsertRows.Insert(XlDirection.xlDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
Marshal.FinalReleaseComObject(rngInsertRows);
}
#endregion
#region 设置单元格格式
if (pDataDT.Rows.Count > 0)
{
if (pNumberFormatCell == null)
{
pNumberFormatCell = new List <int>();
for (int j = 0; j < pDataDT.Columns.Count; j++)
{
pNumberFormatCell.Add(j + 1);
}
}
System.Threading.Thread threadForCulture = new System.Threading.Thread(delegate() { });
string format = threadForCulture.CurrentCulture.DateTimeFormat.ShortDatePattern;
List <string> NumberTypes = new List <string>()
{
"Byte", "Int16", "Int32", "Boolean"
};
foreach (int item in pNumberFormatCell)
{
string ColumnDataType = pDataDT.Columns[item - 1].DataType.Name;
startRange = ws.Cells[beginRowIndex, item];
endRange = ws.Cells[pDataDT.Rows.Count + beginRowIndex, item];
range = ws.Range[startRange, endRange];
Marshal.FinalReleaseComObject(startRange);
Marshal.FinalReleaseComObject(endRange);
if (NumberTypes.Contains(ColumnDataType))
{
range.NumberFormat = "0";
}
else if (ColumnDataType == "Double")
{
range.NumberFormat = "#,###0.000";
}
else if (ColumnDataType == "Decimal")
{
range.NumberFormat = "#,###0.000";
}
else if (ColumnDataType == "DateTime")
{
range.NumberFormat = format;
}
else
{
range.NumberFormat = "@";
}
Marshal.FinalReleaseComObject(startRange);
Marshal.FinalReleaseComObject(endRange);
Marshal.FinalReleaseComObject(range);
}
}
#endregion
#region 列头数据
if (pBeginRow == 0)
{
for (int i = 0; i < pDataDT.Columns.Count; i++)
{
rngCell = ws.Cells[1, i + beginColIndex];
rngCell.Value = pDataDT.Columns[i].ColumnName;
Marshal.FinalReleaseComObject(rngCell);
}
}
#endregion
#region 数据
if (pDataDT.Rows.Count > 0)
{
int rowCount = pDataDT.Rows.Count;
int colCount = pDataDT.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = pDataDT.Rows[i][j];
}
}
startRange = ws.Cells[beginRowIndex, beginColIndex];
endRange = ws.Cells[pDataDT.Rows.Count + beginRowIndex - 1, pDataDT.Columns.Count + beginColIndex - 1];
range = ws.Range[startRange, endRange];
range.Value = dataArray;
Marshal.FinalReleaseComObject(startRange);
Marshal.FinalReleaseComObject(endRange);
Marshal.FinalReleaseComObject(range);
}
#endregion
#region 拉公式
if (pFormulaCell != null && pDataDT.Rows.Count > 1)
{
foreach (int FormulaCellItem in pFormulaCell)
{
startRange = ws.Cells[beginRowIndex, FormulaCellItem];
endRange = ws.Cells[pDataDT.Rows.Count + beginRowIndex - 1, FormulaCellItem];
range = ws.Range[startRange, endRange];
startRange.AutoFill(range, XlAutoFillType.xlFillCopy);
Marshal.FinalReleaseComObject(startRange);
Marshal.FinalReleaseComObject(endRange);
Marshal.FinalReleaseComObject(range);
}
}
#endregion
}
catch (Exception ex)
{
throw;
}
finally
{
if (rngCell != null)
{
Marshal.FinalReleaseComObject(rngCell);
}
if (rngInsertRows != null)
{
Marshal.FinalReleaseComObject(rngInsertRows);
}
if (range != null)
{
Marshal.FinalReleaseComObject(range);
}
if (startRange != null)
{
Marshal.FinalReleaseComObject(startRange);
}
if (endRange != null)
{
Marshal.FinalReleaseComObject(endRange);
}
if (ws != null)
{
Marshal.FinalReleaseComObject(ws);
}
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel_SoTamTra(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, string tenkhoa, string ngaylinh)
{
Utility.CreateFolder(saveAsLocation);
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
// Excel.Range chartRange;
//Excel.Application xlApp;
//Excel.Workbook xlWorkBook;
//Excel.Worksheet xlWorkSheet;
//object misValue = System.Reflection.Missing.Value;
//Excel.Range chartRange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.get_Range("C1", "T1").Merge(true);
excelCellrange = excelSheet.get_Range("C1", "T1");
excelCellrange.Font.FontStyle = new Font("Times New Roman", 20, FontStyle.Bold);
if (PropertyLib._DuocNoitruProperties != null)
{
excelCellrange.Font.Size = PropertyLib._DuocNoitruProperties.TitleFont.Size;
excelCellrange.FormulaR1C1 = string.Format("{0}:{1}", PropertyLib._DuocNoitruProperties.tieudebaocao, ReporType);
}
else
{
excelCellrange.Font.Size = 18;
excelCellrange.FormulaR1C1 = string.Format("{0}:{1}", "SỔ TỔNG HỢP THUỐC HÀNG NGÀY", ReporType);
}
excelCellrange.HorizontalAlignment = 3;
excelCellrange.VerticalAlignment = VerticalAlignment.Center;
excelCellrange.Font.Bold = true;
excelSheet.get_Range("C2", "T2").Merge(true);
excelCellrange = excelSheet.get_Range("C2", "T2");
excelCellrange.Font.FontStyle = new Font("Times New Roman", 14, FontStyle.Bold);
excelCellrange.Font.Size = 14;
excelCellrange.FormulaR1C1 = string.Format("{0}:{1}", "Tên khoa", tenkhoa);
excelCellrange.HorizontalAlignment = 3;
excelCellrange.VerticalAlignment = VerticalAlignment.Center;
excelCellrange.Font.Bold = true;
//excelSheet = excelSheet.get_Range("b2", "e3");
//excelSheet.FormulaR1C1 = "MARK LIST";
//excelSheet.HorizontalAlignment = 3;
//excelSheet.VerticalAlignment = 3;
//excelSheet.Cells[3, 1] = ReporType;
excelSheet.Cells[3, 2] = "Ngày lĩnh : " + ngaylinh;
// loop through each row and add values to our sheet
int rowcount = 4;
// int colHeader = 4;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 5)
{
bool isBold = false;
excelSheet.Cells[rowcount, i] = GetCapTion(dataTable.Columns[i - 1].ColumnName, ref isBold);
//excelCellrange.Font=new Font() font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
excelSheet.Cells.Font.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, i], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.Font.Bold = isBold;
if (PropertyLib._DuocNoitruProperties != null)
{
excelCellrange.Font.Size =
PropertyLib._DuocNoitruProperties.ContentFont.Size;
}
else
{
excelCellrange.Font.Size = 8;
}
excelCellrange.Orientation = i >= 8 ? 90 : 0;
BorderCellRange(excelCellrange);
}
excelSheet.Cells[rowcount + 1, i] = datarow[i - 1].ToString();
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount + 1, 1], excelSheet.Cells[rowcount + 1, dataTable.Columns.Count]];
if (PropertyLib._DuocNoitruProperties != null)
{
excelCellrange.Font.Size =
PropertyLib._DuocNoitruProperties.ContentFont.Size;
}
else
{
excelCellrange.Font.Size = 8;
}
BorderCellRange(excelCellrange);
}
}
// excelSheet.get_Range("G4", "H2").Merge(true);
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[6, 1], excelSheet.Cells[6, dataTable.Columns.Count]];
//excelCellrange.BorderAround()
excelCellrange.EntireColumn.AutoFit();
// border.Weight = 2d;
BorderCellRange(excelCellrange);
// excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
// FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
// Get the current printer
if (System.IO.File.Exists(saveAsLocation))
{
File.Delete(saveAsLocation);
}
excelworkBook.SaveAs(saveAsLocation);;
if (PropertyLib._DuocNoitruProperties.isInThangMayin)
{
PrintMyExcelFile(saveAsLocation);
}
else
{
System.Diagnostics.Process.Start(saveAsLocation);
}
excelworkBook.Close();
//excel.Quit();
excel.Application.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(excelSheet);
//excelworkBook.Close(false, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(excelworkBook);
excel.Quit();
Marshal.FinalReleaseComObject(excel);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
GC.Collect();
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public void SearchDifData(System.Data.DataTable pDataDT, string pSheetName, string searchName, List <string> copyArray, List <string> sumArray)
{
ws = wb.Sheets[pSheetName];
Range rngCell = null;
Range rngInsertRows = null;
Range startRange = null;
Range endRange = null;
Range range = null;
try
{
#region 获取对比的colume,设置需要复制、求和的colume
int beginRowIndex = 2;
int beginColIndex = 1;
//需要对比的列
int searchColume = 1;
//需要求和的列
List <int> sumColume = new List <int>();
List <float> sumNumList = new List <float>();
//需要复制的列
List <int> copyColume = new List <int>();
for (int i = 0; i < pDataDT.Columns.Count; i++)
{
rngCell = ws.Cells[1, i + beginColIndex];
rngCell.Value = pDataDT.Columns[i].ColumnName;
if (rngCell.Value == searchName)
{
searchColume = i;
}
foreach (string item in sumArray)
{
if (rngCell.Value == item)
{
sumColume.Add(i);
sumNumList.Add(0);
}
}
foreach (string item in copyArray)
{
if (rngCell.Value == item)
{
copyColume.Add(i);
}
}
Marshal.FinalReleaseComObject(rngCell);
}
#endregion
int rowCount = pDataDT.Rows.Count;
int colCount = pDataDT.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
string pString = pDataDT.Rows[0][searchColume].ToString();
int addNum = 2;
for (int i = 0; i <= rowCount; i++)
{
if (i == rowCount)
{
SetData(pString, pSheetName, i + addNum, searchColume + 1);
for (int j = 0; j < sumColume.Count; j++)
{
SetData(sumNumList[j], pSheetName, i + addNum, sumColume[j] + 1);
}
foreach (int copy in copyColume)
{
string copyString = pDataDT.Rows[i - 1][copy].ToString();
SetData(copyString, pSheetName, i + addNum, copy + 1);
DeleteData(pSheetName, i + addNum - 1, copy + 1);
}
return;
}
string current = pDataDT.Rows[i][searchColume].ToString();
if (pString != current)
{
InsetRow(pDataDT, pSheetName, i + addNum);
SetData(pString, pSheetName, i + addNum, searchColume + 1);
for (int j = 0; j < sumColume.Count; j++)
{
SetData(sumNumList[j], pSheetName, i + addNum, sumColume[j] + 1);
sumNumList[j] = Convert.ToSingle(pDataDT.Rows[i][sumColume[j]].ToString());
}
foreach (int copy in copyColume)
{
string copyString = pDataDT.Rows[i - 1][copy].ToString();
SetData(copyString, pSheetName, i + addNum, copy + 1);
DeleteData(pSheetName, i + addNum - 1, copy + 1);
}
addNum++;
}
else
{
if (i != 0)
{
foreach (int copy in copyColume)
{
DeleteData(pSheetName, i + addNum - 1, copy + 1);
}
}
for (int j = 0; j < sumColume.Count; j++)
{
float sumAdd = new int();
sumAdd = Convert.ToSingle(pDataDT.Rows[i][sumColume[j]].ToString());
sumNumList[j] += sumAdd;
}
}
pString = current;
}
//startRange = ws.Cells[beginRowIndex, beginColIndex];
//endRange = ws.Cells[pDataDT.Rows.Count + beginRowIndex - 1, pDataDT.Columns.Count + beginColIndex - 1];
//range = ws.Range[startRange, endRange];
//range.Value = dataArray;
//Marshal.FinalReleaseComObject(startRange);
//Marshal.FinalReleaseComObject(endRange);
//Marshal.FinalReleaseComObject(range);
}
catch (Exception ex)
{
throw;
}
finally
{
if (rngCell != null)
{
Marshal.FinalReleaseComObject(rngCell);
}
if (rngInsertRows != null)
{
Marshal.FinalReleaseComObject(rngInsertRows);
}
if (range != null)
{
Marshal.FinalReleaseComObject(range);
}
if (startRange != null)
{
Marshal.FinalReleaseComObject(startRange);
}
if (endRange != null)
{
Marshal.FinalReleaseComObject(endRange);
}
if (ws != null)
{
Marshal.FinalReleaseComObject(ws);
}
}
}
private static void sendMail(Dictionary <String, List <String> > dataMapWithMails, String currentAppName, String recID)
{
System.Data.DataTable applicationNameWiseData = connectAndGetDataFromDB("DtlsOfApplication", ConfigurationManager.AppSettings["DtlsOfApplication"].Replace("RecordID", recID), "1");
List <String> emailIdList = dataMapWithMails.Keys.ToList();
foreach (String emailId in emailIdList)
{
StringBuilder builder = new StringBuilder();
StringBuilder mainBuiler = new StringBuilder();
List <String> emailIdData = dataMapWithMails[emailId];
if (applicationNameWiseData != null && applicationNameWiseData.Rows.Count > 0)
{
builder.Append("<span style=\"font-size:11.0pt;mso-fareast-font-family:"Times New Roman"\"><br>We are seeing Untagged Bugs( <b>" + emailIdData.Count + "</b> ) assigned to you for the application (details below) and we need your immediate attention here!</span><br><br>");
builder.Append("<span style=\"font-size:11.0pt;mso-fareast-font-family:"Times New Roman"\"><b>Details of the application : </b></span>");
builder.Append("<table class=\"imagetable\">");
DataRow row = applicationNameWiseData.Rows[0];
builder.Append("<tr bgcolor='#0F2BDE'>");
if (!"".Equals(row["NameDesc"]) && !row.IsNull("NameDesc"))
{
builder.Append("<th style=\"width:30%;\"><font color=\"#FFFFFF\">Application Name</font></th>");
}
if (!"".Equals(row["SubGrpAccLead"]) && !row.IsNull("SubGrpAccLead"))
{
builder.Append("<th style=\"width:15%;\"><font color=\"#FFFFFF\">PM Owner</font></th>");
}
if (!"".Equals(row["EngOnwer"]) && !row.IsNull("EngOnwer"))
{
builder.Append("<th style=\"width:10%;\"><font color=\"#FFFFFF\">Engineering Owner</font></th>");
}
if (!"".Equals(row["SubGrp"]) && !row.IsNull("SubGrp"))
{
builder.Append("<th style=\"width:30%;\"><font color=\"#FFFFFF\">Sub Group</font></th>");
}
if (!"".Equals(row["Priority"]) && !row.IsNull("Priority"))
{
builder.Append("<th style=\"width:10%;\"><font color=\"#FFFFFF\">Priority</font></th>");
}
if (!"".Equals(row["OpsStatus"]) && !row.IsNull("OpsStatus"))
{
builder.Append("<th style=\"width:15%;\"><font color=\"#FFFFFF\">Application Status</font></th>");
}
builder.Append("</tr>");
builder.Append("<tr>");
if (!"".Equals(row["NameDesc"]) && !row.IsNull("NameDesc"))
{
builder.Append("<td>" + row["NameDesc"] + "</td>");
}
if (!"".Equals(row["SubGrpAccLead"]) && !row.IsNull("SubGrpAccLead"))
{
builder.Append("<td>" + row["SubGrpAccLead"] + "</td>");
}
if (!"".Equals(row["EngOnwer"]) && !row.IsNull("EngOnwer"))
{
builder.Append("<td>" + row["EngOnwer"] + "</td>");
}
if (!"".Equals(row["SubGrp"]) && !row.IsNull("SubGrp"))
{
builder.Append("<td>" + row["SubGrp"] + "</td>");
}
if (!"".Equals(row["Priority"]) && !row.IsNull("Priority"))
{
builder.Append("<td>" + row["Priority"] + "</td>");
}
if (!"".Equals(row["OpsStatus"]) && !row.IsNull("OpsStatus"))
{
builder.Append("<td>" + row["OpsStatus"] + "</td>");
}
builder.Append("</tr>");
builder.Append("</table><br>");
}
else
{
builder.Append("<span style=\"font-size:11.0pt;mso-fareast-font-family:"Times New Roman"\"><br>We are seeing Untagged Bugs( " + emailIdData.Count + " ) assigned to you");
builder.Append("<br>");
}
builder.Append("<span style=\"font-size:11.0pt;mso-fareast-font-family:"Times New Roman"\">It's important to tag our bugs related to Accessibility so we can understand the Accessibility landscape and help us identify and engage with engineering teams in remediation. Refer: <a href='https://apc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Faka.ms%2Fbugtagging&data=02%7C01%7Cvenkatakrishn.sabbe%40hcl.com%7Cfe5ee50a7c2d4e83974008d74664b122%7C189de737c93a4f5a8b686f4ca9941912%7C0%7C0%7C637055270587327118&sdata=olS9Mm41kdLsE0%2FC%2FNwrFWzQzYVEq1Ga63Wd6DthvCY%3D&reserved=0' target='_blank' rel='noopener noreferrer' data-auth='Verified' originalsrc='https://aka.ms/bugtagging' shash='j6HH9xt0jdUu8gNTsiAVydHiuh4mOU68p45bGpyCl9nie/b67pf6RuFDY5aT/ZothN/zcJvHC8k/1D89Ryj6S062+rZZAri6ooCxh0bErAQN89+zlYPwy8w/4Qj/hyVBVSnIhsdXYiUPelOT8TWwEHMVsQDkOiaDLZRhHxzw7h0=' title='Original URL: https://aka.ms/bugtagging" +
"Click or tap if you trust this link.'>https://aka.ms/bugtagging</a> (link to overall CSEO Accessibility Tagging Guidelines). Below are also those tags for quick reference. Feel free to touch base with your groups <a href='https://apc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmicrosoft.sharepoint.com%2Fteams%2Fmeconnection%2FSitePages%2FAccessibility-Contacts.aspx&data=02%7C01%7Cvenkatakrishn.sabbe%40hcl.com%7Cfe5ee50a7c2d4e83974008d74664b122%7C189de737c93a4f5a8b686f4ca9941912%7C0%7C0%7C637055270587337112&sdata=y5BDL052uj%2BQlgAweOG2E16vNEkpU6xM9fsldqE8bsI%3D&reserved=0' target='_blank' rel='noopener noreferrer' data-auth='Verified' originalsrc='https://microsoft.sharepoint.com/teams/meconnection/SitePages/Accessibility-Contacts.aspx' shash='miBHLxMGT4zD1hmQe2pnph0JhDM2V50SIjFk1wSyGjgwAZ+FY67PRZ4rSxTRyTmKVe6Subu5sWmsjrLUu76cWvRTLtj/dOCoknas5ZX5Tx1KbaYMqm793+9p6UjQj/svaEsADeoZqQ6Hud4HTcURoA1RWomDtvKVmYJLEnzo7cs=' title='Original URL: https://microsoft.sharepoint.com/teams/meconnection/SitePages/Accessibility-Contacts.aspx" +
"Click or tap if you trust this link.'>Accessibility Track Lead </a> or us for any queries or concerns and we would help you here.<br><br></span>");
builder.Append("<table class='imagetable'>" +
"<tr bgcolor='#0F2BDE'>" +
"<th style=\"width: 15%;\"><font color=\"#FFFFFF\">Tag</font></th>" +
"<th style=\"width: 20%;\"><font color=\"#FFFFFF\">Description</font></th>" +
"<th style=\"width: 30%;\"><font color=\"#FFFFFF\">Example/Use</font></th>" +
"</tr>" +
"<tr>" +
"<td><span>A11y-COREDEV</span></td>" +
"<td><span>For any Core Development Team bug</span></td>" +
"<td> </td>" +
"</tr>" +
"<tr>" +
"<td><span>A11y-1STPARTY</span></td>" +
"<td><span>For any 1</span><sup>st</sup><span> party bug (MS Product Group)</span></td>" +
"<td><span>MS Product Groups include: SharePoint, Dynamics, Teams</span></td>" +
"</tr>" +
"<tr>" +
"<td>" +
"<span>FirstParty_<DependencyName></span>" +
"</td>" +
"<td>Additional 1st party tag, naming the actual 1st party</td>" +
"<td>" +
"<span>FirstParty_SPO (for Share Point Online)</span>" +
"<span>FirstParty_PowerBI (for Power BI)</span>" +
"<span>FirstParty_PowerApps (for Power apps)</span>" +
"<span>For a complete list, consult the <a href='https://microsoft.sharepoint.com/teams/meconnection/SitePages/First-Party-Points-of-Contact.aspx' data-interception='on'>full list of 1st party products</a></span>" +
"</td>" +
"</tr>" +
"<tr>" +
"<td><span>A11y-3RDPARTY</span></td>" +
"<td>For any 3rd party bug</td>" +
"<td><span>3</span><sup>rd</sup><span> party examples include: SAP, Fidelity, YouTube</span></td>" +
"</tr>" +
"<tr>" +
"<td>" +
"<span>ThirdParty_<DependencyName></span>" +
"</td>" +
"<td>Additional 3rd party tag, naming the actual 3rd party</td>" +
"<td>" +
"<span>ThirdParty_SAP (for SAP)</span>" +
"<span>ThirdParty_Adobe (for Adobe)</span>" +
"<span>ThirdParty_ServiceNow (for Service Now)</span>" +
"</td>" +
"</tr>" +
"</tbody>" +
"</table>");
builder.Append("<span style=\"mso-fareast-font-family:"Times New Roman"\"><u5:p></u5:p><o:p></o:p></span><br>");
builder.Append("<u5:p></u5:p>");
builder.Append("<body>");
List <String> leadMailDetails = new List <String>();
List <String> mailToDetails = new List <String>();
if (emailIdData.Count != 0)
{
builder.Append("<br>");
builder.Append("<span style=\"font-size:11.0pt;mso-fareast-font-family:"Times New Roman"\"><b>List of All Untagged Bugs Assigned to you : </b></span>");
builder.Append("<table class=\"imagetable\">");
builder.Append("<tr bgcolor=\"#0F2BDE\">");
builder.Append("<th><font color=\"#FFFFFF\">Bug Id</font></th>" +
"<th><font color=\"#FFFFFF\">RecID</font></th>" +
"<th><font color=\"#FFFFFF\">Application Name</font></th>" +
"<th><font color=\"#FFFFFF\">Severity</font></th>" +
"<th><font color=\"#FFFFFF\">Bug Identified</font></th>" +
"<th><font color=\"#FFFFFF\">Tagging</font></th>" +
"<th><font color=\"#FFFFFF\">Bug Aging</font></th>" +
"<th><font color=\"#FFFFFF\">Created By</font></th>" +
"<th><font color=\"#FFFFFF\">Created Date</font></th>" +
"<th><font color=\"#FFFFFF\">Area Path</font></th>" +
"<th><font color=\"#FFFFFF\">Iteration Path</font></th>" +
"<th><font color=\"#FFFFFF\">Bug Link</font></th>" +
"<th><font color=\"#FFFFFF\">Assigned To</font></th>"
);
builder.Append("</tr>");
foreach (String appData in emailIdData)
{
string[] appDataArray = appData.Split(',');
builder.Append("<tr>");
builder.Append("<td>" + appDataArray[0] + "</td>");
builder.Append("<td>" + appDataArray[1] + "</td>");
builder.Append("<td>" + appDataArray[2] + "</td>");
builder.Append("<td>" + appDataArray[3] + "</td>");
builder.Append("<td>" + appDataArray[4] + "</td>");
builder.Append("<td>" + appDataArray[5] + "</td>");
builder.Append("<td>" + appDataArray[6] + "</td>");
builder.Append("<td>" + appDataArray[7] + "</td>");
builder.Append("<td>" + appDataArray[8] + "</td>");
builder.Append("<td>" + appDataArray[9] + "</td>");
builder.Append("<td>" + appDataArray[10] + "</td>");
builder.Append("<td>" + appDataArray[11] + "</td>");
builder.Append("<td>" + appDataArray[12] + "</td>");
leadMailDetails.Add(appDataArray[15]);
mailToDetails.Add(appDataArray[12]);
builder.Append("</tr>");
}
builder.Append("</table>");
}
builder.Append("<br>");
builder.Append("<p style=\"color:blue;\">Thanks,<br>CSEO Assessment Service </p>");
builder.Append("</body>");
StringBuilder builder2 = new StringBuilder();
builder2.Append("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 3.2//EN\">");
builder2.Append("<html xmlns='http://www.w3.org/1999/xhtml'>");
builder2.Append("<head>");
builder2.Append("<title>");
builder2.Append("Need Attention: RecID: " + recID + " " + applicationNameWiseData.Rows[0]["NameDesc"] + "All Untagged Bugs Report :" + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString());
builder2.Append("</title>");
builder2.Append("<style type=\"text/css\">");
builder2.Append("table.MsoNormalTable{font-size:10.0pt;font-family:\"Calibri\",serif;}" +
"p.MsoNormal{margin-bottom:.0001pt;font-size:12.0pt;font-family:\"Calibri\",serif;margin-left: 0in;margin-right: 0in;margin-top: 0in;}" +
"h1{margin-right:0in;margin-left:0in;font-size:24.0pt;font-family:\"Calibri\",serif;font-weight:bold;}" +
"a:link{color:#0563C1;text-decoration:underline;text-underline:single;}p{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:\"Calibri\",serif;}");
builder2.Append("table.imagetable{font-family: verdana,Calibri,sans-serif;font-size:11px;" +
"color:#333333;border-width: 1px;border-color: #999999;border-collapse: collapse;}");
builder2.Append("table.imagetable th {background:#0070C0 url('cell-blue.jpg');border-width: 1px;padding: 8px;border-style: solid;border-color: #999999;}");
builder2.Append("table.imagetable td {background:#FFFFFF url('cell-grey.jpg');border-width: 1px;" +
"padding: 8px;border-style: solid;border-color: #999999;width:1px;white-space:nowrap;}</style>");
builder2.Append("</head>");
List <String> distinctLeadMails = leadMailDetails.Distinct().ToList();
List <String> distinctMailToDetails = mailToDetails.Distinct().ToList();
String leadMailString = String.Join(";", distinctLeadMails);
String mailToString = String.Join(";", distinctMailToDetails);
//String[] emailArray = emailId.Split(':');
builder2.Append("<span style=\"font-size:11.0pt;mso-fareast-font-family:"Times New Roman"\">Hi Team,</span><br>");
mainBuiler.Append(builder2);
mainBuiler.Append(builder);
string HtmlFile = mainBuiler.ToString();
Microsoft.Office.Interop.Outlook.Application oApp = new Microsoft.Office.Interop.Outlook.Application();
Microsoft.Office.Interop.Outlook.MailItem oMsg = (Microsoft.Office.Interop.Outlook.MailItem)oApp.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem);
//Live
oMsg.To = mailToString;
//QA
//oMsg.To = "*****@*****.**";
oMsg.Subject = "Need Attention: RecID: " + recID + " - " + applicationNameWiseData.Rows[0]["NameDesc"] + " - All Untagged Bugs Report :" + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString();
oMsg.HTMLBody = HtmlFile;
String localEmailList = ConfigurationManager.AppSettings["emailsList"].ToString();
List <String> localEmailListArray = localEmailList.Split(',').ToList();
bool containsValues = localEmailListArray.Intersect(distinctMailToDetails).Any();
if (containsValues)
{
//Live Only if QA need to comment
oMsg.CC = ConfigurationManager.AppSettings["DftSendMailTo"];
}
else
{
//Live Only if QA need to comment
oMsg.CC = ConfigurationManager.AppSettings["clientMailsCC"].ToString() + leadMailString + ";" + ConfigurationManager.AppSettings["DftSendMailTo"].ToString();
//oMsg.CC = "*****@*****.**";
}
oMsg.Send();
}
}
