public static string CreateContractForm(ContractForm contractForm, QueryHelper qh)
        {
            var head =
               new[]
                {
                    ContractForm.User_ID, ContractForm.CUSTOMER_ID, ContractForm.AGREEMENT_NO,
                    ContractForm.EFFECTIVE_DATE, ContractForm.EXPIRY_DATE, ContractForm.TOTAL_CONTRACT_AMT,
                    ContractForm.CONTRACT_TYPE_ID,ContractForm.REMARK, ContractForm.STATUS_FLG
                }.ToList();
            var data = new[]
            {
                new[]
                {
                    contractForm.UserId.ToString(),contractForm.CustomerId.ToString(),contractForm.AgreementNo,
                    contractForm.EffectiveDate.ToUniversalTime().ToString("yyyy-MM-dd H:mm:ss"), contractForm.ExpiryDate.ToUniversalTime().ToString("yyyy-MM-dd H:mm:ss"), contractForm.TotalContractAmt.ToString(),
                    contractForm.ContractTypeId.ToString(),contractForm.Remarks,contractForm.StatusFlg.ToString()

                }.ToList()
            }.ToList();

            if (!string.IsNullOrWhiteSpace(contractForm.id.ToString()))
            {
                head.Add(ContractForm.DB_ID);
                data[0].Add(contractForm.id.ToString());
            }
            var query = QueryHelper.BuildBulkInsertQuery(ContractForm.DB_TABLE_NAME, head, data, true, ContractForm.DB_ID);

            return qh.QueryInsertUpdateDelete(query).ToString();
        }
 public static User GetUser(string userid, QueryHelper qh)
 {
     var query = QueryHelper.BuildSelectQuery(User.DB_TABLE_NAME,
        new[] { "*" }.ToList(),
        new[] { User.DB_ID }.ToList(),
        new[] { userid }.ToList());
     var queryResult = qh.QuerySelect(query);
     return queryResult.Select(User.Parse).FirstOrDefault();
 }
 public static ContractType GetContractType(string contractTypeId, QueryHelper qh)
 {
     var query = QueryHelper.BuildSelectQuery(ContractType.DB_TABLE_NAME,
        new[] { "*" }.ToList(),
        new[] { ContractType.DB_ID }.ToList(),
        new[] { contractTypeId }.ToList());
     var queryResult = qh.QuerySelect(query);
     return queryResult.Select(ContractType.Parse).FirstOrDefault();
 }
        public static List<ContractForm> GetAllContractForms(QueryHelper qh)
        {
            try
            {
                var query = QueryHelper.BuildSelectQuery(ContractForm.DB_TABLE_NAME,
                new[] { "*" }.ToList());
                var queryResult = qh.QuerySelect(query);
                return queryResult.Select(ContractForm.Parse).ToList();

            }
            catch (ArgumentNullException ex)
            {
                throw new ArgumentNullException("Can't GetAllContractForms", ex);
            }
        }
 public static Customer GetCustomer(string customerId, QueryHelper qh)
 {
     try
     {
         string query = QueryHelper.BuildSelectQuery(Customer.DB_TABLE_NAME,
                 new[] { "*" }.ToList(),
                 new[] { Customer.DB_ID }.ToList(),
                 new[] { customerId }.ToList());
         var queryResult = qh.QuerySelect(query);
         return queryResult.Select(Customer.Parse).FirstOrDefault();
     }
     catch (ArgumentNullException ex)
     {
         throw new Exception("Can't GetCustomer", ex);
     }
 }
        public static string ModifyContractFormWithId(string contract_id, ContractForm contractForm, QueryHelper qh)
        {
            var head =
               new[]
                {
                    ContractForm.User_ID, ContractForm.CUSTOMER_ID, ContractForm.AGREEMENT_NO,
                    ContractForm.EFFECTIVE_DATE, ContractForm.EXPIRY_DATE, ContractForm.TOTAL_CONTRACT_AMT,
                    ContractForm.CONTRACT_TYPE_ID,ContractForm.REMARK, ContractForm.STATUS_FLG
                }.ToList();
            var data = new[]
            {
                    contractForm.UserId.ToString(),contractForm.CustomerId.ToString(),contractForm.AgreementNo,
                    contractForm.EffectiveDate.ToUniversalTime().ToString("yyyy-MM-dd H:mm:ss"), contractForm.ExpiryDate.ToUniversalTime().ToString("yyyy-MM-dd H:mm:ss"), contractForm.TotalContractAmt.ToString(),
                    contractForm.ContractTypeId.ToString(),contractForm.Remarks,contractForm.StatusFlg.ToString()

            }.ToList();

            var query = QueryHelper.BuildUpdateQuery(ContractForm.DB_TABLE_NAME,
                head,
                data,
                new[] { ContractForm.DB_ID }.ToList(),
                new[] { contract_id }.ToList());
            return qh.QueryInsertUpdateDelete(query).ToString();
        }
 //check contractTypeId against DB
 public static bool isContractTypeExist(int contractTypeId)
 {
     try
     {
         var qh = new QueryHelper();
         ContractType contractType = null;
         contractType = ContractTypeManager.GetContractType(contractTypeId.ToString(), qh);
         if (contractType != null)
         {
             return true;
         }
         return false;
     }
     catch (ArgumentException ex)
     {
         throw ex;
     }
 }
        public static string CreateContractForm(string jsonString)
        {
            //convert jsonString to object
            /* {
                    "UserId" : "1",
                    "ContractTypeId" : 1,
                    "CustomerId" : 1,
                    "EffectiveDate" : "2015-06-21T18:24:18Z",
                    "ExpiryDate" : "2015-06-22T18:24:18Z",
                    "TotalContractAmt" : 200.36,
                    "Remarks" : "testing remark text"
                    "StatusFlg" : 0
                }
             */
            ContractForm cf = null;
            try
            {
                cf = new ContractForm(jsonString);
            }
            catch (JsonException ex)
            {
                throw new Exception("Unable to convert jsonString to ContractForm object", ex);
            }

            try
            {
                string contractFormId = null;
                //validate user inputs
                string invalidType = "";

                if (IsValidCreateContractForm(cf, out invalidType))
                {
                    // save the contract form into DB
                    QueryHelper qh = new QueryHelper();

                    contractFormId = ContractFormManager.CreateContractForm(cf, qh);
                    // generate agreement no. based on DB's id

                    cf.AgreementNo = GenerateAgreementNo(cf, contractFormId, qh);
                    // save the agreement no. into DB

                    ContractFormManager.ModifyContractFormWithId(contractFormId,cf,qh);

                    //save everything

                    qh.Commit();
                }
                // pass feedback message
                string feedbackMsg = "";

                if (!invalidType.Equals(""))
                {
                    feedbackMsg = "Fail to create contract form due to " + invalidType;
                    return feedbackMsg;
                }

                if (contractFormId == null)
                {
                    feedbackMsg = "Unable to create contract form";
                    return feedbackMsg;
                }
                feedbackMsg = "Success: contractForm Id - " + contractFormId;
                return feedbackMsg;

            }
            catch (ArgumentNullException ex)
            {
                return ex.Message;
            }
        }
        private static string GenerateAgreementNo(ContractForm cf , string cf_id, QueryHelper qh)
        {
            try
            {
                 ContractType contractType = ContractTypeManager.GetContractType(cf.ContractTypeId.ToString(), qh);
                User user = UserManager.GetUser(cf.UserId.ToString(), qh);
                Customer customer = CustomerManager.GetCustomer(cf.CustomerId.ToString(), qh);
                string temp = customer.customerName;

                // below block of code is to get the runningNo
                return "" + contractType.contractTypeName + "/"
                    + user.userName + "/"
                    + customer.customerName + "/"
                    + "C" + cf.EffectiveDate.Year + "-"
                    + cf_id;

            }
            catch (ArgumentNullException ex)
            {
                throw ex;
            }
        }
 //check customerId against DB
 public static bool isValidUser(int userId)
 {
     try
     {
         var qh = new QueryHelper();
         User user = null;
         user = UserManager.GetUser(userId.ToString(), qh);
         if (user != null)
         {
             return true;
         }
         return false;
     }
     catch (ArgumentException ex)
     {
         throw ex;
     }
 }
 //check customerId against DB
 public static bool isCustomerExist(int customerId)
 {
     try
     {
         var qh = new QueryHelper();
         Customer customer = null;
         customer = CustomerManager.GetCustomer(customerId.ToString(), qh);
         if (customer != null)
         {
             return true;
         }
         return false;
     }
     catch (ArgumentNullException ex)
     {
         throw ex;
     }
 }