public static int AddBiller(BillerItem itm)
{
bool returnValue = false;
int billerID = 0;
if (itm != null)
{
billerID = getNextBillerID();
IBaseQueryData query = new InsertQueryData();
query.TableName = "Biller";
query.Fields.Add(new FieldData { FieldName = "BillerID", FieldValue = billerID.ToString(), FieldType = SqlDbType.BigInt });
query.Fields.Add(new FieldData { FieldName = "BillerName", FieldValue = itm.Name, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "AddressLine1", FieldValue = itm.AddressLine1, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "AddressLine2", FieldValue = itm.AddressLine2, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "City", FieldValue = itm.City, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "Zip", FieldValue = itm.Zip, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "State", FieldValue = itm.State, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "Country", FieldValue = itm.Country, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "Notes", FieldValue = itm.Notes, FieldType = SqlDbType.VarChar });
returnValue = SQLWrapper.ExecuteQuery(query);
}
if (returnValue)
{
return billerID;
}
else
{
return 0;
}
}
public static long AddSchedule(ISchedule itm)
{
long itmID = getNextScheduleID();
IBaseQueryData query = new InsertQueryData();
query.TableName = "Schedules";
query.Fields.Add(new FieldData { FieldName = "ScheduleID", FieldValue = itmID.ToString(), FieldType = SqlDbType.BigInt });
if (itm.GetType() == typeof(YearlySchedule))
{
YearlySchedule ySched = (YearlySchedule)itm;
query.Fields.Add(new FieldData { FieldName = "RecurType", FieldValue = "4", FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "StartDate", FieldValue = ySched.StartDate.ToString(Constants.DATE_FORMAT_SQL) , FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "EndDate", FieldValue = ySched.EndDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "RecurUnits", FieldValue = ySched.RecurEveryNoOfYears.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnDay", FieldValue = ySched.OnDay.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnMonth", FieldValue = ySched.OnMonth.ToString(), FieldType = SqlDbType.Int });
}
else if (itm.GetType() == typeof(MonthlySchedule))
{
MonthlySchedule mSched = (MonthlySchedule)itm;
query.Fields.Add(new FieldData { FieldName = "RecurType", FieldValue = "3", FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "RecurUnits", FieldValue = mSched.RecurEveryNoOfMonths.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "StartDate", FieldValue = mSched.StartDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "EndDate", FieldValue = mSched.EndDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "OnDay", FieldValue = mSched.OnDay.ToString(), FieldType = SqlDbType.Int });
}
else if (itm.GetType() == typeof(WeeklySchedule))
{
WeeklySchedule wSched = (WeeklySchedule)itm;
query.Fields.Add(new FieldData { FieldName = "RecurType", FieldValue = "2", FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "RecurUnits", FieldValue = wSched.RecurEveryNoOfWeeks.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "StartDate", FieldValue = wSched.StartDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "EndDate", FieldValue = wSched.EndDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "OnMondays", FieldValue = wSched.OnMondays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnTuesdays", FieldValue = wSched.OnTuesdays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnWednesdays", FieldValue = wSched.OnWednesdays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnThursdays", FieldValue = wSched.OnThursdays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnFridays", FieldValue = wSched.OnFridays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnSaturdays", FieldValue = wSched.OnSaturdays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "OnSundays", FieldValue = wSched.OnSundays.ToString(), FieldType = SqlDbType.Int });
}
else
{
DailySchedule dSched = (DailySchedule)itm;
query.Fields.Add(new FieldData { FieldName = "RecurType", FieldValue = "1", FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "RecurUnits", FieldValue = dSched.RecurEveryNoOfDays.ToString(), FieldType = SqlDbType.Int });
query.Fields.Add(new FieldData { FieldName = "StartDate", FieldValue = dSched.StartDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "EndDate", FieldValue = dSched.EndDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "WeekDaysOnly", FieldValue = dSched.WeekDaysOnly.ToString(), FieldType = SqlDbType.Bit });
}
if (!SQLWrapper.ExecuteQuery(query))
{
itmID = 0;
}
return itmID;
}
public static long AddBill(BillItem itm)
{
bool returnValue = false;
long billID = 0;
if (itm != null)
{
billID = getNextBillID();
long scheduleID = 0;
List<DateTime> schedules = null;
if (itm.BillSchedule != null)
{
scheduleID = DALSchedule.AddSchedule(itm.BillSchedule);
schedules = ScheduleHelper.GetInstanceDates(itm.BillSchedule, itm.StartDate, itm.EndDate);
}
else
{
schedules = new List<DateTime>();
schedules.Add(itm.StartDate);
}
IBaseQueryData query = new InsertQueryData();
query.TableName = "Bill";
query.Fields.Add(new FieldData { FieldName = "BillID", FieldValue = billID.ToString(), FieldType = SqlDbType.BigInt });
if (itm.Biller != null)
{
query.Fields.Add(new FieldData { FieldName = "BillerID", FieldValue = itm.Biller.ID.ToString(), FieldType = SqlDbType.Int });
}
query.Fields.Add(new FieldData { FieldName = "StartDate", FieldValue = itm.StartDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "FinishDate", FieldValue = itm.EndDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.DateTime });
query.Fields.Add(new FieldData { FieldName = "BillAmount", FieldValue = itm.Amount.ToString(Constants.CURRENCY_FORMAT_SQL), FieldType = SqlDbType.Money });
query.Fields.Add(new FieldData { FieldName = "Notes", FieldValue = itm.Notes, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "BillTitle", FieldValue = itm.BillTitle, FieldType = SqlDbType.VarChar });
query.Fields.Add(new FieldData { FieldName = "ScheduleID", FieldValue = scheduleID.ToString(), FieldType = SqlDbType.BigInt });
List<BillInstanceItem> itms = new List<BillInstanceItem>();
for (int j = 0; j <= schedules.Count - 1; j++)
{
itms.Add(new BillInstanceItem { BillID = billID, InstanceDate = schedules[j] });
}
List<IBaseQueryData> queryData = DALBillInstance.GetUpsertBillInstanceQueryData(billID, itms);
queryData.Insert(0, query);
returnValue = SQLWrapper.ExecuteQuery(queryData);
}
if (returnValue)
{
return billID;
}
else
{
return 0;
}
}
public static List<IBaseQueryData> GetUpsertBillInstanceQueryData(long billID, List<BillInstanceItem> itms)
{
long billInstanceID = getNextBillInstanceID();
List<BillInstanceItem> oldInstances = GetBillInstancesByID(billID);
List<IBaseQueryData> queryData = new List<IBaseQueryData>();
IBaseQueryData query = new DeleteQueryData();
query.TableName = "BillInstance";
query.KeyFields.Add(new FieldData { FieldName = "BillID", FieldValue = billID.ToString(), FieldType = SqlDbType.BigInt });
queryData.Add(query);
for (int i = 0; i <= itms.Count - 1; i++)
{
query = new InsertQueryData();
query.TableName = "BillInstance";
query.Fields.Add(new FieldData { FieldName = "BillInstanceID", FieldValue = billInstanceID.ToString(), FieldType = SqlDbType.BigInt });
query.Fields.Add(new FieldData { FieldName = "BillID", FieldValue = billID.ToString(), FieldType = SqlDbType.BigInt });
query.Fields.Add(new FieldData { FieldName = "InstanceDate", FieldValue = itms[i].InstanceDate.ToString(Constants.DATE_FORMAT_SQL), FieldType = SqlDbType.Date });
bool isCompleted = false;
if (itms != null)
{
if (itms[i] != null)
{
isCompleted = itms[i].IsCompleted;
}
}
BillInstanceItem tmpItem = oldInstances.FindAll(x => x.BillID == billID && x.InstanceDate == itms[i].InstanceDate).FirstOrDefault();
if (tmpItem != null)
{
isCompleted = tmpItem.IsCompleted;
}
query.Fields.Add(new FieldData { FieldName = "InstanceCompleted", FieldValue = isCompleted.ToString(), FieldType = SqlDbType.Bit });
queryData.Add(query);
billInstanceID++;
}
return queryData;
}