public List <ReportSourceModel> GetAll()
{
// init list of report source model
var reportSourceModels = new List <ReportSourceModel>();
// get all payroll config
var payrollConfigs = CatalogController.GetAll("sal_PayrollConfig", null, null, null, false, null, null);
// check available payroll
if (payrollConfigs.Count > 0)
{
foreach (var payrollConfig in payrollConfigs)
{
// get list of columns
var columns = SalaryBoardConfigController.GetAll(payrollConfig.Id, null, null, null, null,
"[ColumnCode]", null).Select(s => s.ColumnCode).ToList();
// init report source model
var reportSourceModel = new ReportSourceModel {
Name = payrollConfig.Name
};
// set model props
// add column in to report source
reportSourceModel.Columns.AddRange(columns);
// add model into source list
reportSourceModels.Add(reportSourceModel);
}
}
// return
return(reportSourceModels);
}
/// <summary>
/// delete boardConfig
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnDelete_Click(object sender, DirectEventArgs e)
{
foreach (var item in RowSelectionModel1.SelectedRows)
{
//delete
SalaryBoardConfigController.Delete(int.Parse("0" + item.RecordID));
}
// reload data
gridSalaryConfig.Reload();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void cbxSelectColumnStore_OnRefreshData(object sender, StoreRefreshDataEventArgs e)
{
try
{
cbxSelectColumnStore.DataSource = SalaryBoardConfigController.GetAll();
cbxSelectColumnStore.DataBind();
}
catch (Exception ex)
{
Dialog.ShowNotification("Có lỗi xảy ra" + ex.Message);
}
}
/// <summary>
/// Sinh cột động
/// </summary>
public void InitDynamicColumn()
{
// get payroll
var payroll = PayrollController.GetById(int.Parse(hdfPayrollId.Text));
if (payroll == null)
{
return;
}
grpPayrollDetail.Title = payroll.Title;
// get salary board config
var salaryBoardConfigs = SalaryBoardConfigController.GetAll(payroll.ConfigId, true, null, null, null, null, null);
//sort list by columnExcel
salaryBoardConfigs.Sort((x, y) => CompareUtil.CompareStringByLength(x.ColumnExcel, y.ColumnExcel));
foreach (var config in salaryBoardConfigs)
{
var recordField = new RecordField
{
Name = config.ColumnCode
};
var column = new Column
{
Header = config.Display,
DataIndex = recordField.Name,
Width = 120,
Align = Alignment.Right,
Renderer = { Fn = "RenderValue" },
Tooltip = config.Display,
};
switch (config.DataType)
{
case SalaryConfigDataType.FieldFormula:
case SalaryConfigDataType.Formula:
column.Css = "font-weight:bold;";
break;
case SalaryConfigDataType.DynamicValue:
column.Css = "background:#e6eaf2;";
column.Editor.Add(new TextField());
break;
}
storePayrollDetail.AddField(recordField);
grpPayrollDetail.ColumnModel.Columns.Add(column);
}
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DownloadTemplate_Click(object sender, DirectEventArgs e)
{
// init table
var dataTable = new DataTable();
var salaryBoardConfigs = new List <SalaryBoardConfigModel>();
var payroll = new PayrollModel();
var salaryBoardInfos = new List <SalaryBoardInfoModel>();
// adjust table
dataTable.Rows.Add();
dataTable.Columns.Add(new DataColumn(EmployeeCode));
dataTable.Columns.Add(new DataColumn(FullName));
// get config
if (!string.IsNullOrEmpty(hdfConfigId.Text))
{
salaryBoardConfigs = SalaryBoardConfigController.GetAll(int.Parse(hdfConfigId.Text), null, null, null,
SalaryConfigDataType.DynamicValue, null, null);
}
// get payroll
if (!string.IsNullOrEmpty(hdfSalaryBoardListID.Text))
{
payroll = PayrollController.GetById(int.Parse(hdfSalaryBoardListID.Text));
}
if (salaryBoardConfigs != null)
{
foreach (var model in salaryBoardConfigs)
{
dataTable.Columns.Add(new DataColumn(model.Display));
}
}
// get record by department
if (payroll != null)
{
salaryBoardInfos = SalaryBoardInfoController.GetAll(null, payroll.Id.ToString(), null, null);
}
// fill employee name and code
for (var i = 0; i < salaryBoardInfos.Count; i++)
{
dataTable.Rows.Add();
dataTable.Rows[i][EmployeeCode] = salaryBoardInfos[i].EmployeeCode;
dataTable.Rows[i][FullName] = salaryBoardInfos[i].FullName;
}
ExportToExcel(dataTable, "~/" + Constant.PathTemplate, ImportSalaryExcelFile);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnEdit_Click(object sender, DirectEventArgs e)
{
if (string.IsNullOrEmpty(hdfId.Text))
{
ExtNet.Msg.Alert("Thông báo", "Bạn chưa chọn bản ghi nào").Show();
}
else
{
var config = SalaryBoardConfigController.GetById(Convert.ToInt32(hdfId.Text));
if (config != null)
{
cboExcelColumn.Text = config.ColumnExcel;
txtDisplay.Text = config.Display;
txtFormula.Text = config.Formula;
txtDescription.Text = config.Description;
chk_IsInUsed.Checked = config.IsInUsed;
chk_IsReadOnly.Checked = config.IsReadOnly;
cbxDataType.SetValue((int)config.DataType);
txtOrder.SetValue(config.Order);
hdfConfigId.Text = config.ConfigId.ToString();
if ((int)config.DataType == (int)SalaryConfigDataType.FieldValue)
{
cbxChoseFieldFixed.Show();
txtColumnCode.Hide();
hdfChoseFieldFixed.Text = config.ColumnCode;
cbxChoseFieldFixed.Text = config.ColumnCode;
}
else if ((int)config.DataType == (int)SalaryConfigDataType.FieldFormula)
{
cbxChoseFieldFormula.Show();
txtColumnCode.Hide();
cbxChoseFieldFixed.Hide();
hdfChoseFieldFormula.Text = config.ColumnCode;
cbxChoseFieldFormula.Text = config.ColumnCode;
}
else
{
cbxChoseFieldFixed.Hide();
txtColumnCode.Show();
txtColumnCode.Text = config.ColumnCode;
}
}
wdSalaryBoardConfig.Title = @"Cập nhật cấu hình bảng lương";
btUpdateNew.Hide();
btnUpdate.Show();
wdSalaryBoardConfig.Show();
}
}
/// <summary>
/// Nhân đôi cấu hình
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DuplicateData(object sender, DirectEventArgs e)
{
try
{
if (!string.IsNullOrEmpty(hdfConfigId.Text))
{
var result = sal_PayrollConfigServices.GetById(Convert.ToInt32(hdfConfigId.Text));;
if (result != null)
{
var payrollConfig = result;
payrollConfig.Name = payrollConfig.Name + @"Copy";
payrollConfig.CreatedBy = CurrentUser.User.UserName;
payrollConfig.CreatedDate = DateTime.Now;
payrollConfig.EditedDate = DateTime.Now;
payrollConfig.EditedBy = CurrentUser.User.UserName;
//create payrollConfig
var newConfig = sal_PayrollConfigServices.Create(payrollConfig);
//create detail
var details =
SalaryBoardConfigController.GetAll(Convert.ToInt32(hdfConfigId.Text), null, null, null, null, null, null);
foreach (var itemConfig in details)
{
var detailConfig = itemConfig;
detailConfig.ConfigId = newConfig.Id;
detailConfig.CreatedBy = CurrentUser.User.UserName;
detailConfig.CreatedDate = DateTime.Now;
detailConfig.EditedDate = DateTime.Now;
detailConfig.EditedBy = CurrentUser.User.UserName;
//create
SalaryBoardConfigController.Create(detailConfig);
}
}
// reload data
grdPayrollConfig.Reload();
}
}
catch (Exception exception)
{
Dialog.Alert(exception.Message);
}
}
/// <summary>
/// Sinh cột và record field động
/// </summary>
private void InitDynamicColumn()
{
if (string.IsNullOrEmpty(hdfSalaryBoardListID.Text))
{
return;
}
// get payroll
var payroll = PayrollController.GetById(Convert.ToInt32(hdfSalaryBoardListID.Text));
if (payroll == null)
{
return;
}
// get payroll config
var salaryBoardConfigs = SalaryBoardConfigController.GetAll(payroll.ConfigId, true, null, null,
SalaryConfigDataType.DynamicValue, null, null);
foreach (var salaryBoardConfig in salaryBoardConfigs)
{
// init record field
var recordField = new RecordField
{
Name = salaryBoardConfig.ColumnCode,
};
// init column
var column = new Column
{
DataIndex = recordField.Name,
Header = salaryBoardConfig.Display,
Width = 120,
Align = Alignment.Right,
Tooltip = salaryBoardConfig.Description,
Editor = { new TextField() },
Renderer = { Fn = "RenderValue" }
};
storeAdjustment.AddField(recordField);
gridColumnDynamic.ColumnModel.Columns.Add(column);
}
}
/// <summary>
/// get unused caculated code
/// </summary>
/// <returns></returns>
private List <ListItemModel> GetCalculateCodes(int num)
{
var codes = new List <ListItemModel>();
var configs = SalaryBoardConfigController.GetAll(int.Parse(hdfConfigId.Text), null, null, null, null, null, null);
for (var i = 1; i < num + configs.Count; i++)
{
codes.Add(new ListItemModel {
Key = i.ToExcelColumnName(), Value = i.ToExcelColumnName()
});
}
//check calculateCode exist
var colExcels = configs.Select(a => a.ColumnExcel).ToList();
codes.RemoveAll(o => colExcels.Contains(o.Value));
return(codes);
}
/// <summary>
/// Delete catalog
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Delete(object sender, DirectEventArgs e)
{
// init id
var param = e.ExtraParams["Id"];
// parse id
if (!int.TryParse(param, out var id) || id <= 0)
{
// parse error, show error
Dialog.ShowError("Có lỗi xảy ra trong quá trình xử lý");
return;
}
// delete
sal_PayrollConfigServices.Delete(id);
//delete detail
SalaryBoardConfigController.DeleteByCondition(id);
// reload data
grdPayrollConfig.Reload();
}
public void AfterEdit(int id, int prkey, string field, string oldValue, string newValue, SalaryBoardConfigInfo oj)
{
try
{
if (newValue == "" && oj.DataSourceFunction == "")
{
if (oj.ColumnName == "Formula" || oj.ColumnName == "DataSourceFunction")
{
newValue = "";
}
else
{
newValue = "0";
}
}
SalaryBoardConfigController sdc = new SalaryBoardConfigController();
sdc.Update(id, prkey, field, newValue);
this.grpSalaryConfig.Store.Primary.CommitChanges();
if (field == "IsInUsed")
{
string colName = oj.ColumnName;
int menuID = int.Parse(hdfSalaryTable.Text);
int v = bool.Parse(newValue) == true ? 1 : 0;
if (v == 0)
{
try
{
string sql = "UPDATE TienLuong.BangLuongDong SET " + colName + " = " + v +
" WHERE IdBangLuong IN (SELECT dsbl.ID FROM TienLuong.DanhSachBangLuong dsbl WHERE dsbl.MenuID = " + menuID + ")";
DataController.DataHandler.GetInstance().ExecuteNonQuery(sql);
}
catch (Exception)
{
}
}
}
}
catch (Exception ex) { Dialog.ShowError(ex.Message); }
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdateImportExcel_Click(object sender, DirectEventArgs e)
{
var workbook = new WorkBook();
// upload file
if (fileExcel.HasFile)
{
var path = UploadFile(fileExcel, Constant.PathTemplate);
if (path != null)
{
// Read data from excel
workbook.readXLSX(Path.Combine(Server.MapPath("~/"), Constant.PathTemplate, path));
// Export to datatable
var dataTable = workbook.ExportDataTable(0, //first row
0, //first col
workbook.LastRow + 1, //last row
workbook.LastCol + 1, //last col
true, //first row as header
false //convert to DateTime object if it match date pattern
);
// get config by ID
var salaryBoardConfig = SalaryBoardConfigController.GetAll(int.Parse(hdfConfigId.Text), null, null, null,
SalaryConfigDataType.DynamicValue, null, null);
foreach (DataRow row in dataTable.Rows)
{
// get employee code
var employeeCode = row[EmployeeCode].ToString();
if (string.IsNullOrEmpty(employeeCode))
{
continue;
}
// get record by employee code
var record = RecordController.GetByEmployeeCode(employeeCode);
foreach (DataColumn col in dataTable.Columns)
{
foreach (var item in salaryBoardConfig)
{
// check column name exists
if (item.Display != col.ColumnName)
{
continue;
}
// check empty string
var value = Convert.ToString(row[col], CultureInfo.InvariantCulture);
if (string.IsNullOrEmpty(value))
{
continue;
}
// check if dynamic column value exists
var salaryBoardDynamicColumn = SalaryBoardDynamicColumnController.GetAll(null, record.Id.ToString(), int.Parse(hdfPayrollId.Text), item.ColumnCode, true, null, null);
if (salaryBoardDynamicColumn.Count > 0)
{
// update dynamic column value
salaryBoardDynamicColumn.First().Value = value;
SalaryBoardDynamicColumnController.Update(salaryBoardDynamicColumn.First());
}
else
{
// create dynamic column value
var salaryBoardConfigColumn = new SalaryBoardDynamicColumnModel()
{
RecordId = record.Id,
SalaryBoardId = int.Parse(hdfPayrollId.Text),
ColumnCode = item.ColumnCode,
Value = value,
Display = item.Display
};
SalaryBoardDynamicColumnController.Create(salaryBoardConfigColumn);
}
}
}
}
}
}
else
{
Dialog.Alert("Bạn chưa chọn tệp tin đính kèm. Vui lòng chọn.");
return;
}
Dialog.Alert("Cập nhật thành công");
grpPayrollDetail.Reload();
}
/// <summary>
/// Insert or Update Catalog
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void InsertOrUpdate(object sender, DirectEventArgs e)
{
try
{
// init entity
var model = new ReportDynamicModel(null);
// check id
if (!string.IsNullOrEmpty(hdfId.Text) && Convert.ToInt32(hdfId.Text) > 0)
{
var result = ReportDynamicController.GetById(Convert.ToInt32(hdfId.Text));;
if (result != null)
{
model = result;
}
}
// set new props for entity
model.Name = txtName.Text;
model.Description = txtDescription.Text;
model.Template = (ReportTemplate)Convert.ToInt32(hdfReportTemplate.Text);
model.PaperKind = (ReportPaperKind)Convert.ToInt32(hdfReportPaperKind.Text);
model.Orientation = (ReportOrientation)Convert.ToInt32(hdfReportOrientation.Text);
model.GroupHeader1 = (ReportGroupHeader)Convert.ToInt32(hdfGroupHeader1.Text);
model.GroupHeader2 = (ReportGroupHeader)Convert.ToInt32(hdfGroupHeader2.Text);
model.GroupHeader3 = (ReportGroupHeader)Convert.ToInt32(hdfGroupHeader3.Text);
model.ParentDepartment = txtParentDepartment.Text;
model.Department = txtDepartment.Text;
model.Title = txtTitle.Text;
model.Duration = txtDuration.Text;
model.CreatedByTitle = txtCreatedByTitle.Text;
model.CreatedByNote = txtCreatedByNote.Text;
model.CreatedByName = txtCreatedByName.Text;
model.ReviewedByTitle = txtReviewedByTitle.Text;
model.ReviewedByNote = txtReviewedByNote.Text;
model.ReviewedByName = txtReviewedByName.Text;
model.SignedByTitle = txtSignedByTitle.Text;
model.SignedByNote = txtSignedByNote.Text;
model.SignedByName = txtSignedByName.Text;
model.Status = (ReportStatus)Convert.ToInt32(hdfStatus.Text);
// parse report source to json
if (!string.IsNullOrEmpty(hdfReportSource.Text))
{
var payrollConfigId = Convert.ToInt32(hdfReportSource.Text);
switch (model.Template)
{
case ReportTemplate.EnterprisePayroll:
var payrollConfigs = SalaryBoardConfigController.GetAll(payrollConfigId, true, null, null, null, "[ColumnCode]", null);
var lstItemModel = new List <ListItemModel>();
lstItemModel.Add(new ListItemModel("FullName", "FullName"));
lstItemModel.Add(new ListItemModel("EmployeeCode", "EmployeeCode"));
lstItemModel.Add(new ListItemModel("PositionName", "PositionName"));
lstItemModel.AddRange(payrollConfigs.Select(c => new ListItemModel(c.ColumnCode, c.ColumnCode)));
model.ReportSource = JSON.Serialize(lstItemModel);
model.Argument = payrollConfigId.ToString();
break;
}
}
// check entity id
if (model.Id > 0)
{
// update
ReportDynamicController.Update(model);
}
else
{
// insert
ReportDynamicController.Create(model);
}
// hide window
wdSetting.Hide();
// reload data
gpReport.Reload();
}
catch (Exception exception)
{
Dialog.ShowError(exception);
}
}
public void LoadTongLuong()
{
try
{
int idBangLuong = int.Parse("0" + hdfIDBangLuong.Text);
//DataTable table = DataController.DataHandler.GetInstance().ExecuteDataTable("TienLuong_TinhTongLuongGroupHeader", "@MaDonVi", "@IDBangLuong", "@seachKey",
// "", idBangLuong, txtSearch.Text);
List <SalaryBoardConfigInfo> lists = new SalaryBoardConfigController().GetSalaryBoardConfig(MenuID);
string sqlString = string.Empty;
foreach (var it in lists)
{
if (it.AllowSum == true)
{
sqlString += "SUM(ISNULL(bld." + it.ColumnName + ", 0)) AS '" + it.ColumnName + "',";
}
}
int pos = sqlString.LastIndexOf(',');
if (pos != -1)
{
sqlString = sqlString.Remove(pos);
}
if (string.IsNullOrEmpty(sqlString) || sqlString == "")
{
return;
}
sqlString = "SELECT " + sqlString + string.Format(@"FROM (
SELECT hs.PR_KEY,
hs.MA_CB,
hs.TEN_CB,
hs.HO_TEN,
hs.MA_DONVI
FROM HOSO hs
) AS hs
LEFT JOIN TienLuong.BangLuongDong bld
ON hs.PR_KEY = bld.PrKeyHoSo
WHERE (
LEN('" + txtSearch.Text + @"') = 0
OR hs.MA_CB LIKE N'%' + '" + txtSearch.Text + @"' + '%'
OR hs.HO_TEN LIKE N'%' + '" + txtSearch.Text + @"' + '%'
)
AND (LEN('') = 0 OR hs.MA_DONVI in (select MA_DONVI from f_GetDanhSachMaDonVi('')))
AND " + idBangLuong + @"=bld.IDBangLuong
GROUP BY bld.IDBangLuong");
DataTable table = DataController.DataHandler.GetInstance().ExecuteDataTable(sqlString);
if (table.Rows.Count > 0)
{
DataRow record = table.Rows[0];
string javascript = string.Empty;
foreach (var item in lists)
{
if (item.AllowSum == true)
{
javascript += "dpf" + item.ColumnName + ".setValue(RenderVNDGroupHeader(" + record[item.ColumnName] + "));";
}
}
RM.RegisterClientScriptBlock("SetSum", javascript);
}
}
catch (Exception ex)
{
Dialog.ShowError(ex.Message);
}
}
