public static int CreateWithTaxBracket(double value, int excessmult)
{
var isnert_query = $@"INSERT INTO `msadb`.`withtax_value` (`value`, `excessmult`) VALUES ('{value}', '{excessmult}');";
SQLTools.ExecuteQuery(isnert_query);
return(SQLTools.GetInt("select last_insert_id()"));
}
public static int GetNumberOfDependents(int GID)
{
String q = @"SELECT count(DeID) FROM msadb.dependents where GID={0};";
q = string.Format(q, GID);
return(SQLTools.GetInt(q) - 2);
}
public static void CreateWithTaxQuery(DateTime date_effective)
{
SingleTransactionQuery =
"insert into `msadb`.`withtax_bracket` (`estatus`, `bracket`, `taxid`, `contrib_id`) VALUES ";
var insert_contribdetail =
$@"INSERT INTO `msadb`.`contribdetails`
(`date_effective`, `date_dissolved`, `type`, `status`)
VALUES ('{date_effective.ToString("yyyy-MM-dd")}', '{"9999-12-31"}', '{2}', '{2}');";
SQLTools.ExecuteQuery(insert_contribdetail);
contrib_id = SQLTools.GetInt("select last_insert_id()");
}
public void SetAttendance(int AtID, int ti_hh, int ti_mm, String ti_ampm, int to_hh, int to_mm, String to_ampm)
{
int did = SQLTools.GetInt("select did from attendance where AtID=" + AtID);
TimeSpan ts = GetTimeDiff(ti_hh, ti_mm, ti_ampm, to_hh, to_mm, to_ampm);
DateTime ti = GetDateTime(ti_hh, ti_mm, ti_ampm);
DateTime to = GetDateTime(to_hh, to_mm, to_ampm);
//compare with tama na sched for overtime
DataRow dt = SQLTools.ExecuteQuery("select * from dutydetails where DID=" + did).Rows[0];
DateTime start_night = GetDateTime(10, 00, "PM");
DateTime end_night = GetDateTime(6, 0, "AM").AddDays(1);
String q = @"UPDATE `msadb`.`attendance` SET `TimeIn`='{1}', `TimeOut`='{2}' WHERE `AtID`='{0}';";
q = Format(q, AtID, ti.ToString("hh:mm tt"), to.ToString("hh:mm tt"));
SQLTools.ExecuteNonQuery(q);
}
public static bool Authenticate(string uname, string pword)
{
int uid;
try {
String q = @"select * from account where uname='" + uname + "'";
DataTable dt = SQLTools.ExecuteQuery_(q);
//Console.WriteLine(Crypt.HashPassword("clerk"));
string db_hash = dt.Rows[0]["hash"].ToString();
if (Crypt.Verify(pword, db_hash))
{
UserName = dt.Rows[0]["uname"].ToString();
AccountType = int.Parse(dt.Rows[0]["type"].ToString());
uid = int.Parse(dt.Rows[0]["accid"].ToString());
LoggedInUser = uid;
SQLTools.conn.Close();
SQLTools.ExecuteNonQuery($@"
INSERT INTO `msadb`.`loginhistory`
(`uid`, `session_start`)
VALUES ('{uid}', '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}');
");
SessionId = SQLTools.GetInt("select last_insert_id()");
SQLTools.ExecuteNonQuery($@"SET @cuser={SessionId};");
SQLTools.ExecuteNonQuery($@"call init_checkdate_all");
return(true);
}
else
{
return(false);
}
}
catch (Exception) {
SQLTools.conn.Close();
return(false);
}
}
public static int GetCivilStatus(int GID)
{
string q = @"SELECT CivilStatus from msadb.guards where GID=" + GID;
return(SQLTools.GetInt(q));
}
public PayrollReport(int GID, int year, int month, int period)
{
// Set Days of Work.
this.DaysOfWork = SQLTools.GetInt($@"
SELECT
COUNT(*)
FROM
attendance
LEFT JOIN `msadb`.`period` ON period.pid = attendance.pid
WHERE
gid = {GID}
AND('{year}-{month.ToString().PadLeft(2,'0')}-{(period==1?1:15)}' <= `Date` AND `Date` <= '{year}-{month}-{(period == 1 ? 14 : 31)}');
");
// Get Guard and Payroll data.
var q = $@"
SELECT
*
FROM
msadb.payroll
LEFT JOIN msadb.guards ON msadb.payroll.gid = msadb.guards.gid
WHERE
guards.gid = {GID}
AND month = {month}
AND period = {period}
AND YEAR = {year};
";
DataRow dt = SQLTools.ExecuteQuery(q).Rows[0];
this.FN = dt["FN"].ToString();
this.MN = dt["MN"].ToString();
this.LN = dt["LN"].ToString();
this.Rate = double.Parse(dt["basicpayhourly"].ToString()) * 8;
// Set the derivables
this.EmergencyAllowance = double.Parse(dt["emergencyallowance"].ToString());
this.CashBond = double.Parse(dt["cashbond"].ToString());
this.Cola = double.Parse(dt["cola"].ToString());
this.ThirteenthMonthPay = double.Parse(dt["thirteenth"].ToString());
this.CashAdvance = double.Parse(dt["cashadv"].ToString());
// Set the primitives
this.HDMF = double.Parse(dt["pagibig"].ToString());
this.PHIC = double.Parse(dt["philhealth"].ToString());
this.Sss = double.Parse(dt["sss"].ToString());
this.Withtax = double.Parse(dt["withtax"].ToString());
this.hc = (Dictionary <string, HourCostPair>)Payroll._DeserializeObject(
System.Text.Encoding.Default.GetString((byte[])dt["hc_serializable"])
);
this.TotalSummary = (Dictionary <string, HourCostPair>)Payroll._DeserializeObject(
System.Text.Encoding.Default.GetString((byte[])dt["totalsummary_serializable"])
);
this.TotalAmount = TotalSummary["total"].total;
HourCostPair notOvertime =
hc["nsu_proper_day_normal"] +
hc["nsu_proper_day_special"] +
hc["nsu_proper_day_regular"] +
hc["nsu_proper_night_normal"] +
hc["nsu_proper_night_special"] +
hc["nsu_proper_night_regular"] +
hc["sun_proper_day_normal"] +
hc["sun_proper_day_special"] +
hc["sun_proper_day_regular"] +
hc["sun_proper_night_normal"] +
hc["sun_proper_night_special"] +
hc["sun_proper_night_regular"];
this.TotalRegularWage = notOvertime.total;
/*
*
* nsu_overtime_day_special
* nsu_overtime_day_regular
* nsu_overtime_night_special
* nsu_overtime_night_regular
* sun_overtime_day_normal
* sun_overtime_day_special
* sun_overtime_day_regular
* sun_overtime_night_normal
* sun_overtime_night_special
* sun_overtime_night_regular
*/
this.overtime.RegularDay = hc["nsu_overtime_day_normal"] + hc["nsu_overtime_night_normal"];
this.overtime.SundayAndHoliday =
hc["nsu_overtime_day_special"] +
hc["nsu_overtime_day_regular"] +
hc["nsu_overtime_night_special"] +
hc["nsu_overtime_night_regular"] +
hc["sun_overtime_day_normal"] +
hc["sun_overtime_day_special"] +
hc["sun_overtime_day_regular"] +
hc["sun_overtime_night_normal"] +
hc["sun_overtime_night_special"] +
hc["sun_overtime_night_regular"];
this.NetAmountPaid = TotalSummary["total"].total + this.CashAdvance + this.CashBond + this.Cola + this.EmergencyAllowance - this.HDMF - this.PHIC - this.Sss + this.ThirteenthMonthPay - this.Withtax;
}
