public static DataRow validateCards(String card_type, String card_no, String code_check = "")
{
try
{
string original_card_type = card_type;
GF.doDebug("VALIDATE CARD : " + original_card_type);
if (card_type == "MONEY_COUPON")
{
card_type = "COUPON";
}
String queryString = "SELECT " + card_type + @".*, ";
if (card_type == "MEMBERCARD" || card_type == "VIP_CARD")
{
queryString += "NULL PROGRAM_NAME, NULL PROGRAM_CODE, ";
}
else
{
queryString += "SPA_PROGRAM.PROGRAM_NAME PROGRAM_NAME, SPA_PROGRAM.CODE PROGRAM_CODE, ";
}
if (card_type == "MEMBERCARD")
{
queryString += "MEMBERCARD_TYPE.DISCOUNT DISCOUNT, 0 DISCOUNT_UNIT, ";
}
else if (card_type == "VIP_CARD")
{
queryString += "VIP_CARD.DISCOUNT DISCOUNT, 0 DISCOUNT_UNIT, ";
}
else if (card_type == "CROSS_PROMOTION")
{
queryString += "CROSS_PROMOTION.DISCOUNT DISCOUNT, 0 DISCOUNT_UNIT, ";
}
else if (card_type == "COUPON")
{
queryString += "COUPON.DISCOUNT_AMOUNT DISCOUNT, COUPON.DISCOUNT_UNIT, ";
}
else
{
queryString += "NULL DISCOUNT, NULL DISCOUNT_UNIT, ";
}
if (card_type == "MEMBERCARD")
{
queryString += "MEMBERCARD_TYPE.DISCOUNT_FOOD, MEMBERCARD_TYPE.DISCOUNT_FOOD_UNIT, ";
}
else
{
queryString += itemDiscount.ToString() + " DISCOUNT_FOOD, 0 DISCOUNT_FOOD_UNIT, ";
}
if (card_type != "MEMBERCARD" && card_type != "GIFT_CERTIFICATE" && card_type != "COUPON")
{
queryString += "NULL BALANCE, ";
}
queryString = queryString.Substring(0, queryString.Length - 2) + " ";
queryString += "FROM " + card_type + " ";
if (card_type == "MEMBERCARD")
{
queryString += "INNER JOIN MEMBERCARD_TYPE ON MEMBERCARD.MEMBERCARD_TYPE_ID = MEMBERCARD_TYPE.MEMBERCARD_TYPE_ID ";
}
else if (card_type != "VIP_CARD")
{
queryString += "LEFT OUTER JOIN SPA_PROGRAM ON " + card_type + @".SPA_PROGRAM_ID = SPA_PROGRAM.SPA_PROGRAM_ID ";
}
queryString += "WHERE RTRIM(LTRIM(CARD_NO)) = '" + card_no + "' AND " + card_type + ".IS_USE = 1 ";
Dictionary <string, string> Params = new Dictionary <string, string>();
//Params.Add("@card_no", card_no);
if (card_type == "VIP_CARD" || card_type == "COUPON")
{
queryString += "AND IS_VOID = 0 ";
}
DataTable DT;
using (DT = DB.getS(queryString, Params, "GET " + original_card_type + "[" + card_no + "]", false))
{
if (DT.Rows.Count == 0)
{
if (card_type != "CROSS_PROMOTION")
{
String error_msg = "NO " + card_type.Replace('_', ' ') + " WITH THIS CODE !!";
if (card_type == "VIP_CARD" || card_type == "COUPON")
{
queryString = @"
SELECT TOP 1 " + card_type + @".*, EMPLOYEE.FULLNAME VOIDER
FROM " + card_type + @"
LEFT OUTER JOIN EMPLOYEE ON " + card_type + @".VOIDED_BY = EMPLOYEE.EMP_ID
WHERE RTRIM(LTRIM(CARD_NO)) = '" + card_no + "' AND " + card_type + ".IS_VOID = 1 ORDER BY " + card_type + ".VOIDED_DATETIME DESC";
DataTable tmpDT;
using (tmpDT = DB.getS(queryString, Params, "GET VOIDED LATEST REASON", false))
{
if (tmpDT.Rows.Count == 1)
{
error_msg = "THIS " + card_type.Replace('_', ' ') + " WAS VOIDED !!\r\nREASON : " + tmpDT.Rows[0]["VOIDED_REASON"].ToString() + "\r\nVOIDED BY : " + (tmpDT.Rows[0]["VOIDED_BY"].ToString() == "0" ? "S.A." : tmpDT.Rows[0]["VOIDER"].ToString()) + "\r\nVOIDED ON : " + GF.formatDateTime(tmpDT.Rows[0]["VOIDED_DATETIME"].ToString());
}
}
}
GF.doDebug(error_msg);
MessageBox.Show(error_msg, "ERROR");
}
else
{
GF.doDebug("NOT FOUND !!");
MessageBox.Show("NOT FOUND !!", "ERROR");
}
GF.closeLoading();
return(null);
}
else if (DT.Rows.Count > 1)
{
GF.doDebug("DUPLICATE CARDS WITH THIS CODE !!");
MessageBox.Show("DUPLICATE CARDS WITH THIS CODE !!", "ERROR");
GF.closeLoading();
return(null);
}
else if (DT.Rows.Count == 1)
{
String expireColName = "";
if (card_type == "MEMBERCARD")
{
expireColName = "EXPIRE_DATE";
}
else
{
expireColName = "EXPIRY_DATE";
}
if (DT.Rows[0][expireColName].ToString() != "NULL" && DT.Rows[0][expireColName].ToString() != "")
{
DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
dtfi.ShortDatePattern = "d" + GF.dateSep + "M" + GF.dateSep + "yyyy";
dtfi.DateSeparator = GF.dateSep;
DateTime today = Convert.ToDateTime(DateTime.Today.ToString("d" + GF.dateSep + "M" + GF.dateSep + "yyyy"), dtfi);
DateTime expire = Convert.ToDateTime(DT.Rows[0][expireColName].ToString().Split(' ')[0], dtfi);
if (DateTime.Compare(today, expire) > 0)
{
GF.doDebug("THIS CARD IS EXPIRED !!");
MessageBox.Show("THIS CARD IS EXPIRED !!", "ERROR");
GF.closeLoading();
return(null);
}
}
if (DT.Rows[0]["IS_USE"].ToString() == "0")
{
GF.doDebug("THIS " + card_type.Replace('_', ' ') + " WAS ALREADY USED OR MIGHT BE VOIDED !!");
MessageBox.Show("THIS " + card_type.Replace('_', ' ') + " WAS ALREADY USED OR MIGHT BE VOIDED !!", "ERROR");
GF.closeLoading();
return(null);
}
if (card_type == "MEMBERCARD" || card_type == "GIFT_CERTIFICATE" || card_type == "COUPON")
{
if (DT.Rows[0]["BALANCE"].ToString() != "" && DT.Rows[0]["BALANCE"].ToString() != "NULL")
{
if (card_type == "MEMBERCARD")
{
queryString = "SELECT * FROM MEMBERCARD_TYPE WHERE MEMBERCARD_TYPE_ID = " + DT.Rows[0]["MEMBERCARD_TYPE_ID"].ToString();
DataTable tmpDT;
using (tmpDT = DB.getS(queryString, null, "CHECK IF MEMBERCARD CAN USE AFTER NO CREDIT ?", false))
{
if (Convert.ToInt32(tmpDT.Rows[0]["CAN_USE_NO_CREDIT"].ToString()) == 0 && Convert.ToInt32(DT.Rows[0]["BALANCE"].ToString()) == 0)
{
GF.doDebug("NO CREDIT LEFT !!");
MessageBox.Show("NO CREDIT LEFT !!", "ERROR");
GF.closeLoading();
return(null);
}
else
{
GF.doDebug("CREDIT LEFT : " + DT.Rows[0]["BALANCE"].ToString() + " " + Properties.Settings.Default.money_unit);
MessageBox.Show("CREDIT LEFT : " + DT.Rows[0]["BALANCE"].ToString() + " " + Properties.Settings.Default.money_unit);
GF.closeLoading();
}
}
}
else
{
int balance_left = 0;
if (Int32.TryParse(DT.Rows[0]["BALANCE"].ToString(), out balance_left))
{
if (balance_left == 0)
{
GF.doDebug("NO CREDIT LEFT !!");
MessageBox.Show("NO CREDIT LEFT !!", "ERROR");
GF.closeLoading();
return(null);
}
}
else
{
if (original_card_type == "MONEY_COUPON")
{
GF.closeLoading();
GF.doDebug("THIS IS NOT 'MONEY COUPON' !!\r\n\r\nPLEASE MAKE SURE YOU CLICKED THE CORRECT COUPON TYPE !!");
MessageBox.Show("THIS IS NOT 'MONEY COUPON' !!\r\n\r\nPLEASE MAKE SURE YOU CLICKED THE CORRECT COUPON TYPE !!", "ERROR");
return(null);
}
}
}
}
}
GF.doDebug("CARD IS OK !!");
return(DT.Rows[0]);
}
else
{
return(null);
}
}
}
catch (Exception e)
{
GF.closeLoading();
GF.doDebug("CARD VALIDATION ERROR !!\r\n\r\nPLEASE CONTACT SOFTWARE DEVELOPER !!\r\n\r\n" + e.Message);
MessageBox.Show("CARD VALIDATION ERROR !!\r\n\r\nPLEASE CONTACT SOFTWARE DEVELOPER !!\r\n\r\n" + e.Message, "ERROR !!");
return(null);
}
}