private string WriteElement(int key, string title, SalaryData data, List <string> children = null)
{
string element = string.Format("\"id\":\"{0}\",\"yearMonth\":\"{1}\"", key,
JsonSanitize(title));
string imageClass = "IconFormMonth";
if (children != null)
{
element += String.Format(",\"state\":\"{0}\",\"children\":{1}",
key == this._currentYear ? "open" : "closed",
"[" + String.Join(",", children) + "]");
imageClass = "IconFormYear";
}
string image = String.Format("<img src=\"{0}\" class=\"{1}\" yearmonth=\"{2}\" height=\"24px\" width=\"24px\" />",
"/Images/Flags/" + _country.Code.ToLower() + "-24px.png", imageClass, key);
element += String.Format(",\"grossPay\":\"{0:N0}\",\"additiveTax\":\"{1:N0}\",\"deductedTax\":\"{2:N0}\",\"costTotal\":\"{3:N0}\",\"taxTotal\":\"{4:N0}\",\"forms\":\"{5}\"",
data.GrossSalaryCents / 100.0, data.AdditiveTaxCents / 100.0, data.DeductedTaxCents / 100.0, (data.GrossSalaryCents + data.AdditiveTaxCents) / 100.0,
(data.AdditiveTaxCents + data.DeductedTaxCents) / 100.0, image.Replace("\"", "\\\""));
return("{" + element + "}");
}
private Dictionary <int, SalaryData> GetSalaryData()
{
Dictionary <int, SalaryData> result = new Dictionary <int, SalaryData>();
Salaries salaries = Salaries.ForOrganization(_authenticationData.CurrentOrganization, true);
foreach (Salary salary in salaries)
{
int monthKey = salary.PayoutDate.Year * 100 + salary.PayoutDate.Month;
if (!result.ContainsKey(monthKey))
{
result[monthKey] = new SalaryData();
}
result[monthKey].GrossSalaryCents += salary.GrossSalaryCents;
result[monthKey].AdditiveTaxCents += salary.AdditiveTaxCents;
result[monthKey].DeductedTaxCents += salary.SubtractiveTaxCents;
if (monthKey < _minKey)
{
_minKey = monthKey;
}
}
return(result);
}
public static void ExecuteBasicML()
{
var context = new MLContext();
//Load data
var trainData = context.Data.LoadFromTextFile <SalaryData>("Data//SalaryData.csv", hasHeader: true, separatorChar: ',');
//Build data
var pipeline = context.Transforms.Concatenate("Features", "YearsOfExperience")
.Append(context.Regression.Trainers.LbfgsPoissonRegression());
var modelPoisonRegression = pipeline.Fit(trainData);
//Evaluate
var predictionsPoison = modelPoisonRegression.Transform(trainData);
var matricesPoison = context.Regression.Evaluate(predictionsPoison);
Console.WriteLine($"PR RSquared: {matricesPoison.RSquared}");
//Predict
var newData = new SalaryData
{
YearsOfExperience = 1.2f
};
var predictionFuncPoison = context.Model.CreatePredictionEngine <SalaryData, SalaryPrediction>(modelPoisonRegression);
var predictionPoison = predictionFuncPoison.Predict(newData);
Console.WriteLine($"Poison Pred: {predictionPoison.PredictedSalary}");
}
public List <int> ImportData(Salary salary)
{
var excel = ExcelHelper.GetWorkbook(salary.FilePath);
var sheetIndex = excel.NumberOfSheets - 1;
var sheet = excel.GetSheetAt(sheetIndex);
var currentRowIndex = 0;
var failList = new List <int>();
do
{
var headerRow = FindHeader(sheet, currentRowIndex);
if (headerRow == -1)
{
sheetIndex--;
if (sheetIndex < 0)
{
break;
}
sheet = excel.GetSheetAt(sheetIndex);
continue;
}
var header = BuildHeader(sheet, headerRow);
if (header == null)
{
break;
}
for (var rowIndex = header.StartRow + header.RowHeight; rowIndex < 500; rowIndex++)
{
currentRowIndex = rowIndex;
var rowData = ReadData(sheet, header, rowIndex);
if (rowData == null)
{
currentRowIndex++;
break;
}
var data = new SalaryData {
SalaryId = salary.ID, Json = rowData.ToJson()
};
var userRealName = rowData["姓名"]?.ToString();
if (!string.IsNullOrEmpty(userRealName))
{
var user = DB.Users.FirstOrDefault(e => e.Username == userRealName);
if (user != null)
{
data.UserId = user.ID;
}
data.UserName = userRealName;
}
if (data.UserId == 0)
{
failList.Add(rowIndex + 1);
}
SaveData(data);
}
} while (true);
return(failList);
}
public void SaveData(SalaryData model)
{
var entity = DB.SalaryDatas.FirstOrDefault(e => e.UserId == model.UserId && e.SalaryId == model.SalaryId);
if (entity != null)
{
DB.SalaryDatas.Remove(entity);
}
DB.SalaryDatas.AddOrUpdate(model);
DB.SaveChanges();
}
protected void Page_Load(object sender, EventArgs e)
{
// Get auth data
this._authenticationData = GetAuthenticationDataAndCulture();
// Get current year
int yearLoop = _currentYear;
// Country asked for
this._country = Country.FromCode(Request["Country"]);
Response.ContentType = "application/json";
Dictionary <int, SalaryData> data = GetSalaryData();
List <string> yearElements = new List <string>();
while (_minKey < yearLoop * 1000) // at least one more year with data
{
List <string> monthElements = new List <string>();
SalaryData yearData = new SalaryData();
for (int month = 12; month >= 1; month--)
{
int key = yearLoop * 100 + month;
if (data.ContainsKey(key))
{
monthElements.Add(WriteElement(key, new DateTime(yearLoop, month, 1).ToString("yyyy MMMM"), data [key]));
yearData.GrossSalaryCents += data[key].GrossSalaryCents;
yearData.AdditiveTaxCents += data[key].AdditiveTaxCents;
yearData.DeductedTaxCents += data[key].DeductedTaxCents;
}
}
if (monthElements.Count > 0)
{
yearElements.Add(WriteElement(yearLoop, String.Format(Resources.Global.Global_YearX, yearLoop), yearData, monthElements));
}
yearLoop--;
}
Response.Output.WriteLine("[" + String.Join(",", yearElements) + "]");
Response.End();
}
public SalaryData getSalary(String Salary_Code)
{
SalaryData Salary_data = new SalaryData();
ConnectDB db = new ConnectDB();
SqlDataSource oracleObj = db.ConnectionOracle();
oracleObj.SelectCommand = "Select * From SALARY Where SALARY_CODE='" + Salary_Code + "'";
DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView rowData in allData)
{
Salary_data.Salary_Code = rowData["SALARY_CODE"].ToString();
Salary_data.Salary_Baht = rowData["SALARY_BAHT"].ToString();
Salary_data.Salary_Us = rowData["SALARY_US"].ToString();
}
return(Salary_data);
}
public List <SalaryData> getSalary()
{
List <SalaryData> Salary_data = new List <SalaryData>();
ConnectDB db = new ConnectDB();
SqlDataSource oracleObj = db.ConnectionOracle();
oracleObj.SelectCommand = "Select * From SALARY Order By SALARY_CODE";
DataView allData = (DataView)oracleObj.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView rowData in allData)
{
SalaryData Salary = new SalaryData();
Salary.Salary_Code = rowData["SALARY_CODE"].ToString();
Salary.Salary_Baht = rowData["SALARY_BAHT"].ToString();
Salary.Salary_Us = rowData["SALARY_US"].ToString();
Salary_data.Add(Salary);
}
return(Salary_data);
}
