///<summary>Inserts one TimeCardRule into the database. Provides option to use the existing priKey.</summary>
public static long Insert(TimeCardRule timeCardRule,bool useExistingPK){
if(!useExistingPK && PrefC.RandomKeys) {
timeCardRule.TimeCardRuleNum=ReplicationServers.GetKey("timecardrule","TimeCardRuleNum");
}
string command="INSERT INTO timecardrule (";
if(useExistingPK || PrefC.RandomKeys) {
command+="TimeCardRuleNum,";
}
command+="EmployeeNum,OverHoursPerDay,AfterTimeOfDay,BeforeTimeOfDay) VALUES(";
if(useExistingPK || PrefC.RandomKeys) {
command+=POut.Long(timeCardRule.TimeCardRuleNum)+",";
}
command+=
POut.Long (timeCardRule.EmployeeNum)+","
+ POut.Time (timeCardRule.OverHoursPerDay)+","
+ POut.Time (timeCardRule.AfterTimeOfDay)+","
+ POut.Time (timeCardRule.BeforeTimeOfDay)+")";
if(useExistingPK || PrefC.RandomKeys) {
Db.NonQ(command);
}
else {
timeCardRule.TimeCardRuleNum=Db.NonQ(command,true);
}
return timeCardRule.TimeCardRuleNum;
}
///<summary>Returns true if Update(TimeCardRule,TimeCardRule) would make changes to the database.
///Does not make any changes to the database and can be called before remoting role is checked.</summary>
public static bool UpdateComparison(TimeCardRule timeCardRule, TimeCardRule oldTimeCardRule)
{
if (timeCardRule.EmployeeNum != oldTimeCardRule.EmployeeNum)
{
return(true);
}
if (timeCardRule.OverHoursPerDay != oldTimeCardRule.OverHoursPerDay)
{
return(true);
}
if (timeCardRule.AfterTimeOfDay != oldTimeCardRule.AfterTimeOfDay)
{
return(true);
}
if (timeCardRule.BeforeTimeOfDay != oldTimeCardRule.BeforeTimeOfDay)
{
return(true);
}
if (timeCardRule.IsOvertimeExempt != oldTimeCardRule.IsOvertimeExempt)
{
return(true);
}
if (timeCardRule.MinClockInTime != oldTimeCardRule.MinClockInTime)
{
return(true);
}
return(false);
}
///<summary>Inserts one TimeCardRule into the database. Returns the new priKey.</summary>
public static long Insert(TimeCardRule timeCardRule)
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
timeCardRule.TimeCardRuleNum = DbHelper.GetNextOracleKey("timecardrule", "TimeCardRuleNum");
int loopcount = 0;
while (loopcount < 100)
{
try {
return(Insert(timeCardRule, true));
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex) {
if (ex.Number == 1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated"))
{
timeCardRule.TimeCardRuleNum++;
loopcount++;
}
else
{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else
{
return(Insert(timeCardRule, false));
}
}
///<summary>Inserts one TimeCardRule into the database. Returns the new priKey.</summary>
internal static long Insert(TimeCardRule timeCardRule)
{
if(DataConnection.DBtype==DatabaseType.Oracle) {
timeCardRule.TimeCardRuleNum=DbHelper.GetNextOracleKey("timecardrule","TimeCardRuleNum");
int loopcount=0;
while(loopcount<100){
try {
return Insert(timeCardRule,true);
}
catch(Oracle.DataAccess.Client.OracleException ex){
if(ex.Number==1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated")){
timeCardRule.TimeCardRuleNum++;
loopcount++;
}
else{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else {
return Insert(timeCardRule,false);
}
}
///<summary>Inserts one TimeCardRule into the database. Provides option to use the existing priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(TimeCardRule timeCardRule, bool useExistingPK)
{
bool isRandomKeys = Prefs.GetBoolNoCache(PrefName.RandomPrimaryKeys);
string command = "INSERT INTO timecardrule (";
if (!useExistingPK && isRandomKeys)
{
timeCardRule.TimeCardRuleNum = ReplicationServers.GetKeyNoCache("timecardrule", "TimeCardRuleNum");
}
if (isRandomKeys || useExistingPK)
{
command += "TimeCardRuleNum,";
}
command += "EmployeeNum,OverHoursPerDay,AfterTimeOfDay,BeforeTimeOfDay,IsOvertimeExempt) VALUES(";
if (isRandomKeys || useExistingPK)
{
command += POut.Long(timeCardRule.TimeCardRuleNum) + ",";
}
command +=
POut.Long(timeCardRule.EmployeeNum) + ","
+ POut.Time(timeCardRule.OverHoursPerDay) + ","
+ POut.Time(timeCardRule.AfterTimeOfDay) + ","
+ POut.Time(timeCardRule.BeforeTimeOfDay) + ","
+ POut.Bool(timeCardRule.IsOvertimeExempt) + ")";
if (useExistingPK || isRandomKeys)
{
Db.NonQ(command);
}
else
{
timeCardRule.TimeCardRuleNum = Db.NonQ(command, true, "TimeCardRuleNum", "timeCardRule");
}
return(timeCardRule.TimeCardRuleNum);
}
///<summary>Inserts one TimeCardRule into the database. Provides option to use the existing priKey.</summary>
public static long Insert(TimeCardRule timeCardRule, bool useExistingPK)
{
if (!useExistingPK && PrefC.RandomKeys)
{
timeCardRule.TimeCardRuleNum = ReplicationServers.GetKey("timecardrule", "TimeCardRuleNum");
}
string command = "INSERT INTO timecardrule (";
if (useExistingPK || PrefC.RandomKeys)
{
command += "TimeCardRuleNum,";
}
command += "EmployeeNum,OverHoursPerDay,AfterTimeOfDay,BeforeTimeOfDay,IsOvertimeExempt,MinClockInTime) VALUES(";
if (useExistingPK || PrefC.RandomKeys)
{
command += POut.Long(timeCardRule.TimeCardRuleNum) + ",";
}
command +=
POut.Long(timeCardRule.EmployeeNum) + ","
+ POut.Time(timeCardRule.OverHoursPerDay) + ","
+ POut.Time(timeCardRule.AfterTimeOfDay) + ","
+ POut.Time(timeCardRule.BeforeTimeOfDay) + ","
+ POut.Bool(timeCardRule.IsOvertimeExempt) + ","
+ POut.Time(timeCardRule.MinClockInTime) + ")";
if (useExistingPK || PrefC.RandomKeys)
{
Db.NonQ(command);
}
else
{
timeCardRule.TimeCardRuleNum = Db.NonQ(command, true, "TimeCardRuleNum", "timeCardRule");
}
return(timeCardRule.TimeCardRuleNum);
}
public static void CreatePMTimeRule(long emp, TimeSpan afterTime)
{
TimeCardRule tcr = new TimeCardRule();
tcr.EmployeeNum = emp;
tcr.AfterTimeOfDay = afterTime;
TimeCardRules.Insert(tcr);
return;
}
public static void CreateAMTimeRule(long emp, TimeSpan beforeTime)
{
TimeCardRule tcr = new TimeCardRule();
tcr.EmployeeNum = emp;
tcr.BeforeTimeOfDay = beforeTime;
TimeCardRules.Insert(tcr);
return;
}
public static void CreateHoursTimeRule(long emp, TimeSpan hoursPerDay)
{
TimeCardRule tcr = new TimeCardRule();
tcr.EmployeeNum = emp;
tcr.OverHoursPerDay = hoursPerDay;
TimeCardRules.Insert(tcr);
return;
}
///<summary>Updates one TimeCardRule in the database.</summary>
internal static void Update(TimeCardRule timeCardRule)
{
string command = "UPDATE timecardrule SET "
+ "EmployeeNum = " + POut.Long(timeCardRule.EmployeeNum) + ", "
+ "OverHoursPerDay= " + POut.Time(timeCardRule.OverHoursPerDay) + ", "
+ "AfterTimeOfDay = " + POut.Time(timeCardRule.AfterTimeOfDay) + " "
+ "WHERE TimeCardRuleNum = " + POut.Long(timeCardRule.TimeCardRuleNum);
Db.NonQ(command);
}
///<summary>Updates one TimeCardRule in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables. Returns true if an update occurred.</summary>
public static bool Update(TimeCardRule timeCardRule, TimeCardRule oldTimeCardRule)
{
string command = "";
if (timeCardRule.EmployeeNum != oldTimeCardRule.EmployeeNum)
{
if (command != "")
{
command += ",";
}
command += "EmployeeNum = " + POut.Long(timeCardRule.EmployeeNum) + "";
}
if (timeCardRule.OverHoursPerDay != oldTimeCardRule.OverHoursPerDay)
{
if (command != "")
{
command += ",";
}
command += "OverHoursPerDay = " + POut.Time(timeCardRule.OverHoursPerDay) + "";
}
if (timeCardRule.AfterTimeOfDay != oldTimeCardRule.AfterTimeOfDay)
{
if (command != "")
{
command += ",";
}
command += "AfterTimeOfDay = " + POut.Time(timeCardRule.AfterTimeOfDay) + "";
}
if (timeCardRule.BeforeTimeOfDay != oldTimeCardRule.BeforeTimeOfDay)
{
if (command != "")
{
command += ",";
}
command += "BeforeTimeOfDay = " + POut.Time(timeCardRule.BeforeTimeOfDay) + "";
}
if (timeCardRule.IsOvertimeExempt != oldTimeCardRule.IsOvertimeExempt)
{
if (command != "")
{
command += ",";
}
command += "IsOvertimeExempt = " + POut.Bool(timeCardRule.IsOvertimeExempt) + "";
}
if (command == "")
{
return(false);
}
command = "UPDATE timecardrule SET " + command
+ " WHERE TimeCardRuleNum = " + POut.Long(timeCardRule.TimeCardRuleNum);
Db.NonQ(command);
return(true);
}
///<summary>Provide timeCardeRule to edit. Otherwise will insert new entry for every selected Employee.
///This form is used to edit an existing timeCardRule or insert many new timeCardRules.</summary>
public FormTimeCardRuleEdit(TimeCardRule timeCardRule = null)
{
InitializeComponent();
Lan.F(this);
_timeCardRule = timeCardRule;
if (_timeCardRule == null) //No timeCardRule provided, we must be inserting new entries.
{
_timeCardRule = new TimeCardRule(); //Allows Load(...) to set empty values.
_isInsertMode = true;
listEmp.SelectionMode = SelectionMode.MultiExtended; //When adding new entries allow the user to insert many new rows.
}
}
///<summary>Updates one TimeCardRule in the database.</summary>
public static void Update(TimeCardRule timeCardRule)
{
string command = "UPDATE timecardrule SET "
+ "EmployeeNum = " + POut.Long(timeCardRule.EmployeeNum) + ", "
+ "OverHoursPerDay = " + POut.Time(timeCardRule.OverHoursPerDay) + ", "
+ "AfterTimeOfDay = " + POut.Time(timeCardRule.AfterTimeOfDay) + ", "
+ "BeforeTimeOfDay = " + POut.Time(timeCardRule.BeforeTimeOfDay) + ", "
+ "IsOvertimeExempt= " + POut.Bool(timeCardRule.IsOvertimeExempt) + " "
+ "WHERE TimeCardRuleNum = " + POut.Long(timeCardRule.TimeCardRuleNum);
Db.NonQ(command);
}
private void butAddRule_Click(object sender, EventArgs e)
{
TimeCardRule rule = new TimeCardRule();
rule.IsNew = true;
FormTimeCardRuleEdit FormT = new FormTimeCardRuleEdit();
FormT.timeCardRule = rule;
FormT.ShowDialog();
FillRules();
changed = true;
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List<TimeCardRule> TableToList(DataTable table){
List<TimeCardRule> retVal=new List<TimeCardRule>();
TimeCardRule timeCardRule;
for(int i=0;i<table.Rows.Count;i++) {
timeCardRule=new TimeCardRule();
timeCardRule.TimeCardRuleNum= PIn.Long (table.Rows[i]["TimeCardRuleNum"].ToString());
timeCardRule.EmployeeNum = PIn.Long (table.Rows[i]["EmployeeNum"].ToString());
timeCardRule.OverHoursPerDay= PIn.Time(table.Rows[i]["OverHoursPerDay"].ToString());
timeCardRule.AfterTimeOfDay = PIn.Time(table.Rows[i]["AfterTimeOfDay"].ToString());
timeCardRule.BeforeTimeOfDay= PIn.Time(table.Rows[i]["BeforeTimeOfDay"].ToString());
retVal.Add(timeCardRule);
}
return retVal;
}
///<summary>Inserts one TimeCardRule into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(TimeCardRule timeCardRule)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
return(InsertNoCache(timeCardRule, false));
}
else
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
timeCardRule.TimeCardRuleNum = DbHelper.GetNextOracleKey("timecardrule", "TimeCardRuleNum"); //Cacheless method
}
return(InsertNoCache(timeCardRule, true));
}
}
///<summary>Converts a DataTable to a list of objects.</summary>
internal static List <TimeCardRule> TableToList(DataTable table)
{
List <TimeCardRule> retVal = new List <TimeCardRule>();
TimeCardRule timeCardRule;
for (int i = 0; i < table.Rows.Count; i++)
{
timeCardRule = new TimeCardRule();
timeCardRule.TimeCardRuleNum = PIn.Long(table.Rows[i]["TimeCardRuleNum"].ToString());
timeCardRule.EmployeeNum = PIn.Long(table.Rows[i]["EmployeeNum"].ToString());
timeCardRule.OverHoursPerDay = PIn.Time(table.Rows[i]["OverHoursPerDay"].ToString());
timeCardRule.AfterTimeOfDay = PIn.Time(table.Rows[i]["AfterTimeOfDay"].ToString());
retVal.Add(timeCardRule);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <TimeCardRule> TableToList(DataTable table)
{
List <TimeCardRule> retVal = new List <TimeCardRule>();
TimeCardRule timeCardRule;
foreach (DataRow row in table.Rows)
{
timeCardRule = new TimeCardRule();
timeCardRule.TimeCardRuleNum = PIn.Long(row["TimeCardRuleNum"].ToString());
timeCardRule.EmployeeNum = PIn.Long(row["EmployeeNum"].ToString());
timeCardRule.OverHoursPerDay = PIn.Time(row["OverHoursPerDay"].ToString());
timeCardRule.AfterTimeOfDay = PIn.Time(row["AfterTimeOfDay"].ToString());
timeCardRule.BeforeTimeOfDay = PIn.Time(row["BeforeTimeOfDay"].ToString());
timeCardRule.IsOvertimeExempt = PIn.Bool(row["IsOvertimeExempt"].ToString());
retVal.Add(timeCardRule);
}
return(retVal);
}
///<summary>Updates one TimeCardRule in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables.</summary>
internal static void Update(TimeCardRule timeCardRule, TimeCardRule oldTimeCardRule)
{
string command = "";
if (timeCardRule.EmployeeNum != oldTimeCardRule.EmployeeNum)
{
if (command != "")
{
command += ",";
}
command += "EmployeeNum = " + POut.Long(timeCardRule.EmployeeNum) + "";
}
if (timeCardRule.OverHoursPerDay != oldTimeCardRule.OverHoursPerDay)
{
if (command != "")
{
command += ",";
}
command += "OverHoursPerDay = " + POut.Time(timeCardRule.OverHoursPerDay) + "";
}
if (timeCardRule.AfterTimeOfDay != oldTimeCardRule.AfterTimeOfDay)
{
if (command != "")
{
command += ",";
}
command += "AfterTimeOfDay = " + POut.Time(timeCardRule.AfterTimeOfDay) + "";
}
if (command == "")
{
return;
}
command = "UPDATE timecardrule SET " + command
+ " WHERE TimeCardRuleNum = " + POut.Long(timeCardRule.TimeCardRuleNum);
Db.NonQ(command);
}
///<summary>Updates one TimeCardRule in the database.</summary>
public static void Update(TimeCardRule timeCardRule){
string command="UPDATE timecardrule SET "
+"EmployeeNum = "+POut.Long (timeCardRule.EmployeeNum)+", "
+"OverHoursPerDay= "+POut.Time (timeCardRule.OverHoursPerDay)+", "
+"AfterTimeOfDay = "+POut.Time (timeCardRule.AfterTimeOfDay)+", "
+"BeforeTimeOfDay= "+POut.Time (timeCardRule.BeforeTimeOfDay)+" "
+"WHERE TimeCardRuleNum = "+POut.Long(timeCardRule.TimeCardRuleNum);
Db.NonQ(command);
}
///<summary>Updates one TimeCardRule in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables. Returns true if an update occurred.</summary>
public static bool Update(TimeCardRule timeCardRule,TimeCardRule oldTimeCardRule){
string command="";
if(timeCardRule.EmployeeNum != oldTimeCardRule.EmployeeNum) {
if(command!=""){ command+=",";}
command+="EmployeeNum = "+POut.Long(timeCardRule.EmployeeNum)+"";
}
if(timeCardRule.OverHoursPerDay != oldTimeCardRule.OverHoursPerDay) {
if(command!=""){ command+=",";}
command+="OverHoursPerDay = "+POut.Time (timeCardRule.OverHoursPerDay)+"";
}
if(timeCardRule.AfterTimeOfDay != oldTimeCardRule.AfterTimeOfDay) {
if(command!=""){ command+=",";}
command+="AfterTimeOfDay = "+POut.Time (timeCardRule.AfterTimeOfDay)+"";
}
if(timeCardRule.BeforeTimeOfDay != oldTimeCardRule.BeforeTimeOfDay) {
if(command!=""){ command+=",";}
command+="BeforeTimeOfDay = "+POut.Time (timeCardRule.BeforeTimeOfDay)+"";
}
if(command==""){
return false;
}
command="UPDATE timecardrule SET "+command
+" WHERE TimeCardRuleNum = "+POut.Long(timeCardRule.TimeCardRuleNum);
Db.NonQ(command);
return true;
}
///<summary>Inserts one TimeCardRule into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(TimeCardRule timeCardRule)
{
return(InsertNoCache(timeCardRule, false));
}
///<summary>Inserts many TimeCardRules into the database. Provides option to use the existing priKey.</summary>
public static void InsertMany(List <TimeCardRule> listTimeCardRules, bool useExistingPK)
{
if (!useExistingPK && PrefC.RandomKeys)
{
foreach (TimeCardRule timeCardRule in listTimeCardRules)
{
Insert(timeCardRule);
}
}
else
{
StringBuilder sbCommands = null;
int index = 0;
int countRows = 0;
while (index < listTimeCardRules.Count)
{
TimeCardRule timeCardRule = listTimeCardRules[index];
StringBuilder sbRow = new StringBuilder("(");
bool hasComma = false;
if (sbCommands == null)
{
sbCommands = new StringBuilder();
sbCommands.Append("INSERT INTO timecardrule (");
if (useExistingPK)
{
sbCommands.Append("TimeCardRuleNum,");
}
sbCommands.Append("EmployeeNum,OverHoursPerDay,AfterTimeOfDay,BeforeTimeOfDay,IsOvertimeExempt,MinClockInTime) VALUES ");
countRows = 0;
}
else
{
hasComma = true;
}
if (useExistingPK)
{
sbRow.Append(POut.Long(timeCardRule.TimeCardRuleNum)); sbRow.Append(",");
}
sbRow.Append(POut.Long(timeCardRule.EmployeeNum)); sbRow.Append(",");
sbRow.Append(POut.Time(timeCardRule.OverHoursPerDay)); sbRow.Append(",");
sbRow.Append(POut.Time(timeCardRule.AfterTimeOfDay)); sbRow.Append(",");
sbRow.Append(POut.Time(timeCardRule.BeforeTimeOfDay)); sbRow.Append(",");
sbRow.Append(POut.Bool(timeCardRule.IsOvertimeExempt)); sbRow.Append(",");
sbRow.Append(POut.Time(timeCardRule.MinClockInTime)); sbRow.Append(")");
if (sbCommands.Length + sbRow.Length + 1 > TableBase.MaxAllowedPacketCount && countRows > 0)
{
Db.NonQ(sbCommands.ToString());
sbCommands = null;
}
else
{
if (hasComma)
{
sbCommands.Append(",");
}
sbCommands.Append(sbRow.ToString());
countRows++;
if (index == listTimeCardRules.Count - 1)
{
Db.NonQ(sbCommands.ToString());
}
index++;
}
}
}
}