public static bool IsAccountFrozen(OperationRecord model)
        {
            if (!string.IsNullOrEmpty(model.FinancialBankAccountID))
            {
                string SQLString = "SELECT * FROM tblAccount where FinancialAccountID = " + model.FinancialBankAccountID;
                using (SqlDataAdapter adapter = new SqlDataAdapter(SQLString, Helpers.Helpers.GetAppConnectionString()))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);

                    DataTable tb = (DataTable)ds.Tables[0];
                    if (tb != null && tb.Rows.Count > 0)
                    {
                        DataRow dr = tb.Rows[0];
                        if (dr["FrozenDateTime"] != DBNull.Value)
                        {
                            DateTime frozenDate = DateTime.Parse(dr["FrozenDateTime"].ToString());
                            if (frozenDate > model.CompleteDate)
                            {
                                return(true);
                            }
                        }
                    }
                }
            }
            return(false);
        }
        public static int CreateOperationRecord(OperationRecord model)
        {
            int expenseID = 0;

            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                // Create the Command and Parameter objects.
                SqlCommand cmd = new SqlCommand("", connection);
                connection.Open();
                //only unfrozen bank record can be changed for the finish date, financialaccount and amount
                if (!IsAccountFrozen(model))
                {
                    int i = (model.IsCredit == true) ? 1 : 0;
                    if (!model.IsCredit)
                    {
                        model.Payment = -model.Payment;
                    }
                    if (string.IsNullOrEmpty(model.LinkedRentID))
                    {
                        model.LinkedRentID = "0";
                    }
                    //create record that the payment recevied by the owner who paid the bill on behalf of the real owner
                    cmd.CommandText  = "insert into tblUnitOperation(UploadedBy, UploadDate,StatusID, CategoryID, IsCredit, ContractorID, UnitID, DueDate, DueAmount, Notes, BankTracking,FinancialAccountID, Amount, FinishDate, InvoiceLink, LinkedRentID) values (";
                    cmd.CommandText += model.UploadBy + ", '" + DateTime.Now + "'," + model.StatusID + ", " + model.CategoryID + ", " + i + "," + model.ContractorID + ", " + model.UnitID + ",'" + model.DueDate + "', " + model.DueAmount + ", '" + model.Memo + "', '" + model.BankTracking;
                    cmd.CommandText += "'," + model.FinancialBankAccountID + ", " + model.Payment + ", '" + model.CompleteDate + "','" + model.InvoiceLink + "' ," + model.LinkedRentID + "); SELECT SCOPE_IDENTITY();";
                    expenseID        = int.Parse(cmd.ExecuteScalar().ToString());
                }
                connection.Close();
            }
            return(expenseID);
        }
        public static void TransferFund(OperationRecord model)
        {
            int LinkedReimburseExpenseID = CreateOperationRecord(model);

            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                try
                {
                    // Create the Command and Parameter objects.
                    SqlCommand cmd = new SqlCommand("", connection);
                    connection.Open();

                    if (LinkedReimburseExpenseID > 0)
                    {
                        //update the reimbursed expense link id
                        cmd.CommandText = "UPDATE tblUnitOperation set LinkedExpenseID=" + LinkedReimburseExpenseID + " where id=" + model.ID;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        public static void EditRent(OperationRecord model)
        {
            //setup deposit type
            if (model.IsSecurityDeposit)
            {
                model.CategoryID = (int)Helpers.Helpers.EmailType.SecurityDeposit;
            }
            else
            {
                model.CategoryID = (int)Helpers.Helpers.EmailType.Rent;
            }
            if (string.IsNullOrEmpty(model.FinancialBankAccountID))
            {
                model.FinancialBankAccountID = "0";
            }
            if (model.LinkedRentID != null)
            {
                UpdateOperationRecord(model);
            }

            if (model.IsEmailReceipt)
            {
                Email.EmailPayment(model.ID, model.ContractorID, model.UnitID, model.CompleteDate, model.FinancialBankAccountID, model.DueAmount, model.Payment, model.CategoryID);
            }

            int tenantid = GetTenantID(model.ContractorID);

            UpdateRent(model, tenantid);
        }
        public static int CreateRent(OperationRecord model, int tenantID)
        {
            int rentID = 0;

            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                // Create the Command and Parameter objects.
                SqlCommand cmd = new SqlCommand("", connection);
                connection.Open();
                //only unfrozen bank record can be changed for the finish date, financialaccount and amount
                if (!IsAccountFrozen(model))
                {
                    int i = (model.IsCredit == true) ? 1 : 0;
                    if (!model.IsCredit)
                    {
                        model.Payment = -model.Payment;
                    }
                    model.IsSecurityDeposit = false;
                    if (model.CategoryID == (int)Helpers.Helpers.ExpenseCategory.SecurityDeposit)
                    {
                        model.IsSecurityDeposit = true;
                    }
                    int r = (model.IsSecurityDeposit == false) ? 1 : 0;
                    //create record that the payment recevied by the owner who paid the bill on behalf of the real owner
                    cmd.CommandText = "INSERT INTO  tblRent (TenantID, RentAmount, PaidAmount, DueDate, IsRent, Note, StatusID, FinancialAccountID, PaymentDate ) VALUES ( " + tenantID + "," + model.DueAmount + "," + model.Payment + ",'" + model.DueDate.ToShortDateString() + "'," + r + ",'" + model.Memo + "'," + model.StatusID + "," + model.FinancialBankAccountID + ",'" + model.CompleteDate + "'); SELECT SCOPE_IDENTITY();";
                    rentID          = int.Parse(cmd.ExecuteScalar().ToString());
                }
                connection.Close();
            }
            return(rentID);
        }
Exemple #6
0
        public static void Add(OperationRecord model)
        {
            OperationRecordManager.CreateOperationRecord(model);
            //using (SqlConnection connection = new SqlConnection(appString))
            //{
            //    try
            //    {
            //        // Create the Command and Parameter objects.
            //        SqlCommand cmd = new SqlCommand("", connection);
            //        connection.Open();
            //        if (!model.IsCredit)
            //        {
            //            model.Payment = -model.Payment;
            //        }
            //        int i = (model.IsCredit == true) ? 1 : 0;
            //        cmd.CommandText = "insert into tblUnitOperation(StatusID, CategoryID, IsCredit, ContractorID, UnitID, DueDate, DueAmount, Notes,FinancialAccountID, Amount, FinishDate, InvoiceLink) values (";
            //        cmd.CommandText += model.StatusID + ", " + model.CategoryID + ", " + i + "," + model.ContractorID + ", " + model.UnitID + ",'" + model.DueDate + "', " + model.DueAmount + ", '" + model.Memo;
            //        cmd.CommandText += "'," + model.FinancialBankAccountID + ", " + model.Payment + ", '" + model.CompleteDate + "'," + model.InvoiceLink + "' )";
            //        cmd.ExecuteNonQuery();
            //    }
            //    catch (Exception ex)
            //    {
            //        throw new Exception(ex.Message);
            //    }
            //    finally
            //    {
            //        connection.Close();
            //    }

            //}
        }
Exemple #7
0
        public static void Edit(OperationRecord model)
        {
            using (SqlConnection connection = new SqlConnection(appString))
            {
                try
                {
                    // Create the Command and Parameter objects.
                    SqlCommand cmd = new SqlCommand("", connection);
                    connection.Open();
                    if (!model.IsCredit)
                    {
                        model.Payment = -model.Payment;
                    }
                    int i = (model.IsCredit == true) ? 1 : 0;
                    cmd.CommandText  = "Update tblUnitOperation set StatusID=" + model.StatusID + ", CategoryID = " + model.CategoryID + " , IsCredit=" + Convert.ToInt32(model.IsCredit) + ", ContractorID=";
                    cmd.CommandText += model.ContractorID + ", UnitID=" + model.UnitID + ", DueDate='" + model.DueDate.ToShortDateString() + "', DueAmount=" + model.DueAmount + ", Notes='" + model.Memo;
                    cmd.CommandText += "' " + ", FinishDate='" + model.CompleteDate + "', FinancialAccountID= " + model.FinancialBankAccountID + ", Amount=" + model.Payment + ", InvoiceLink='" + model.InvoiceLink + "'";
                    cmd.CommandText += " where ID=" + model.ID;
                    cmd.ExecuteNonQuery();


                    connection.Close();
                }
                catch (Exception ex)
                {
                    string exms = ex.Message;
                }
            }
        }
        public static void AddAndTransferFund(OperationRecord model)
        {
            string TransferedFinancialBankAccountID = model.TransferedFinancialBankAccountID;
            string financialBankAccount             = model.FinancialBankAccountID;

            //create record the actually payment paid by the real owner
            int expenseID = CreateOperationRecord(model);

            //create record that the payment recevied by the owner who paid the bill on behalf of the real owner
            model.IsCredit = model.IsCredit ? false:true;
            int LinkedReimburseExpenseID = CreateOperationRecord(model);

            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                try
                {
                    // Create the Command and Parameter objects.
                    SqlCommand cmd = new SqlCommand("", connection);
                    connection.Open();

                    //update the reimbursed expense link id
                    cmd.CommandText = "UPDATE tblUnitOperation set LinkedReimburseExpenseID=" + LinkedReimburseExpenseID + " where id=" + model.ID;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        public static void Edit(OperationRecord model)
        {
            UpdateOperationRecord(model);

            if (model.IsEmailReceipt)
            {
                Email.EmailPayment(model.ID, model.ContractorID, model.UnitID, model.CompleteDate, model.FinancialBankAccountID, model.DueAmount, model.Payment, model.CategoryID);
            }
        }
 public static void ReceiveRent(OperationRecord model)
 {
     if (model.LinkedRentID == null)
     {
         //create linked operation
         CreateOperationRecord(model);
     }
     UpdateOperationRecord(model);
     UpdateRent(model, GetTenantID(model.ContractorID));
     if (model.IsEmailReceipt)
     {
         Email.EmailPayment(model.ID, model.ContractorID, model.UnitID, model.CompleteDate, model.FinancialBankAccountID, model.DueAmount, model.Payment, model.CategoryID);
     }
 }
 public static void DeleteOperationRecord(OperationRecord model)
 {
     using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
     {
         // Create the Command and Parameter objects.
         SqlCommand cmd = new SqlCommand("", connection);
         connection.Open();
         //only unfrozen bank record can be changed for the finish date, financialaccount and amount
         if (!IsAccountFrozen(model))
         {
             cmd.CommandText = "DELETE FROM tblRent where RENTID= (select LinkedRentID from tblUnitOperation where ID=" + model.ID + ")";
             cmd.ExecuteNonQuery();
             cmd.CommandText = "DELETE FROM tblUnitOperation where ID=" + model.ID;
             cmd.ExecuteNonQuery();
         }
         connection.Close();
     }
 }
        public static void Reimburse(OperationRecord model)
        {
            string TransferedFinancialBankAccountID = model.TransferedFinancialBankAccountID;
            string financialBankAccount             = model.FinancialBankAccountID;
            double originalDueAmount     = model.DueAmount;
            double originalPaymentAmoutn = model.Payment;

            //create record the actually payment paid by the real owner
            //it will be the exact process as the guy who pay/receive the bill
            model.FinancialBankAccountID = TransferedFinancialBankAccountID;
            int linkedExpenseID = CreateOperationRecord(model);

            //create record that the payment recevied by the owner who paid the bill on behalf of the real owner
            model.IsCredit  = model.IsCredit ? false : true;
            model.DueAmount = -originalDueAmount;
            model.Payment   = originalPaymentAmoutn;
            model.FinancialBankAccountID = financialBankAccount;
            int LinkedReimburseExpenseID = CreateOperationRecord(model);

            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                try
                {
                    // Create the Command and Parameter objects.
                    SqlCommand cmd = new SqlCommand("", connection);
                    connection.Open();

                    //update the reimbursed expense link id
                    cmd.CommandText = "UPDATE tblUnitOperation set linkedExpenseID=" + linkedExpenseID + ", LinkedReimburseExpenseID=" + LinkedReimburseExpenseID + ", StatusID=" + model.StatusID + " where id=" + model.ID;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        public static void UpdateOperationRecord(OperationRecord model)
        {
            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                // Create the Command and Parameter objects.
                SqlCommand cmd = new SqlCommand("", connection);
                connection.Open();
                if (!model.IsCredit)
                {
                    model.Payment = -model.Payment;
                }
                int i = (model.IsCredit == true) ? 1 : 0;
                cmd.CommandText  = "Update tblUnitOperation set CategoryID = " + model.CategoryID + " , IsCredit=" + Convert.ToInt32(model.IsCredit) + ", ContractorID=";
                cmd.CommandText += model.ContractorID + ", UnitID=" + model.UnitID + ", DueDate='" + model.DueDate.ToShortDateString() + "', DueAmount=" + model.DueAmount + ", Notes='" + model.Memo;
                cmd.CommandText += "' , InvoiceLink='" + model.InvoiceLink + "'" + ", BankTracking='" + model.BankTracking + "'";
                //only unfrozen bank record can be changed for the finish date, financialaccount and amount
                if (!IsAccountFrozen(model))
                {
                    cmd.CommandText += ", StatusID=" + model.StatusID + ", FinishDate='" + model.CompleteDate + "', FinancialAccountID= " + model.FinancialBankAccountID + ", Amount=" + model.Payment;
                }
                cmd.CommandText += " where ID=" + model.ID;
                cmd.ExecuteNonQuery();

                //update rent record
                if (!string.IsNullOrEmpty(model.LinkedRentID) && !model.LinkedRentID.Equals("0"))
                {
                    //update tenant id also if needed
                    cmd.CommandText = "select  tblTenant.TenantID from tblTenant WHERE UserID=  " + model.ContractorID + " Order by TenantID desc";
                    int tenantID = Int32.Parse(cmd.ExecuteScalar().ToString());

                    cmd.CommandText  = "Update tblRent set StatusID= " + model.StatusID + ", PaymentDate ='" + model.CompleteDate + "', FinancialAccountID= ";
                    cmd.CommandText += model.FinancialBankAccountID + ", PaidAmount=" + model.Payment + ", TenantID=" + tenantID + ", DueDate='" + model.DueDate;
                    cmd.CommandText += "', RentAmount=" + model.DueAmount + ", CategoryID=" + model.CategoryID + ", BankTracking='" + model.BankTracking + "'" + ", Note='" + model.Memo + "'";
                    cmd.CommandText += " where RentID=" + model.LinkedRentID;
                    cmd.ExecuteNonQuery();
                }

                connection.Close();
            }
        }
 public static void UpdateRent(OperationRecord model, int tenantID)
 {
     using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
     {
         // Create the Command and Parameter objects.
         SqlCommand cmd = new SqlCommand("", connection);
         connection.Open();
         if (!model.IsCredit)
         {
             model.Payment = -model.Payment;
         }
         int i = (model.IsCredit == true) ? 1 : 0;
         cmd.CommandText  = "Update tblRent set TenantID=" + tenantID + ", DueDate='" + model.DueDate;
         cmd.CommandText += "', RentAmount=" + model.DueAmount + ", CategoryID=" + model.CategoryID + ", BankTracking='" + model.BankTracking + "'" + ", Note='" + model.Memo + "'";
         //only unfrozen bank record can be changed for the finish date, financialaccount and amount
         if (!IsAccountFrozen(model))
         {
             cmd.CommandText += ", StatusID=" + model.StatusID + ", PaymentDate='" + model.CompleteDate + "', FinancialAccountID= " + model.FinancialBankAccountID + ", PaidAmount=" + model.Payment;
         }
         cmd.CommandText += " where RentID=" + model.LinkedRentID;
         cmd.ExecuteNonQuery();
         connection.Close();
     }
 }
        public static OperationRecord GetExpenseByID(int id)
        {
            string SQLString = "select * from tblUnitOperation where ID =  " + id;

            using (SqlDataAdapter adapter = new SqlDataAdapter(SQLString, Helpers.Helpers.GetAppConnectionString()))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                DataTable       tb   = (DataTable)ds.Tables[0];
                OperationRecord role = new OperationRecord();
                if (tb != null && tb.Rows.Count > 0)
                {
                    DataRow dr = tb.Rows[0];
                    if (dr["ID"] != DBNull.Value)
                    {
                        role.ID = Int32.Parse(dr["ID"].ToString());
                    }
                    if (dr["IsCredit"] != DBNull.Value)
                    {
                        role.IsCredit = Boolean.Parse(dr["IsCredit"].ToString());
                    }

                    if (dr["StatusID"] != DBNull.Value)
                    {
                        role.StatusID = short.Parse(dr["StatusID"].ToString());
                    }

                    if (dr["CategoryID"] != DBNull.Value)
                    {
                        role.CategoryID = short.Parse(dr["CategoryID"].ToString());
                    }

                    if (dr["ContractorID"] != DBNull.Value)
                    {
                        role.ContractorID = (int)dr["ContractorID"];
                    }
                    if (dr["Amount"] != DBNull.Value)
                    {
                        if (role.IsCredit)
                        {
                            role.Payment = double.Parse(dr["Amount"].ToString());
                        }
                        else
                        {
                            role.Payment = -double.Parse(dr["Amount"].ToString());
                        }
                    }
                    if (dr["DueDate"] != DBNull.Value)
                    {
                        role.DueDate = DateTime.Parse(dr["DueDate"].ToString());
                    }
                    if (dr["FinishDate"] != DBNull.Value)
                    {
                        role.CompleteDate = DateTime.Parse(dr["FinishDate"].ToString());
                    }
                    if (dr["UnitID"] != DBNull.Value)
                    {
                        role.UnitID = (int)dr["UnitID"];
                    }
                    if (dr["LinkedRentID"] != DBNull.Value)
                    {
                        role.LinkedRentID = dr["LinkedRentID"].ToString();
                    }
                    role.Memo                   = dr["Notes"].ToString();
                    role.BankTracking           = dr["BankTracking"].ToString();
                    role.FinancialBankAccountID = dr["FinancialAccountID"].ToString();
                    if (dr["DueAmount"] != DBNull.Value)
                    {
                        role.DueAmount = double.Parse(dr["DueAmount"].ToString());
                    }
                    if (dr["InvoiceLink"] != DBNull.Value)
                    {
                        role.InvoiceLink = dr["InvoiceLink"].ToString();
                    }
                }
                return(role);
            }
        }
 public static void Add(OperationRecord model)
 {
     CreateOperationRecord(model);
 }
        public static List <OperationRecord> GetExpense(string startDate, string endDate, string[] companyIDs, string[] propertyIDs, string[] unitIDs, string[] bankAccountIDs, string[] statusIDs, string[] contractorIDs, string[] categoryIDs, string expense, int loggedinUser)
        {
            DateTime      start       = DateTime.Parse(startDate);
            DateTime      end         = DateTime.Parse(endDate);
            StringBuilder sbOperation = new StringBuilder();

            sbOperation.Append("Select distinct ID, [tblUnitOperation].DueDate, FinishDate, PaidBy.FirstName +' ' + PaidBy.LastName as PaidBy, ");
            sbOperation.Append("tblUnitOperation.Notes,[tblUnitOperation].LinkedExpenseID, Amount, tblAccount.AccountName, tblProperty.Address, tblPropertyUnit.UnitName, IsCredit, ");
            sbOperation.Append(" DueAmount, [tblUnitOperation].StatusID, cStatusType.Name  as StatusName, cExpenseCategory.CategoryName,tblUnitOperation.BankTracking  from tblUnitOperation ");
            sbOperation.Append(" inner join  tblPropertyUnit on tblPropertyUnit.UnitID =  tblUnitOperation.UnitID ");
            sbOperation.Append(" INNER JOIN  tblProperty ON tblProperty.PropertyID = tblPropertyUnit.PropertyID ");
            sbOperation.Append(" INNER JOIN mCompanyProperty on mCompanyProperty.PropertyID = tblProperty.PropertyID ");
            sbOperation.Append(" LEFT OUTER JOIN cUser as PaidBy on PaidBy.UserID = tblUnitOperation.ContractorID ");
            sbOperation.Append(" LEFT OUTER JOIN cStatusType on cStatusType.StatusTypeID = tblUnitOperation.StatusID ");
            sbOperation.Append(" LEFT OUTER JOIN cExpenseCategory on cExpenseCategory.CategoryID = tblUnitOperation.CategoryID ");
            sbOperation.Append(" LEFT OUTER JOIN tblAccount as tblAccount on tblAccount.FinancialAccountID = tblUnitOperation.FinancialAccountID ");

            StringBuilder whereClause = new StringBuilder();

            if (!String.IsNullOrEmpty(startDate))
            {
                start = DateTime.Parse(startDate);
                whereClause.Append(" and [tblUnitOperation].FinishDate>='" + start.ToShortDateString() + "' ");
            }
            if (!String.IsNullOrEmpty(endDate))
            {
                end = DateTime.Parse(endDate);
                whereClause.Append(" and [tblUnitOperation].FinishDate<='" + end.ToShortDateString() + "'");
            }
            // Add modality id to the where clause if appropriate
            if (bankAccountIDs != null && bankAccountIDs.Count() > 0 && !string.IsNullOrEmpty(bankAccountIDs[0]))
            {
                whereClause.Append(" AND tblUnitOperation.FinancialAccountID IN (" + String.Join(",", bankAccountIDs) + ")");
            }
            // Add modality id to the where clause if appropriate
            if (companyIDs != null && companyIDs.Count() > 0 && !string.IsNullOrEmpty(companyIDs[0]))
            {
                whereClause.Append(" AND mCompanyProperty.CompanyID IN (" + String.Join(",", companyIDs) + ")");
            }
            else
            {
                //get the companys only the owner can access
                whereClause.Append(" AND mCompanyProperty.CompanyID IN (" + Helpers.Helpers.GetUserManagedCompanyString(loggedinUser.ToString()) + ")");
            }
            // Add modality id to the where clause if appropriate
            if (propertyIDs != null && propertyIDs.Count() > 0 && !string.IsNullOrEmpty(propertyIDs[0]))
            {
                whereClause.Append(" AND tblProperty.PropertyID IN (" + String.Join(",", propertyIDs) + ")");
            }
            // Add modality id to the where clause if appropriate
            if (unitIDs != null && unitIDs.Count() > 0 && !string.IsNullOrEmpty(unitIDs[0]))
            {
                whereClause.Append(" AND tblPropertyUnit.UnitID IN (" + String.Join(",", unitIDs) + ")");
            }
            if (statusIDs != null && statusIDs.Count() > 0 && !string.IsNullOrEmpty(statusIDs[0]))
            {
                whereClause.Append(" AND [tblUnitOperation].StatusID IN (" + String.Join(",", statusIDs) + ")");
            }
            if (contractorIDs != null && contractorIDs.Count() > 0 && !string.IsNullOrEmpty(contractorIDs[0]))
            {
                whereClause.Append(" AND [tblUnitOperation].ContractorID IN (" + String.Join(",", contractorIDs) + ")");
            }
            if (categoryIDs != null && categoryIDs.Count() > 0 && !string.IsNullOrEmpty(categoryIDs[0]))
            {
                whereClause.Append(" AND [tblUnitOperation].CategoryID IN (" + String.Join(",", categoryIDs) + ")");
            }
            if (!string.IsNullOrEmpty(expense))
            {
                whereClause.Append(" AND ([tblUnitOperation].Amount IN (" + expense + ", -" + expense + ") OR [tblUnitOperation].DueAmount IN (" + expense + ", -" + expense + "))");
            }

            sbOperation.Append(whereClause.Remove(0, 4).Insert(0, " where "));

            sbOperation.Append(" Order by DueDate");

            // Create a list of our result class to hold the data from the query
            // Please ensure you instatiate the class for this controller and not a different controller
            List <OperationRecord> result = new List <OperationRecord>();

            // Execute the SQL query and get the results

            using (SqlDataAdapter adapter = new SqlDataAdapter(sbOperation.ToString(), Helpers.Helpers.GetAppConnectionString()))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                DataTable tb = (DataTable)ds.Tables[0];
                if (tb != null && tb.Rows.Count > 0)
                {
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        DataRow         dr  = tb.Rows[i];
                        OperationRecord row = new OperationRecord();
                        if (dr["DueDate"] != DBNull.Value)
                        {
                            row.DueDate = DateTime.Parse(dr["DueDate"].ToString());
                        }
                        if (dr["FinishDate"] != DBNull.Value)
                        {
                            row.CompleteDate = DateTime.Parse(dr["FinishDate"].ToString());
                        }
                        row.PaidBy = dr["PaidBy"].ToString();
                        if (dr["BankTracking"] != DBNull.Value)
                        {
                            row.Memo = dr["Notes"].ToString() + " " + dr["BankTracking"].ToString();
                        }
                        else
                        {
                            row.Memo = dr["Notes"].ToString();
                        }
                        if (dr["Amount"] != DBNull.Value)
                        {
                            row.Payment = double.Parse(dr["Amount"].ToString());
                        }
                        row.BankAccountName = dr["AccountName"].ToString();
                        row.Address         = dr["Address"].ToString() + " -- " + dr["UnitName"].ToString();
                        if (dr["DueAmount"] != DBNull.Value)
                        {
                            row.DueAmount = double.Parse(dr["DueAmount"].ToString());
                        }
                        row.ID = int.Parse(dr["ID"].ToString());
                        if (dr["StatusID"] != DBNull.Value)
                        {
                            row.StatusID   = short.Parse(dr["StatusID"].ToString());
                            row.StatusName = dr["StatusName"].ToString();
                        }
                        if (dr["LinkedExpenseID"] != DBNull.Value)
                        {
                            row.LinkedExpenseID = Int32.Parse(dr["LinkedExpenseID"].ToString());
                        }
                        row.CategoryName = dr["CategoryName"].ToString();
                        result.Add(row);
                    }
                }
            }

            return(result);
        }
        public static void DeleteRent(int rentID)
        {
            OperationRecord op = GetExpenseByRentID(rentID);

            DeleteOperationRecord(op);
        }
        public static void Delete(int ID)
        {
            OperationRecord op = GetExpenseByID(ID);

            DeleteOperationRecord(op);
        }
        public static OperationRecord GetExpenseByRentID(int id)
        {
            string SQLString = "select tblUnitOperation.ID, tblUnitOperation.IsCredit, tblUnitOperation.StatusID, tblUnitOperation.CategoryID, InvoiceLink, tblUnitOperation.ContractorID, Amount, tblUnitOperation.DueDate, tblUnitOperation.FinishDate, tblUnitOperation.UnitID, Notes, tblUnitOperation.FinancialAccountID , DueAmount, tblUnitOperation.LinkedRentID, tblUnitOperation.BankTracking, TenantID from tblUnitOperation, tblRent where tblUnitOperation.LinkedRentID= tblRent.RentID and LinkedRentID =  " + id;

            using (SqlDataAdapter adapter = new SqlDataAdapter(SQLString, Helpers.Helpers.GetAppConnectionString()))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                DataTable       tb   = (DataTable)ds.Tables[0];
                OperationRecord role = new OperationRecord();
                if (tb == null || tb.Rows.Count == 0)
                {
                    SQLString = "1 as IsCredit, tblRent.StatusID, 36 as CategoryID, tblTenant.UserID as ContractorID, tblTenant.UnitID AS UnitID, PaidAmount as Amount, DueDate, PaymentDate as FinishDate, tblRent.Note as Notes, FinancialAccountID , RentAmount as DueAmount, tblRent.RentID AS LinkedRentID from tblRent, tblTenant where tblTenant.TenantID=tblRent.TenantID AND tblRent.RentID =" + id;
                }
                if (tb != null && tb.Rows.Count > 0)
                {
                    DataRow dr = tb.Rows[0];
                    role.ID = int.Parse(dr["ID"].ToString());

                    if (dr["IsCredit"] != DBNull.Value)
                    {
                        role.IsCredit = Boolean.Parse(dr["IsCredit"].ToString());
                    }

                    if (dr["StatusID"] != DBNull.Value)
                    {
                        role.StatusID = short.Parse(dr["StatusID"].ToString());
                    }

                    if (dr["CategoryID"] != DBNull.Value)
                    {
                        role.CategoryID = short.Parse(dr["CategoryID"].ToString());
                    }

                    if (dr["ContractorID"] != DBNull.Value)
                    {
                        role.ContractorID = (int)dr["ContractorID"];
                    }
                    if (dr["TenantID"] != DBNull.Value)
                    {
                        role.TenantID = (int)dr["TenantID"];
                    }
                    if (dr["Amount"] != DBNull.Value)
                    {
                        if (role.IsCredit)
                        {
                            role.Payment = double.Parse(dr["Amount"].ToString());
                        }
                        else
                        {
                            role.Payment = -double.Parse(dr["Amount"].ToString());
                        }
                    }
                    if (dr["DueDate"] != DBNull.Value)
                    {
                        role.DueDate = DateTime.Parse(dr["DueDate"].ToString());
                    }
                    if (dr["FinishDate"] != DBNull.Value)
                    {
                        role.CompleteDate = DateTime.Parse(dr["FinishDate"].ToString());
                    }
                    if (dr["UnitID"] != DBNull.Value)
                    {
                        role.UnitID = (int)dr["UnitID"];
                    }
                    role.Memo                   = dr["Notes"].ToString();
                    role.BankTracking           = dr["BankTracking"].ToString();
                    role.FinancialBankAccountID = dr["FinancialAccountID"].ToString();
                    if (dr["DueAmount"] != DBNull.Value)
                    {
                        role.DueAmount = double.Parse(dr["DueAmount"].ToString());
                    }
                    if (dr["InvoiceLink"] != DBNull.Value)
                    {
                        role.InvoiceLink = dr["InvoiceLink"].ToString();
                    }
                    if (dr["LinkedRentID"] != DBNull.Value)
                    {
                        role.LinkedRentID = dr["LinkedRentID"].ToString();
                    }
                    role.IsCredit       = true;
                    role.IsEmailReceipt = true;
                }
                return(role);
            }
        }
Exemple #21
0
        public static void EditTask(AddTaskVM model)
        {
            using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString()))
            {
                try
                {
                    int linkedExpenseID = GetTaskByID(model.TaskID).LinkedExpenseID;
                    //for closed task, system will auto generated expense
                    if (model.StatusID == (int)Helpers.Helpers.StatusType.Close)
                    {
                        if (model.LinkedExpenseID == 0)
                        {
                            //add expense
                            OperationRecord op = new OperationRecord();
                            op.ContractorID = model.ContractorID;
                            op.CategoryID   = (int)Helpers.Helpers.ExpenseCategory.Repair;
                            op.DueAmount    = model.TotalPayment;
                            op.DueDate      = DateTime.Now;
                            op.CompleteDate = DateTime.Now;
                            op.UnitID       = model.UnitID;
                            //op.UploadBy = Int32.Parse(Session["UserID"].ToString());
                            op.FinancialBankAccountID = model.BankAccountID.ToString();
                            op.Payment            = model.TotalPayment;
                            op.Memo               = "Labor: " + model.Hours + " Milage: " + model.Milage + " Material: " + model.Material;
                            op.StatusID           = (short)Helpers.Helpers.StatusType.Close;
                            model.LinkedExpenseID = OperationRecordManager.CreateOperationRecord(op);
                        }
                        else
                        {
                            //update expense
                            OperationRecord op = OperationRecordManager.GetExpenseByID(model.LinkedExpenseID);
                            op.ContractorID = model.ContractorID;
                            op.CategoryID   = (int)Helpers.Helpers.ExpenseCategory.Repair;
                            op.DueAmount    = model.TotalPayment;
                            op.DueDate      = DateTime.Now;
                            op.CompleteDate = DateTime.Now;
                            op.UnitID       = model.UnitID;
                            //op.UploadBy = Int32.Parse(Session["UserID"].ToString());
                            op.FinancialBankAccountID = model.BankAccountID.ToString();
                            op.Payment = model.TotalPayment;
                            op.Memo    = "Labor: " + model.Hours + " Milage: " + model.Milage + " Material: " + model.Material;
                            OperationRecordManager.UpdateOperationRecord(op);
                        }
                    }
                    // Create the Command and Parameter objects.
                    SqlCommand cmd = new SqlCommand("", connection);
                    connection.Open();
                    model.TotalPayment = model.Hours * 15 + model.Milage * 0.53 + model.Material;
                    cmd.CommandText    = "Update tblTasks set TaskDetail = '" + model.TaskDetail.Replace("'", "") + "', UpdateDate='" + DateTime.Now
                                         + "', ContractorID=" + model.ContractorID
                                         + ", StatusID=" + model.StatusID
                                         + ", WorkHours =" + model.Hours
                                         + ", Milage =" + model.Milage
                                         + ", Labor =" + model.Labor
                                         + ", MaterialCost =" + model.Material
                                         + ", TotalCost =" + model.TotalPayment
                                         + ", PaymentBankAccountID =" + model.BankAccountID
                                         + ", LinkedExpenseID =" + model.LinkedExpenseID
                                         + ", UnitID =" + model.UnitID
                                         + "  where TaskID=" + model.TaskID;
                    cmd.ExecuteNonQuery();

                    connection.Close();
                }
                catch (Exception ex)
                {
                    string exms = ex.Message;
                }
            }
        }