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;
}
}
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;
}
}