public string WriteCSVFile(string account, string perfix = "", string PathIFExist = "")
{
try {
string filename = DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString() + "-" + DateTime.Now.Hour.ToString() + "-" + DateTime.Now.Minute.ToString() + "-" + DateTime.Now.Second.ToString();
if (PathIFExist != "")
{
filename = PathIFExist;
}
if (Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + account) == false)
{
Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + account);
}
using (StreamWriter writer = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + account + "\\" + perfix + filename + ".csv", true))
{
if (PathIFExist != "")
{
Rfc4180Writer.WriteDataTable(SearchResultTable, writer, false);
}
else
{
Rfc4180Writer.WriteDataTable(SearchResultTable, writer, true);
}
}
return(AppDomain.CurrentDomain.BaseDirectory + account + "\\" + perfix + filename + ".csv");
}
catch (Exception ex)
{
return("");
}
}
public void PUSHToFile(string account, string path)
{
try
{
if (Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + account) == false)
{
Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + account);
}
using (StreamWriter writer = new StreamWriter(path, true))
{
Rfc4180Writer.WriteDataTable(SearchResultTable, writer, false);
}
}
catch (Exception ex)
{
}
}
/// <summary>
/// Build the excel adp report and send the temporary stored path of the adp report
/// </summary>
/// <returns> adp report path in temporary folder </returns>
private void buildCSVs()
{
try
{
var centerNo = Convert.ToInt32(ddSygmaCenterNo.SelectedValue);
var weekend = Convert.ToDateTime(ddlWeekending.SelectedItem.ToString());
var ds = ATMDB.GetDataSet("up_p_sendToAdp", centerNo, weekend, UserName,
AppSettings.GetAppSetting("adpmainreport", "ratetype", "required"),
AppSettings.GetAppSetting("adpbonusreport", "ratetype", "required"),
AppSettings.GetAppSetting("adpexpensereport", "ratetype", "required"),
AppSettings.GetAppSetting("adpptoreport", "ratetype", "required"));
var tableCount = ds.Tables.Count;
for (var i = 0; i < tableCount; i++)
{
const int TABLE_INDEX_ADP_REPORT = 0;
const int TABLE_INDEX_BONUS_REPORT_B = 1;
const int TABLE_INDEX_BONUS_REPORT_C = 2;
const int TABLE_INDEX_EXPENSE_REPORT = 3;
string baseFileName;
switch (i)
{
case TABLE_INDEX_ADP_REPORT:
baseFileName = "ADP_Main";
break;
case TABLE_INDEX_BONUS_REPORT_B:
baseFileName = "ADP_Bonus_Tax_B";
break;
case TABLE_INDEX_BONUS_REPORT_C:
baseFileName = "ADP_Bonus_Tax_C";
break;
case TABLE_INDEX_EXPENSE_REPORT:
baseFileName = "ADP_Expense";
break;
default:
baseFileName = "ADP_Pto";
break;
}
var dataTable = ds.Tables[i];
var columnCount = dataTable.Columns.Count;
var hasEarnings = false;
//write header
for (var c = 0; c < columnCount; c++)
{
if (dataTable.Columns[c].ColumnName.StartsWith("EarningCode"))
{
dataTable.Columns[c].Caption = "Earnings 3 Code";
hasEarnings = true;
}
else if (dataTable.Columns[c].ColumnName.StartsWith("EarningAmount"))
{
dataTable.Columns[c].Caption = "Earnings 3 Amount";
hasEarnings = true;
}
else if (dataTable.Columns[c].ColumnName.StartsWith("ExpenseCode"))
{
dataTable.Columns[c].Caption = "Adjust Ded Code";
hasEarnings = true;
}
else if (dataTable.Columns[c].ColumnName.StartsWith("ExpenseAmount"))
{
dataTable.Columns[c].Caption = "Adjust Ded Amount";
hasEarnings = true;
}
else if (dataTable.Columns[c].ColumnName.StartsWith("PayNumber"))
{
dataTable.Columns[c].Caption = "Pay #";
}
else if (dataTable.Columns[c].ColumnName.StartsWith("TaxFrequency"))
{
dataTable.Columns[c].Caption = "Tax Frequency";
}
else
{
dataTable.Columns[c].Caption = dataTable.Columns[c].ColumnName;
}
}
//write data
var fullFileName = getExcelFileFullName(baseFileName);
using (var writer = new StreamWriter(fullFileName))
Rfc4180Writer.WriteDataTable(dataTable, writer, true);
if (hasEarnings)
{
_email.Attachments.Add(new Attachment(fullFileName));
}
}
}
catch (Exception exp)
{
throw new Exception("Error creating ADP Report Excel", exp);
}
}
private void BtnGetData_Click(object sender, RoutedEventArgs e)
{
try
{
lblErrorTxt.Content = "";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString);
var outCsvFile1 = @"C:\test\result1.csv";
var outCsvFile2 = @"C:\test\result2.csv";
var sqlCmd = new SqlDataAdapter("CustOrdersDetail", conn);
sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter orderIDParameter = sqlCmd.SelectCommand.Parameters.Add("@OrderID", SqlDbType.Int);
orderIDParameter.Value = Convert.ToInt32(txtOrderID.Text);
var ds1 = new DataSet();
var ds2 = new DataTable();
using (ds1)
{
sqlCmd.Fill(ds1);
}
using (ds2)
{
sqlCmd.Fill(ds2);
}
//created by me
StringBuilder csvFileData = new StringBuilder();
if (ds1.Tables[0].Rows.Count > 0)
{
DataRow dr1 = ds1.Tables[0].Rows[0];
var headerCount = dr1.Table.Columns.Count;
int index = 1;
foreach (DataColumn dataColumn in dr1.Table.Columns)
{
csvFileData.Append(String.Format("\"{0}\"", dataColumn.ColumnName));
if (index < headerCount)
{
csvFileData.Append(",");
}
else
{
csvFileData.Append("\r\n");
}
index++;
}
foreach (DataRow dataRow in dr1.Table.Rows)
{
string strRow = string.Empty;
for (int x = 0; x <= headerCount - 1; x++)
{
strRow += "\"" + dataRow[x].ToString() + "\"";
if (x < headerCount - 1 && x >= 0)
{
strRow += ",";
}
}
csvFileData.Append(strRow + "\r\n");
}
File.WriteAllText(outCsvFile1, csvFileData.ToString());
}
//code found on internet static class use
using (StreamWriter writer = new StreamWriter(outCsvFile2))
{
Rfc4180Writer.WriteDataTable(ds2, writer, true);
}
conn.Close();
lblErrorTxt.Content = "Files have been sucessfully exported.";
}
catch (Exception ex)
{
lblErrorTxt.Content = ex.ToString();
}
}
