public DataTable GetAllCondition(string carYear, string carName, string carModel, string carEngine)
{
try
{
DBbase.Connect();
string sql = @"SELECT A.INSURE_CAR_CODE, A.COMPANY_CODE, A.PACKAGE_NAME, A.CAR_ID,
A.INSURE_CATEGORY, A.INSURE_TYPE_REPAIR, A.CAR_YEAR, A.LIVE_COVERAGE_PEOPLE,
A.LIVE_COVERAGE_TIME, A.ASSET_TIME, A.DAMAGE_TO_VEHICLE,
A.MISSING_FIRE_CAR, A.FIRST_DAMAGE_PRICE, A.PERSONAL_ACCIDENT_AMT,
A.PERSONAL_ACCIDENT_PEOPLE, A.MEDICAL_FEE_AMT, A.MEDICAL_FEE_PEOPLE,
A.DRIVER_INSURANCE_AMT, A.NET_PRICE, A.TOTAL_PRICE, A.PRICE_ROUND,
A.CAPITAL_INSURANCE, A.INSURE_PRIORITY, A.EFFECTIVE_DATE, A.EXPIRE_DATE,
A.CONFIDENTIAL_STATUS, A.CREATE_DATE, A.CREATE_USER, A.UPDATE_DATE,
A.UPDATE_USER, A.INSURE_CAR_STATUS, C.CAR_CODE,C.CAR_NAME,C.CAR_MODEL,C.CAR_ENGINE,C.CAR_IMAGE ,I.COMPANY_FULLNAME, I.COMPANY_CODE, I.COMPANY_PATH_PIC
FROM MA_INSURE_CAR A INNER JOIN MA_CAR C ON A.CAR_ID = C.CAR_ID INNER JOIN MA_INSURE_COMPANY I ON A.COMPANY_CODE = I.COMPANY_CODE
WHERE A.INSURE_CAR_STATUS = 'A' AND A.CAR_YEAR ='" + carYear + "' AND C.CAR_NAME = '" + carName + "' AND C.CAR_MODEL = '" + carModel + "' AND C.CAR_ENGINE = '" + carEngine + "' AND A.INSURE_PRIORITY = 999"
+ " ORDER BY I.COMPANY_CODE ";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(InsureCompanyData item)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE MA_INSURE_COMPANY SET COMPANY_CODE = '" + item.COMPANY_CODE.ToUpper() + "',");
sql.Append(" COMPANY_FULLNAME = '" + item.COMPANY_FULLNAME + "',");
sql.Append(" COMPANY_PATH_PIC = '" + item.COMPANY_PATH_PIC + "',");
sql.Append(" COMPANY_REMARK = '" + item.COMPANY_REMARK + "',");
sql.Append(" COMPANY_SHORTNAME = '" + item.COMPANY_SHORTNAME + "',");
sql.Append(" COMPANY_STATUS = '" + item.COMPANY_STATUS.ToUpper() + "',");
sql.Append(" UPDATE_DATE = '" + DateTime.Now + "',");
sql.Append(" UPDATE_USER = '******'");
sql.Append(" WHERE COMPANY_CODE = '" + item.COMPANY_CODE.ToUpper() + "'");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertMember(MemberData item)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO MA_MEMBER (MEMBER_USER,MEMBER_PASSWORD,MEMBER_NAME,MEMBER_SURENAME,MEMBER_STATUS,ROLE_CODE,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER) VALUES (");
sql.Append(" '" + item.MEMBER_USER + "',");
sql.Append(" '" + item.MEMBER_PASSWORD + "',");
sql.Append(" '" + item.MEMBER_NAME + "',");
sql.Append(" '" + item.MEMBER_SURENAME + "',");
sql.Append(" '" + item.MEMBER_STATUS + "',");
sql.Append(" '" + item.ROLE_CODE.ToLower() + "',");
sql.Append(" '" + DateTime.Now + "',");
sql.Append(" '" + member.MEMBER_USER + "',");
sql.Append(" '" + DateTime.Now + "',");
sql.Append(" '" + member.MEMBER_USER + "')");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (Exception ex)
{
throw ex;
}
}
public void Insert(InsureCompanyData item)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO MA_INSURE_COMPANY (COMPANY_CODE,COMPANY_FULLNAME,COMPANY_PATH_PIC,COMPANY_REMARK,COMPANY_SHORTNAME,COMPANY_STATUS,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER) VALUES (");
sql.Append(" '" + item.COMPANY_CODE.ToUpper() + "',");
sql.Append(" '" + item.COMPANY_FULLNAME + "',");
sql.Append(" '" + item.COMPANY_PATH_PIC + "',");
sql.Append(" '" + item.COMPANY_REMARK + "',");
sql.Append(" '" + item.COMPANY_SHORTNAME + "',");
sql.Append(" '" + item.COMPANY_STATUS + "',");
sql.Append(" '" + DateTime.Now + "',");
sql.Append(" '" + member.MEMBER_USER + "',");
sql.Append(" '" + DateTime.Now + "',");
sql.Append(" '" + member.MEMBER_USER + "')");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateMember(MemberData item)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE MA_MEMBER SET MEMBER_NAME = '" + item.MEMBER_NAME + "',");
sql.Append(" MEMBER_SURENAME = '" + item.MEMBER_SURENAME + "',");
sql.Append(" MEMBER_USER = '******',");
sql.Append(" MEMBER_PASSWORD = '******',");
sql.Append(" ROLE_CODE = '" + item.ROLE_CODE + "',");
sql.Append(" MEMBER_STATUS = '" + item.MEMBER_STATUS + "',");
sql.Append(" UPDATE_DATE = '" + DateTime.Now + "',");
sql.Append(" UPDATE_USER = '******'");
sql.Append(" WHERE MEMBER_USER = '******'");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (Exception ex)
{
throw ex;
}
}
public string GetCompanyCode(string name)
{
try
{
DBbase.Connect();
string sql = "SELECT COMPANY_CODE FROM MA_INSURE_COMPANY WHERE COMPANY_FULLNAME = '" + name + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string stringReturn = reader["COMPANY_CODE"].ToString();
reader.Close();
DBbase.DisConnect();
return(stringReturn);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public MemberData GetMember(string user, string pass)
{
try
{
DBbase.Connect();
string sql = "SELECT MEMBER_NAME,MEMBER_SURENAME,MEMBER_USER,MEMBER_PASSWORD,MEMBER_STATUS,ROLE_CODE FROM MA_MEMBER WHERE MEMBER_USER = '******' AND MEMBER_PASSWORD = '******'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
MemberData member = new MemberData();
member.MEMBER_NAME = reader["MEMBER_NAME"].ToString();
member.MEMBER_SURENAME = reader["MEMBER_SURENAME"].ToString();
member.MEMBER_USER = reader["MEMBER_USER"].ToString();
member.MEMBER_PASSWORD = reader["MEMBER_PASSWORD"].ToString();
member.MEMBER_STATUS = reader["MEMBER_STATUS"].ToString();
member.ROLE_CODE = reader["ROLE_CODE"].ToString();
reader.Close();
DBbase.DisConnect();
return(member);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public string CheckStatus(string code)
{
try
{
DBbase.Connect();
string sql = @"SELECT SELECT_INSURANCE_STATUS FROM TA_SELECT_INSURANCE WHERE SELECT_INSURANCE_CODE ='" + code + "' AND SELECT_INSURANCE_STATUS = '01'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
string stringReturn = "";
if (reader.Read())
{
stringReturn = reader["SELECT_INSURANCE_STATUS"].ToString();
reader.Close();
DBbase.DisConnect();
return(stringReturn);
}
else
{
reader.Close();
DBbase.DisConnect();
return(stringReturn);
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(CarData oldItem, CarData newItem)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE MA_CAR SET CAR_CODE = '" + newItem.CAR_CODE + "',");
sql.Append(" CAR_ENGINE = '" + newItem.CAR_ENGINE + "',");
sql.Append(" CAR_MODEL = '" + newItem.CAR_MODEL + "',");
sql.Append(" CAR_NAME = '" + newItem.CAR_NAME + "',");
sql.Append(" CAR_REMARK = '" + newItem.CAR_REMARK + "',");
sql.Append(" CAR_STATUS = '" + newItem.CAR_STATUS + "',");
sql.Append(" UPDATE_DATE = '" + DateTime.Now + "',");
sql.Append(" UPDATE_USER = '******'");
sql.Append(" WHERE CAR_CODE = '" + oldItem.CAR_CODE + "'");
sql.Append(" AND CAR_ENGINE = '" + oldItem.CAR_ENGINE + "'");
sql.Append(" AND CAR_MODEL = '" + oldItem.CAR_MODEL + "'");
sql.Append(" AND CAR_NAME = '" + oldItem.CAR_NAME + "'");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (Exception ex)
{
throw ex;
}
}
public CarData GetItem(string carName, string carModel, string carEngine)
{
try
{
DBbase.Connect();
string sql = @"SELECT CAR_ID,CAR_CODE,CAR_NAME,CAR_MODEL,CAR_ENGINE,CAR_REMARK,CAR_STATUS FROM MA_CAR WHERE
CAR_NAME = '" + carName + "' AND CAR_MODEL = '" + carModel + "' AND CAR_ENGINE = '" + carEngine + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
CarData item = new CarData();
item.CAR_ID = Convert.ToInt32(reader["CAR_ID"].ToString());
item.CAR_CODE = reader["CAR_CODE"].ToString();
item.CAR_NAME = reader["CAR_NAME"].ToString();
item.CAR_MODEL = reader["CAR_MODEL"].ToString();
item.CAR_ENGINE = reader["CAR_ENGINE"].ToString();
item.CAR_REMARK = reader["CAR_REMARK"].ToString();
item.CAR_STATUS = reader["CAR_STATUS"].ToString();
reader.Close();
DBbase.DisConnect();
return(item);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetAll()
{
try
{
DBbase.Connect();
string sql = @"SELECT T.SELECT_INSURANCE_CODE,T.CUSTOMER_NAME,T.CUSTOMER_EMAIL,T.CUSTOMER_TEL,T.SELECT_INSURANCE_STATUS,
(CASE T.SELECT_INSURANCE_STATUS WHEN '01' THEN 'ส่งเรื่อง' WHEN '02' THEN 'รับเรื่อง' WHEN '03' THEN 'ติดต่อแล้ว' WHEN '04' THEN 'ข้อมูลเท็จ' END) AS SELECT_INSURANCE_STATUS_NAME ,
T.WINDOW_IP,T.AGENT_CODE,T.TRANSACTION_TYPE,T.UPDATE_USER,
A.INSURE_CAR_CODE, A.COMPANY_CODE, A.PACKAGE_NAME, A.CAR_ID,
A.INSURE_CATEGORY, A.INSURE_TYPE_REPAIR, A.CAR_YEAR, A.LIVE_COVERAGE_PEOPLE,
A.LIVE_COVERAGE_TIME, A.ASSET_TIME, A.DAMAGE_TO_VEHICLE,
A.MISSING_FIRE_CAR, A.FIRST_DAMAGE_PRICE, A.PERSONAL_ACCIDENT_AMT,
A.PERSONAL_ACCIDENT_PEOPLE, A.MEDICAL_FEE_AMT, A.MEDICAL_FEE_PEOPLE,
A.DRIVER_INSURANCE_AMT, A.NET_PRICE, A.TOTAL_PRICE, A.PRICE_ROUND,
A.CAPITAL_INSURANCE, A.INSURE_PRIORITY, A.EFFECTIVE_DATE, A.EXPIRE_DATE,
A.CONFIDENTIAL_STATUS, A.CREATE_DATE, A.CREATE_USER, A.UPDATE_DATE,
A.UPDATE_USER, A.INSURE_CAR_STATUS, C.CAR_CODE,C.CAR_NAME,C.CAR_MODEL,C.CAR_ENGINE ,I.COMPANY_FULLNAME
FROM MA_INSURE_CAR A INNER JOIN MA_CAR C ON A.CAR_ID = C.CAR_ID INNER JOIN MA_INSURE_COMPANY I ON A.COMPANY_CODE = I.COMPANY_CODE
INNER JOIN TA_SELECT_INSURANCE T ON A.INSURE_CAR_CODE = T.INSURE_CAR_CODE ORDER BY A.CREATE_DATE ,T.SELECT_INSURANCE_STATUS";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public InsureCompanyData GetItem(string code)
{
try
{
DBbase.Connect();
string sql = "SELECT COMPANY_CODE,COMPANY_FULLNAME,COMPANY_PATH_PIC,COMPANY_REMARK,COMPANY_SHORTNAME,COMPANY_STATUS FROM MA_INSURE_COMPANY WHERE COMPANY_CODE = '" + code + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
InsureCompanyData item = new InsureCompanyData();
item.COMPANY_CODE = reader["COMPANY_CODE"].ToString();
item.COMPANY_FULLNAME = reader["COMPANY_FULLNAME"].ToString();
item.COMPANY_PATH_PIC = reader["COMPANY_PATH_PIC"].ToString();
item.COMPANY_REMARK = reader["COMPANY_REMARK"].ToString();
item.COMPANY_SHORTNAME = reader["COMPANY_SHORTNAME"].ToString();
item.COMPANY_STATUS = reader["COMPANY_STATUS"].ToString();
reader.Close();
DBbase.DisConnect();
return(item);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
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 Insert(CarData item)
{
try
{
MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER");
DBbase.Connect();
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO MA_CAR (CAR_CODE,CAR_ENGINE,CAR_MODEL,CAR_NAME,CAR_REMARK,CAR_STATUS,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER) VALUES (");
sql.Append(" '" + item.CAR_CODE.ToUpper() + "',");
sql.Append(" '" + item.CAR_ENGINE.ToUpper() + "',");
sql.Append(" '" + item.CAR_MODEL.ToUpper() + "',");
sql.Append(" '" + item.CAR_NAME.ToUpper() + "',");
sql.Append(" '" + item.CAR_REMARK + "',");
sql.Append(" '" + item.CAR_STATUS.ToUpper() + "',");
sql.Append(" '" + DateTime.Now + "',");
sql.Append(" '" + member.MEMBER_USER + "',");
sql.Append(" '" + DateTime.Now + "',");
sql.Append(" '" + member.MEMBER_USER + "')");
SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con);
cmd.ExecuteNonQuery();
DBbase.DisConnect();
}
catch (SqlException exception)
{
if (exception.Number == 2601) // Cannot insert duplicate key row in object error
{
// handle duplicate key error
return;
}
else
{
throw exception; // throw exception if this exception is unexpected
}
}
catch (Exception ex)
{
throw ex;
}
}
public CheckInsureCarData GetItemAgent(string code)
{
try
{
DBbase.Connect();
string sql = @"SELECT T.SELECT_INSURANCE_CODE,T.SELECT_INSURANCE_STATUS,
T.WINDOW_IP,T.AGENT_CODE,T.TRANSACTION_TYPE,T.INSURE_CAR_CODE,T.REMARK,T.CREATE_DATE,S.sm_name,S.sm_lastname,S.sm_email,S.sm_tel,S.sm_phone
FROM TA_SELECT_INSURANCE T INNER JOIN system_member S ON T.AGENT_CODE = S.sm_code WHERE SELECT_INSURANCE_CODE = '" + code + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
CheckInsureCarData item = new CheckInsureCarData();
item.SELECT_INSURANCE_CODE = reader["SELECT_INSURANCE_CODE"].ToString();
item.INSURE_CAR_CODE = reader["INSURE_CAR_CODE"].ToString();
//item.AGENT_NAME = reader.GetString("sm_name") + " " + reader.GetString("sm_lastname");
//item.AGENT_EMAIL = reader.GetString("sm_email");
//item.AGENT_PHONE = reader.GetString("sm_phone");
//item.AGENT_TEL = reader.GetString("sm_tel");
item.SELECT_INSURANCE_STATUS = reader["SELECT_INSURANCE_STATUS"].ToString();
item.WINDOW_IP = reader["WINDOW_IP"].ToString();
item.AGENT_CODE = reader["AGENT_CODE"].ToString();
item.TRANSACTION_TYPE = reader["TRANSACTION_TYPE"].ToString();
item.REMARK = reader["REMARK"].ToString();
item.CREATE_DATE = Convert.ToDateTime(reader["CREATE_DATE"].ToString());
reader.Close();
DBbase.DisConnect();
return(item);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public CheckInsureCarData GetItem(string code)
{
try
{
DBbase.Connect();
string sql = @"SELECT SELECT_INSURANCE_CODE,CUSTOMER_NAME,CUSTOMER_EMAIL,CUSTOMER_TEL,SELECT_INSURANCE_STATUS,
WINDOW_IP,AGENT_CODE,TRANSACTION_TYPE,INSURE_CAR_CODE,REMARK,CREATE_DATE
FROM TA_SELECT_INSURANCE WHERE SELECT_INSURANCE_CODE = '" + code + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
CheckInsureCarData item = new CheckInsureCarData();
item.SELECT_INSURANCE_CODE = reader["SELECT_INSURANCE_CODE"].ToString();
item.INSURE_CAR_CODE = reader["INSURE_CAR_CODE"].ToString();
item.CUSTOMER_NAME = reader["CUSTOMER_NAME"].ToString();
item.CUSTOMER_EMAIL = reader["CUSTOMER_EMAIL"].ToString();
item.CUSTOMER_TEL = reader["CUSTOMER_TEL"].ToString();
item.SELECT_INSURANCE_STATUS = reader["SELECT_INSURANCE_STATUS"].ToString();
item.WINDOW_IP = reader["WINDOW_IP"].ToString();
item.AGENT_CODE = reader["AGENT_CODE"].ToString();
item.TRANSACTION_TYPE = reader["TRANSACTION_TYPE"].ToString();
item.REMARK = reader["REMARK"].ToString();
item.CREATE_DATE = Convert.ToDateTime(reader["CREATE_DATE"].ToString());
reader.Close();
DBbase.DisConnect();
return(item);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetAll()
{
try
{
DBbase.Connect();
string sql = "SELECT CAR_CODE,CAR_NAME,CAR_MODEL,CAR_ENGINE,CAR_STATUS FROM MA_CAR ORDER BY CAR_CODE";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetComboBoxCarModel(string carName)
{
try
{
DBbase.Connect();
string sql = "SELECT DISTINCT CAR_MODEL FROM MA_CAR WHERE CAR_STATUS = 'A' AND CAR_NAME = '" + carName + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetAllMember()
{
try
{
DBbase.Connect();
string sql = "SELECT MEMBER_NAME,MEMBER_SURENAME,MEMBER_USER,MEMBER_STATUS,ROLE_CODE FROM MA_MEMBER ORDER BY MEMBER_USER";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
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 DataTable GetComboBoxCompanyName()
{
try
{
DBbase.Connect();
string sql = "SELECT COMPANY_FULLNAME FROM MA_INSURE_COMPANY ORDER BY COMPANY_CODE";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetComboBoxCarYear()
{
try
{
DBbase.Connect();
string sql = "SELECT DISTINCT CAR_YEAR FROM MA_INSURE_CAR WHERE INSURE_CAR_STATUS = 'A'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetComboBoxCarEngine(string carYear, string carName, string carModel)
{
try
{
DBbase.Connect();
string sql = "SELECT DISTINCT C.CAR_ENGINE FROM MA_INSURE_CAR I INNER JOIN MA_CAR C ON I.CAR_ID = C.CAR_ID WHERE INSURE_CAR_STATUS = 'A' AND CAR_YEAR ='" + carYear + "' AND CAR_NAME = '" + carName + "' AND CAR_MODEL = '" + carModel + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataTable dataTable = new DataTable();
ds.Tables.Add(dataTable);
ds.EnforceConstraints = false;
dataTable.Load(reader);
reader.Close();
DBbase.DisConnect();
return(dataTable);
}
catch (Exception ex)
{
throw ex;
}
}
public bool CheckItem(InsureCarData item)
{
try
{
DBbase.Connect();
string sql = @"SELECT A.INSURE_CAR_CODE, A.COMPANY_CODE, A.PACKAGE_NAME, A.CAR_ID,
A.INSURE_CATEGORY, A.INSURE_TYPE_REPAIR, A.CAR_YEAR, A.LIVE_COVERAGE_PEOPLE,
A.LIVE_COVERAGE_TIME, A.ASSET_TIME, A.DAMAGE_TO_VEHICLE,
A.MISSING_FIRE_CAR, A.FIRST_DAMAGE_PRICE, A.PERSONAL_ACCIDENT_AMT,
A.PERSONAL_ACCIDENT_PEOPLE, A.MEDICAL_FEE_AMT, A.MEDICAL_FEE_PEOPLE,
A.DRIVER_INSURANCE_AMT, A.NET_PRICE, A.TOTAL_PRICE, A.PRICE_ROUND,
A.CAPITAL_INSURANCE, A.INSURE_PRIORITY, A.EFFECTIVE_DATE, A.EXPIRE_DATE,
A.CONFIDENTIAL_STATUS, A.CREATE_DATE, A.CREATE_USER, A.UPDATE_DATE,
A.UPDATE_USER, A.INSURE_CAR_STATUS, C.CAR_CODE,C.CAR_NAME,C.CAR_MODEL,C.CAR_ENGINE ,I.COMPANY_FULLNAME
FROM MA_INSURE_CAR A INNER JOIN MA_CAR C ON A.CAR_ID = C.CAR_ID INNER JOIN MA_INSURE_COMPANY I ON A.COMPANY_CODE = I.COMPANY_CODE
WHERE I.COMPANY_CODE = '" + item.COMPANY_CODE + "' AND A.PACKAGE_NAME = '" + item.PACKAGE_NAME + "' AND A.CAR_ID = '" + item.CAR_ID
+ "' AND A.INSURE_CATEGORY = '" + item.INSURE_CATEGORY + "' AND A.INSURE_TYPE_REPAIR = '"
+ item.INSURE_TYPE_REPAIR + "' AND A.CAR_YEAR = '" + item.CAR_YEAR + "'"
+ "AND A.CAPITAL_INSURANCE = '" + item.CAPITAL_INSURANCE + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
DBbase.DisConnect();
return(true);
}
else
{
DBbase.DisConnect();
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool SingOn(string user, string pass)
{
try
{
DBbase.Connect();
string sql = "SELECT * FROM MA_MEMBER WHERE MEMBER_USER = '******' AND MEMBER_PASSWORD = '******'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
DBbase.DisConnect();
return(true);
}
else
{
DBbase.DisConnect();
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool CheckItem(string code)
{
try
{
DBbase.Connect();
string sql = "SELECT COMPANY_CODE FROM MA_INSURE_COMPANY WHERE COMPANY_CODE = '" + code + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
DBbase.DisConnect();
return(true);
}
else
{
DBbase.DisConnect();
return(false);
}
}
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;
}
}
public InsureCarData GetItem(string code)
{
try
{
DBbase.Connect();
string sql = @"SELECT A.INSURE_CAR_CODE, A.COMPANY_CODE, A.PACKAGE_NAME, A.CAR_ID,
A.INSURE_CATEGORY, A.INSURE_TYPE_REPAIR, A.CAR_YEAR, A.LIVE_COVERAGE_PEOPLE,
A.LIVE_COVERAGE_TIME, A.ASSET_TIME, A.DAMAGE_TO_VEHICLE,
A.MISSING_FIRE_CAR, A.FIRST_DAMAGE_PRICE, A.PERSONAL_ACCIDENT_AMT,
A.PERSONAL_ACCIDENT_PEOPLE, A.MEDICAL_FEE_AMT, A.MEDICAL_FEE_PEOPLE,
A.DRIVER_INSURANCE_AMT, A.NET_PRICE, A.TOTAL_PRICE, A.PRICE_ROUND,
A.CAPITAL_INSURANCE, A.INSURE_PRIORITY, A.EFFECTIVE_DATE, A.EXPIRE_DATE,
A.CONFIDENTIAL_STATUS, A.CREATE_DATE, A.CREATE_USER, A.UPDATE_DATE,
A.UPDATE_USER, A.INSURE_CAR_STATUS, C.CAR_CODE,C.CAR_NAME,C.CAR_MODEL,C.CAR_ENGINE ,I.COMPANY_FULLNAME
FROM MA_INSURE_CAR A INNER JOIN MA_CAR C ON A.CAR_ID = C.CAR_ID INNER JOIN MA_INSURE_COMPANY I ON A.COMPANY_CODE = I.COMPANY_CODE
WHERE INSURE_CAR_CODE = '" + code + "'";
SqlCommand cmd = new SqlCommand(sql, DBbase.con);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
InsureCarData item = new InsureCarData();
item.ASSET_TIME = Convert.ToDecimal(reader["ASSET_TIME"].ToString());
item.CAPITAL_INSURANCE = Convert.ToDecimal(reader["CAPITAL_INSURANCE"].ToString());
item.CAR_ID = Convert.ToInt32(reader["CAR_ID"].ToString());
item.CAR_CODE = reader["CAR_CODE"].ToString();
item.CAR_MODEL = reader["CAR_MODEL"].ToString();
item.CAR_NAME = reader["CAR_NAME"].ToString();
item.CAR_ENGINE = reader["CAR_ENGINE"].ToString();
item.CAR_YEAR = reader["CAR_YEAR"].ToString();
item.COMPANY_CODE = reader["COMPANY_CODE"].ToString();
item.COMPANY_FULLNAME = reader["COMPANY_FULLNAME"].ToString();
item.CONFIDENTIAL_STATUS = reader["CONFIDENTIAL_STATUS"].ToString();
item.DAMAGE_TO_VEHICLE = Convert.ToDecimal(reader["DAMAGE_TO_VEHICLE"].ToString());
item.DRIVER_INSURANCE_AMT = Convert.ToDecimal(reader["DRIVER_INSURANCE_AMT"].ToString());
item.EFFECTIVE_DATE = Convert.ToDateTime(reader["EFFECTIVE_DATE"].ToString());
item.EXPIRE_DATE = Convert.ToDateTime(reader["EXPIRE_DATE"].ToString());
item.FIRST_DAMAGE_PRICE = Convert.ToDecimal(reader["FIRST_DAMAGE_PRICE"].ToString());
item.INSURE_CAR_CODE = reader["INSURE_CAR_CODE"].ToString();
item.INSURE_CAR_STATUS = reader["INSURE_CAR_STATUS"].ToString();
item.INSURE_CATEGORY = reader["INSURE_CATEGORY"].ToString();
item.INSURE_TYPE_REPAIR = reader["INSURE_TYPE_REPAIR"].ToString();
item.LIVE_COVERAGE_PEOPLE = Convert.ToDecimal(reader["LIVE_COVERAGE_PEOPLE"].ToString());
item.LIVE_COVERAGE_TIME = Convert.ToDecimal(reader["LIVE_COVERAGE_TIME"].ToString());
item.MEDICAL_FEE_AMT = Convert.ToDecimal(reader["MEDICAL_FEE_AMT"].ToString());
item.MEDICAL_FEE_PEOPLE = Convert.ToInt32(reader["MEDICAL_FEE_PEOPLE"].ToString());
item.MISSING_FIRE_CAR = Convert.ToDecimal(reader["MISSING_FIRE_CAR"].ToString());
item.NET_PRICE = Convert.ToDecimal(reader["NET_PRICE"].ToString());
item.PACKAGE_NAME = reader["PACKAGE_NAME"].ToString();
item.PERSONAL_ACCIDENT_AMT = Convert.ToDecimal(reader["PERSONAL_ACCIDENT_AMT"].ToString());
item.PERSONAL_ACCIDENT_PEOPLE = Convert.ToInt32(reader["PERSONAL_ACCIDENT_PEOPLE"].ToString());
item.PRICE_ROUND = Convert.ToDecimal(reader["PRICE_ROUND"].ToString());
item.TOTAL_PRICE = Convert.ToDecimal(reader["TOTAL_PRICE"].ToString());
item.COMPANY_CODE = reader["COMPANY_CODE"].ToString();
item.INSURE_PRIORITY = Convert.ToInt32(reader["INSURE_PRIORITY"].ToString());
reader.Close();
DBbase.DisConnect();
return(item);
}
else
{
DBbase.DisConnect();
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
