public async Task <IActionResult> GetPaySlipFile(int[] accountIds, string fileFormat)
{
var paySlipDetails = await _paySlipService.GetPaySlipDetails(accountIds);
var paySlipDetailDtos = paySlipDetails.Select(x => _mapper.Map <PaySlipDetailDto>(x)).ToArray();
var workBook = ExcelFileHelper.GeneratExcelFileFromPaySlipDetails(paySlipDetailDtos);
fileFormat = fileFormat.ToLower();
switch (fileFormat)
{
case "csv":
return(File(workBook.GetBytes(SpreadSheetFileFormat.csv), "text/csv", "PaySlip.csv"));
case "xls":
return(File(workBook.GetBytes(SpreadSheetFileFormat.xls), "application/vnd.ms-excel",
"PaySlip.xls"));
case "xlsx":
return(File(workBook.GetBytes(SpreadSheetFileFormat.xlsx),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "PaySlip.xlsx"));
}
return(BadRequest("Error in fetching the pay slip file."));
}
private void ConvertToExcel(DataTable output)
{
string ExcelFileName = "";
ExcelFileHelper.GenerateExcelFile(output, out ExcelFileName);
System.Diagnostics.Process.Start(ExcelFileName);
}
public async Task <IActionResult> RawFileUpload(IFormFile file)
{
if (file == null)
{
return(BadRequest("No file data!"));
}
if (!IsRightFileType(file.FileName))
{
return(BadRequest("Wrong file type!"));
}
var dataRawRows = await ExcelFileHelper.ExtractDataFromSpreadsheetAsync(file);
if (dataRawRows.Count < 1)
{
return(BadRequest("Erro in reading out data, please check data file format and content."));
}
var payslipList = await _salaryInfoService.ImportSalaryInfo(dataRawRows);
var paySlipDetailDtos = payslipList.Select(x => _mapper.Map <PaySlipDetailDto>(x)).ToArray();
return(Ok(new { Data = JsonConvert.SerializeObject(paySlipDetailDtos) }));
}
internal static void Start()
{
DataTable dtInfotech = getAllData();
if (dtInfotech.Rows.Count > 0)
{
ExcelFileHelper.GenerateExcelFile(dtInfotech, DateTime.Now.ToString("dd-MM-yyyy"));
}
}
internal void Start()
{
DataTable dtUpdateClassification = getDTStruc();
Dictionary <string, string> dicNULL = new Dictionary <string, string>();
ArrayList AccountIDList = getAllAccountIDList();
foreach (string strAccountID in AccountIDList)
{
DataRow dr = dtUpdateClassification.NewRow();
Guid AccountID = new Guid(strAccountID);
using (SqlConnection Connection = SQLHelper.GetConnection())
{
SqlTransaction Transaction = default(SqlTransaction);
Transaction = Connection.BeginTransaction("WizardSync");
string CompanyName = GetAccountDetailsNameByAccountID(Connection, Transaction, AccountID);
dr[0] = CompanyName;
BOL.AccountContact.odsAccount mgr = new BOL.AccountContact.odsAccount();
//Update JV Category
mgr.UpdateAccountJVCategory_Wizard(Connection, Transaction, AccountID, new Guid(SyncHelper.AdminID), SyncHelper.AdminName);
string JVCategory = getJVCategory(Connection, Transaction, AccountID, ref dicNULL);
dr[1] = JVCategory;
//Update BumiClassification
mgr.UpdateAccountBumiClassification_Wizard(Connection, Transaction, AccountID, new Guid(SyncHelper.AdminID), SyncHelper.AdminName);
string BumiClass = getBumiClass(Connection, Transaction, AccountID, ref dicNULL);
dr[2] = BumiClass;
//Update Classification
mgr.UpdateAccountClassification_Wizard(Connection, Transaction, AccountID, new Guid(SyncHelper.AdminID), SyncHelper.AdminName);
string AccountClass = getAccountClass(Connection, Transaction, AccountID, ref dicNULL);
dr[3] = AccountClass;
Transaction.Commit();
dr[4] = "UPDATED";
dtUpdateClassification.Rows.Add(dr);
}
}
//2016 - 03 - 22 00:00:00 AM
string folderPath = ConfigurationSettings.AppSettings["CRMCleanUpExcelLoc"].ToString();
ExcelFileHelper.GenerateExcelFile(folderPath, dtUpdateClassification, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss tt"), "UpdateAccountClassification");
if (dicNULL.Keys.Count > 0)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\User\Desktop\NULClassification.txt"))
{
foreach (string key in dicNULL.Keys)
{
string Category = "";
if (dicNULL.ContainsKey(key))
{
Category = dicNULL[key].ToString();
}
file.WriteLine(key + ": " + Category);
}
System.Diagnostics.Process.Start("notepad.exe", @"C:\Users\User\Desktop\NULClassification.txt");
}
}
}
/// <summary>
/// 北环得到数据
/// </summary>
/// <returns></returns>
protected override object GetData(object state)
{
if (state == null)
{
Hashtable _data = new Hashtable();
using (Office.Excel.ForwardExcelReader reader = new Office.Excel.ForwardExcelReader(base.DataProvider.DataSourceFile))
{
reader.Open();
Office.Excel.ForwardReadWorksheet sheet = reader.Activate(1) as Office.Excel.ForwardReadWorksheet;
if (sheet == null)
{
return(null);
}
object parameter, content;
while (sheet.ReadNextRow())
{
parameter = null;
content = null;
while (sheet.ReadNextCell(false))
{
if (sheet.CurrentCell.ColumnIndex == 1)
{
parameter = sheet.GetContent();
}
else if (sheet.CurrentCell.ColumnIndex == 2)
{
content = sheet.GetContent();
}
}
if (parameter == null)
{
continue;
}
_data.Add(parameter, content == null ? "" : content.ToString());
}
}
string carType = _data[carTypeName] as string;
if (carType != null)
{
_data[carTypeName] = _carTypeList[carType];
_data.Add("CarType", carType);
}
return(_data);
}
else
{
object[] array = state as object[];
return(ExcelFileHelper.ReadTableData(this, array.Length <= 0 ? null : array[0] as string, array[1] as List <int>));
}
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="reportMonth">月份</param>
/// <param name="reportName">模块名字</param>
/// <param name="exprotExcelColumns">导出的列</param>
/// <returns></returns>
public string ExportExcel(object reportMonth, string reportName, dynamic exprotExcelColumns)
{
try
{
string directoryName = string.Concat(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "TempExprotExcel\\");
if (!Directory.Exists(directoryName))
{
Directory.CreateDirectory(directoryName);
}
DataTable dt = GetExportExcelData(reportMonth, reportName);//获取导出数据
DateTime repMonth = DateTime.MinValue;
DateTime.TryParse(reportMonth.ToString(), out repMonth);
string FileName = (repMonth != DateTime.MinValue ? Convert.ToDateTime(repMonth).ToString("yyyyMMdd") : Convert.ToDateTime(reportMonth).ToString("yyyyMMdd"));
if (reportName == "NoticeList")
{
FileName += "手术通知单.xlsx";
}
else if (reportName == "PickUpList")
{
FileName += "手术接送单.xlsx";
}
else
{
FileName += ".xlsx";
}
string FilePath = string.Concat(directoryName, FileName);
ExcelFileHelper.ExportExcelWithAspose(dt, JsonConvert.DeserializeObject <DataTable>(JsonConvert.SerializeObject(exprotExcelColumns)), FilePath);
return(FileName);
}
catch (Exception ex)
{
throw ex;
}
}
internal void Start()
{
DataTable dtUpdateClassification = getDTStruc();
Dictionary <string, string> dicNULL = new Dictionary <string, string>();
//get all the INACTIVE
//DataTable dtInactiveShareHolder = getAllInactiveShareHolder();
DataTable dtActiveShareHolder = getAllActiveShareHolder();
//DataTable dtNULLJVCategory= getAllNULLJVCategoryCID();
int RecordCounted = dtActiveShareHolder.Rows.Count;
//int RecordCounted = dtNULLJVCategory.Rows.Count;
Console.WriteLine("Total Record to clean up: " + RecordCounted.ToString());
//CHECK 1 by 1 ShareHolder
int Counter = 0;
foreach (DataRow dr1 in dtActiveShareHolder.Rows)
//foreach (DataRow dr1 in dtNULLJVCategory.Rows)
{
string FileID = dr1["MSCFileID"].ToString();
Guid ShareHolderID = new Guid(dr1["ShareHolderID"].ToString());
Guid AccountID = new Guid(dr1["AccountID"].ToString());
string ShareHolderName = dr1["ShareholderName"].ToString();
string Percentage = dr1["Percentage"].ToString();
string CountryRegion = getCountryName(dr1["CountryRegionID"].ToString());
string outPercentage = "";
if (CheckExistedShareHolderInView(FileID, ShareHolderName, Percentage, CountryRegion, out outPercentage))
{
Console.WriteLine("ShareHolder Found in View Table: " + ShareHolderName);
using (SqlConnection Connection = SQLHelper.GetConnection())
{
SqlTransaction Transaction = default(SqlTransaction);
Transaction = Connection.BeginTransaction("UpdateProcess");
DataRow dr = dtUpdateClassification.NewRow();
//Update to ACTIVE
int EffectedRows = 0;
SetShareHolderToActive(Connection, Transaction, ShareHolderID, outPercentage, out EffectedRows);
if (EffectedRows > 0)
{
dr[4] = ShareHolderName;
}
//UPDATE all CLASSIFICATION
string CompanyName = GetAccountDetailsNameByAccountID(Connection, Transaction, AccountID);
dr[0] = CompanyName;
BOL.AccountContact.odsAccount mgr = new BOL.AccountContact.odsAccount();
//Update JV Category
mgr.UpdateAccountJVCategory_Wizard(Connection, Transaction, AccountID, new Guid(SyncHelper.AdminID), SyncHelper.AdminName);
string JVCategory = getJVCategory(Connection, Transaction, AccountID, ref dicNULL);
dr[1] = JVCategory;
//Update BumiClassification
mgr.UpdateAccountBumiClassification_Wizard(Connection, Transaction, AccountID, new Guid(SyncHelper.AdminID), SyncHelper.AdminName);
string BumiClass = getBumiClass(Connection, Transaction, AccountID, ref dicNULL);
dr[2] = BumiClass;
//Update Classification
mgr.UpdateAccountClassification_Wizard(Connection, Transaction, AccountID, new Guid(SyncHelper.AdminID), SyncHelper.AdminName);
string AccountClass = getAccountClass(Connection, Transaction, AccountID, ref dicNULL);
dr[3] = AccountClass;
Transaction.Commit();
dr[5] = DateTime.Now.ToString("dd/MM/yyyy");
dtUpdateClassification.Rows.Add(dr);
Counter++;
Console.WriteLine("UPDATED for record no : " + Counter.ToString() + " / " + RecordCounted + " for company : " + CompanyName);
}
}
else
{
Counter++;
Console.WriteLine("ShareHolder NOT Found in View Table: " + ShareHolderName + " " + Counter + " / " + RecordCounted);
}
}
if (RecordCounted > 0)
{
string folderPath = ConfigurationSettings.AppSettings["CRMCleanUpExcelLoc"].ToString();
ExcelFileHelper.GenerateExcelFile(folderPath, dtUpdateClassification, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss tt"), "ShareHolderCleanUp");
}
if (dicNULL.Keys.Count > 0)
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\CRMCleanUp\NULLClassification.txt"))
{
foreach (string key in dicNULL.Keys)
{
string Category = "";
if (dicNULL.ContainsKey(key))
{
Category = dicNULL[key].ToString();
}
file.WriteLine(key + ": " + Category);
}
System.Diagnostics.Process.Start("notepad.exe", @"C:\CRMCleanUp\NULLClassification_" + DateTime.Now.ToString("dd-MM-yyyy") + ".txt");
}
}
}
object IDataProvider.ProvideData(object state)
{
if (state != null)
{
object[] array = state as object[];
return(ExcelFileHelper.ReadTableData(this, array[0] as string, array[1] as List <int>));
//return this.ReadTableData(array[0] as string, array[1] as List<int>);
}
Hashtable result = new Hashtable();
IWorkbook workbook = ExcelFileHelper.GetWorkbook(_connectionString);
ISheet sheet = workbook.GetSheetAt(0);
Hashtable columnHeader = new Hashtable();
IRow row = sheet.GetRow(0);
for (int colIndex = 0; colIndex < row.LastCellNum; colIndex++)
{
ICell cell = row.GetCell(colIndex);
if (cell != null)
{
columnHeader.Add(cell.ColumnIndex, ExcelFileHelper.GetCellValue(cell, null));
}
}
string key = "", value = "", code = "";
for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
for (int colIndex = 0; colIndex < row.LastCellNum; colIndex++)
{
ICell cell = row.GetCell(colIndex);
if (cell == null)
{
continue;
}
switch (columnHeader[colIndex] as string)
{
case "编号":
key = ExcelFileHelper.GetCellValue(cell, null);
break;
case "参数值":
value = ExcelFileHelper.GetCellValue(cell, null);
break;
case "PATAC统一参数编码":
code = ExcelFileHelper.GetCellValue(cell, null);
break;
}
}
if (key != null && result.ContainsKey(key) == false)
{
result.Add(key, new object[] { value, code });
}
}
//if (state != null)
// return null;
//if (conn == null)
// throw new ArgumentException("未指定有效的数据源！");
//Hashtable result = new Hashtable();
//DataTable data = conn.Tables[0];
//string keyStr = "编号";
//string valueStr = "参数值";
//string codeStr = "PATAC统一参数编码";
//int keyIndex = -1;
//int valueIndex = -1;
//int codeIndex = -1;
//for (int j = 0; j < data.Rows.Count; j++)
//{
// if (keyIndex < 0 || valueIndex < 0)
// {
// for (int i = 0; i < data.Columns.Count; i++)
// {
// string str = data.Rows[j][i].ToString();
// if (keyStr == str) { keyIndex = i; }
// if (valueStr == str) { valueIndex = i; }
// if (codeStr == str) { codeIndex = i; }
// if (keyIndex >= 0 && valueIndex >= 0 && codeIndex >= 0) break;
// }
// }
// else
// {
// string key = data.Rows[j][keyIndex].ToString();
// if (!string.IsNullOrEmpty(key) && !result.ContainsKey(key))
// {
// string value = data.Rows[j][valueIndex].ToString();
// result.Add(key, new object[] { value, data.Rows[j][codeIndex] });
// }
// }
//}
return(result);
}
