Beispiel #1
0
        public void UpdateOnExcel(InsureCarData newItem)
        {
            try
            {
                MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
                DBbase.Connect();
                StringBuilder sql = new StringBuilder();

                sql.Append("UPDATE MA_INSURE_CAR SET ");
                sql.Append(" COMPANY_CODE = N'" + newItem.COMPANY_CODE + "',");
                sql.Append(" PACKAGE_NAME = N'" + newItem.PACKAGE_NAME + "',");
                sql.Append(" CAR_ID = '" + newItem.CAR_ID + "',");
                sql.Append(" INSURE_CATEGORY = N'" + newItem.INSURE_CATEGORY + "',");
                //sql.Append(" INSURE_TYPE_REPAIR = '" + newItem.INSURE_TYPE_REPAIR + "',");
                sql.Append(" CAR_YEAR = N'" + newItem.CAR_YEAR + "',");
                sql.Append(" LIVE_COVERAGE_PEOPLE = '" + newItem.LIVE_COVERAGE_PEOPLE + "',");
                sql.Append(" LIVE_COVERAGE_TIME = '" + newItem.LIVE_COVERAGE_TIME + "',");
                sql.Append(" ASSET_TIME = '" + newItem.ASSET_TIME + "',");
                sql.Append(" INSURE_TYPE_REPAIR = '" + newItem.INSURE_TYPE_REPAIR + "',");
                sql.Append(" DAMAGE_TO_VEHICLE = '" + newItem.DAMAGE_TO_VEHICLE + "',");
                sql.Append(" MISSING_FIRE_CAR = '" + newItem.MISSING_FIRE_CAR + "',");
                sql.Append(" FIRST_DAMAGE_PRICE = '" + newItem.FIRST_DAMAGE_PRICE + "',");
                sql.Append(" PERSONAL_ACCIDENT_AMT = '" + newItem.PERSONAL_ACCIDENT_AMT + "',");
                sql.Append(" PERSONAL_ACCIDENT_PEOPLE = '" + newItem.PERSONAL_ACCIDENT_PEOPLE + "',");
                sql.Append(" MEDICAL_FEE_AMT = '" + newItem.MEDICAL_FEE_AMT + "',");
                sql.Append(" MEDICAL_FEE_PEOPLE = '" + newItem.MEDICAL_FEE_PEOPLE + "',");
                sql.Append(" DRIVER_INSURANCE_AMT = '" + newItem.DRIVER_INSURANCE_AMT + "',");
                sql.Append(" NET_PRICE = '" + newItem.NET_PRICE + "',");
                sql.Append(" TOTAL_PRICE = '" + newItem.TOTAL_PRICE + "',");
                sql.Append(" PRICE_ROUND = '" + newItem.PRICE_ROUND + "',");
                sql.Append(" CAPITAL_INSURANCE = '" + newItem.CAPITAL_INSURANCE + "',");
                sql.Append(" INSURE_PRIORITY = '" + newItem.INSURE_PRIORITY + "',");
                sql.Append(" EFFECTIVE_DATE = '" + ConvertCommon.ConvertDateTime(newItem.EFFECTIVE_DATE) + "',");
                sql.Append(" EXPIRE_DATE = '" + ConvertCommon.ConvertDateTime(newItem.EXPIRE_DATE) + "',");
                sql.Append(" CONFIDENTIAL_STATUS = '" + newItem.CONFIDENTIAL_STATUS + "',");
                sql.Append(" UPDATE_DATE = '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',");
                sql.Append(" UPDATE_USER = '******'");
                sql.Append(" WHERE 	COMPANY_CODE = '" + newItem.COMPANY_CODE + "'");
                sql.Append(" AND 	PACKAGE_NAME = '"+ newItem.PACKAGE_NAME + "'");
                sql.Append(" AND 	CAR_ID = '"+ newItem.CAR_ID + "'");
                sql.Append(" AND 	INSURE_CATEGORY = '"+ newItem.INSURE_CATEGORY + "'");
                sql.Append(" AND 	INSURE_TYPE_REPAIR = '"+ newItem.INSURE_TYPE_REPAIR + "'");
                sql.Append(" AND 	CAR_YEAR = '"+ newItem.CAR_YEAR + "'");
                sql.Append(" AND DAMAGE_TO_VEHICLE = '" + newItem.DAMAGE_TO_VEHICLE + "'");
                SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
                cmd.ExecuteNonQuery();
                DBbase.DisConnect();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 public void UpdateStatus(string code)
 {
     try
     {
         MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
         DBbase.Connect();
         StringBuilder sql = new StringBuilder();
         sql.Append("UPDATE TA_SELECT_INSURANCE ");
         sql.Append("SET SELECT_INSURANCE_STATUS = '02',");
         sql.Append(" WINDOW_IP = '" + UtilityCommon.GetLocalIPAddress() + "',");
         sql.Append(" UPDATE_DATE = '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',");
         sql.Append(" UPDATE_USER = '******'");
         sql.Append(" WHERE SELECT_INSURANCE_CODE = '" + code + "'");
         SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
         cmd.ExecuteNonQuery();
         DBbase.DisConnect();
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
Beispiel #3
0
        public void InsertList(List <InsureCarData> listItem)
        {
            try
            {
                MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
                int        row    = 1;

                SqlCommand cmd;
                string     query = "";

                StringBuilder sql;
                foreach (InsureCarData item in listItem)
                {
                    DBbase.Connect();
                    sql = new StringBuilder();
                    sql.Append(@"INSERT INTO MA_INSURE_CAR (INSURE_CAR_CODE, COMPANY_CODE, PACKAGE_NAME, CAR_ID, INSURE_CATEGORY,INSURE_TYPE_REPAIR,CAR_YEAR,LIVE_COVERAGE_PEOPLE,
                LIVE_COVERAGE_TIME,ASSET_TIME,DAMAGE_TO_VEHICLE,MISSING_FIRE_CAR,FIRST_DAMAGE_PRICE,PERSONAL_ACCIDENT_AMT,PERSONAL_ACCIDENT_PEOPLE,MEDICAL_FEE_AMT,
                MEDICAL_FEE_PEOPLE,DRIVER_INSURANCE_AMT,NET_PRICE,TOTAL_PRICE,PRICE_ROUND,CAPITAL_INSURANCE,INSURE_PRIORITY,EFFECTIVE_DATE,EXPIRE_DATE,CONFIDENTIAL_STATUS,
                INSURE_CAR_STATUS,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER)  VALUES ");
                    string INSURE_CAR_CODE = DateTime.Now.ToString("yyyyMMdd") + "-" + item.COMPANY_CODE.ToUpper() + "-" + item.PACKAGE_NAME;
                    INSURE_CAR_CODE += "-" + item.CAR_ID + "-" + item.INSURE_CATEGORY;
                    INSURE_CAR_CODE += (item.INSURE_TYPE_REPAIR == "ศูนย์") ? "C" : "G" + "-" + item.CAR_YEAR;

                    sql.Append("( N'" + INSURE_CAR_CODE + "',");
                    sql.Append(" N'" + item.COMPANY_CODE.ToUpper() + "',");
                    sql.Append(" N'" + item.PACKAGE_NAME + "',");
                    sql.Append(" '" + item.CAR_ID + "',");
                    sql.Append(" N'" + item.INSURE_CATEGORY + "',");
                    sql.Append(" N'" + item.INSURE_TYPE_REPAIR + "',");

                    sql.Append(" N'" + item.CAR_YEAR + "',");
                    sql.Append(" '" + item.LIVE_COVERAGE_PEOPLE + "',");
                    sql.Append(" '" + item.LIVE_COVERAGE_TIME + "',");
                    sql.Append(" '" + item.ASSET_TIME + "',");
                    sql.Append(" '" + item.DAMAGE_TO_VEHICLE + "',");

                    sql.Append(" '" + item.MISSING_FIRE_CAR + "',");
                    sql.Append(" '" + item.FIRST_DAMAGE_PRICE + "',");
                    sql.Append(" '" + item.PERSONAL_ACCIDENT_AMT + "',");
                    sql.Append(" '" + item.PERSONAL_ACCIDENT_PEOPLE + "',");
                    sql.Append(" '" + item.MEDICAL_FEE_AMT + "',");

                    sql.Append(" '" + item.MEDICAL_FEE_PEOPLE + "',");
                    sql.Append(" '" + item.DRIVER_INSURANCE_AMT + "',");
                    sql.Append(" '" + item.NET_PRICE + "',");
                    sql.Append(" '" + item.TOTAL_PRICE + "',");
                    sql.Append(" '" + item.PRICE_ROUND + "',");

                    sql.Append(" '" + item.CAPITAL_INSURANCE + "',");
                    sql.Append(" '" + item.INSURE_PRIORITY + "',");
                    sql.Append(" '" + ConvertCommon.ConvertDateTime(item.EFFECTIVE_DATE) + "',");
                    sql.Append(" '" + ConvertCommon.ConvertDateTime(item.EXPIRE_DATE) + "',");
                    sql.Append(" '" + item.CONFIDENTIAL_STATUS + "',");

                    sql.Append(" '" + item.INSURE_CAR_STATUS + "',");
                    sql.Append(" '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',");
                    sql.Append(" '" + member.MEMBER_USER + "',");
                    sql.Append(" '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',");
                    sql.Append(" '" + member.MEMBER_USER + "') ");

                    row++;

                    try
                    {
                        query = sql.ToString();
                        cmd   = new SqlCommand(query.Remove(query.Length - 1), DBbase.con);
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException exception)
                    {
                        if (exception.Number == 1062) // Cannot insert duplicate key row in object error
                        {
                        }
                        else
                        {
                            throw exception; // throw exception if this exception is unexpected
                        }
                    }
                    DBbase.DisConnect();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }