/// <summary>
/// Main process for New discount criteria
/// </summary>
/// <param name="minID"></param>
private void ExecuteNewDC(int minID)
{
CompleteFlag = true;
string strMinID = "DC" + string.Format("{0:00000000}", minID);
//create table view data from table mapping
DataTable dtTableView = new DataTable();
dtTableView.Clear();
dtTableView.Columns.Add("DC_ID");
dtTableView.Columns.Add("DC_TYPE");
dtTableView.Columns.Add("DC_VALUE");
dtTableView.Columns.Add("DC_GROUPID");
dtTableView.Columns.Add("DC_START_DT");
dtTableView.Columns.Add("DC_END_DT");
dtTableView.Columns.Add("DC_ACTIVE_FLAG");
Dictionary <string, string> dicGroupID = null;
string existID = "";
for (int i = 0; i < dataGridView1.RowCount; i++)
{
bool hasID = false;
dcCode = dataGridView1.Rows[i].Cells[0].Value.ToString().Trim();
month = dataGridView1.Rows[i].Cells[1].Value.ToString();
channel = dataGridView1.Rows[i].Cells[2].Value.ToString();
mkt = dataGridView1.Rows[i].Cells[3].Value.ToString();
string upMkt = mkt.ToUpper();
if (upMkt.Equals("ALL"))
{
mkt = "ALL";
}
else
{
string[] lstMkt = mkt.Split('-');
mkt = lstMkt[0].Trim();
}
orderType = dataGridView1.Rows[i].Cells[4].Value.ToString();
string getSpeed = dataGridView1.Rows[i].Cells[6].Value.ToString();
province = dataGridView1.Rows[i].Cells[7].Value.ToString().ToUpper();
effective = dataGridView1.Rows[i].Cells[8].Value.ToString();
expire = dataGridView1.Rows[i].Cells[9].Value.ToString();
if (expire.Equals("-"))
{
expire = String.Empty;
}
List <Dictionary <string, string> > lstRangeM = validate.FormatGroupMonth(month);
Dictionary <string, string> groupMonth = new Dictionary <string, string>();
List <string> lstChannel = new List <string>();
string[] lstorder = null;
string[] lstprov = null;
//Cut each Order_Type
if (orderType.Contains(","))
{
lstorder = orderType.Split(',');
}
else
{
lstorder = new string[] { orderType };
string upper = lstorder[0].ToUpper();
if (upper.StartsWith("ALL"))
{
lstorder[0] = "ALL";
}
}
//Cut each Channel
if (channel.Contains(","))
{
string[] lstCh = channel.Split(',');
List <string> distinct = lstCh.Distinct().ToList();
lstChannel = distinct;
}
else
{
lstChannel = new List <string>()
{
channel
};
string upper = lstChannel[0].ToUpper();
if (upper.Equals("ALL") || upper.Equals("DEFAULT"))
{
lstChannel[0] = "ALL";
}
}
//Speed
speed = changeFormat.formatSpeed(getSpeed);
//Cut each Province
if (province.Contains(","))
{
lstprov = province.Split(',');
}
else
{
lstprov = new string[] { province };
}
//Change format Date
effective = changeFormat.formatDate(effective);
if (String.IsNullOrEmpty(expire))
{
expire = "";
}
else
{
expire = changeFormat.formatDate(expire);
}
for (int num = 0; num < lstRangeM.Count; num++)
{
groupMonth = lstRangeM[num];
string minMonth = groupMonth["min" + num];
string maxMonth = groupMonth["max" + num];
string GroupName = dcCode + minMonth + maxMonth;
dcGroupID = dicGroupID[GroupName];
for (int j = 0; j < lstChannel.Count; j++)
{
for (int k = 0; k < lstorder.Length; k++)
{
for (int p = 0; p < lstprov.Length; p++)
{
string ch = lstChannel[j].Trim();
string ord = lstorder[k].Trim();
string prov = lstprov[p].Trim();
string strQuery = "SELECT * FROM (SELECT * FROM (SELECT DC_ID, DG_DISCOUNT DISCOUNT_CODE, DG_DISCOUNT_DESC DISCOUNT_DESCRIPTION,DG_MONTH_MIN MONTH_MIN" +
", DG_MONTH_MAX MONTH_MAX, to_char(trunc(DC_START_DT), 'dd/mm/yyyy') START_DATE ,to_char(trunc(DC_END_DT), 'dd/mm/yyyy') END_DATE" +
", DG_ACTIVE_FLAG ACITVE_FLAG, SALE_CHANNEL, SPEED, MARKETING_CODE, ORDER_TYPE, PROVINCE, PRODUCT, DG_GROUPID, DG_ORDER_BY, DC_GROUPID " +
"FROM(SELECT * FROM(" +
"SELECT DC1.DC_START_DT, DC1.DC_END_DT, DC1.DC_ID, DC1.DC_GROUPID, NVL(PRODUCT, 'ALL') PRODUCT, NVL(SPEED, 'ALL') SPEED, NVL(MARKETING_CODE, 'ALL')MARKETING_CODE," +
"NVL(PROVINCE, 'ALL')PROVINCE, NVL(SALE_CHANNEL, 'ALL')SALE_CHANNEL, NVL(ORDER_TYPE, 'ALL')ORDER_TYPE FROM(" +
"SELECT DC_START_DT, DC_END_DT, DC_ID, DC_GROUPID, DECODE(DC_TYPE, 'PRODUCT', DC_VALUE, 'ALL') PRODUCT FROM DISCOUNT_CRITERIA_MAPPING" +
" WHERE DC_TYPE = 'PRODUCT' AND DC_ACTIVE_FLAG = 'Y') DC1,(SELECT DC_START_DT, DC_END_DT, DC_ID, DC_GROUPID, DECODE(DC_TYPE, 'SPEED', DC_VALUE, 'ALL') SPEED " +
"FROM DISCOUNT_CRITERIA_MAPPING WHERE DC_TYPE = 'SPEED' AND DC_ACTIVE_FLAG = 'Y') DC2,(" +
"SELECT DC_START_DT, DC_END_DT, DC_ID, DC_GROUPID, DECODE(DC_TYPE, 'MARKETING_CODE', DC_VALUE, 'ALL') MARKETING_CODE FROM DISCOUNT_CRITERIA_MAPPING " +
"WHERE DC_TYPE = 'MARKETING_CODE' AND DC_ACTIVE_FLAG = 'Y') DC3, (" +
"SELECT DC_START_DT, DC_END_DT, DC_ID, DC_GROUPID, DECODE(DC_TYPE, 'PROVINCE', DC_VALUE, 'ALL') PROVINCE " +
"FROM DISCOUNT_CRITERIA_MAPPING WHERE DC_TYPE = 'PROVINCE' AND DC_ACTIVE_FLAG = 'Y' ) DC4, (" +
"SELECT DC_START_DT, DC_END_DT, DC_ID, DC_GROUPID, DECODE(DC_TYPE, 'SALE_CHANNEL', DC_VALUE, 'ALL') SALE_CHANNEL " +
"FROM DISCOUNT_CRITERIA_MAPPING " +
"WHERE DC_TYPE = 'SALE_CHANNEL' AND DC_ACTIVE_FLAG = 'Y' ) DC5, (" +
"SELECT DC_START_DT, DC_END_DT, DC_ID, DC_GROUPID, DECODE(DC_TYPE, 'ORDER_TYPE', DC_VALUE, 'ALL') ORDER_TYPE " +
"FROM DISCOUNT_CRITERIA_MAPPING " +
"WHERE DC_TYPE = 'ORDER_TYPE' AND DC_ACTIVE_FLAG = 'Y' ) DC6" +
" WHERE DC1.DC_ID = DC2.DC_ID(+) " +
"AND DC1.DC_ID = DC3.DC_ID(+) " +
"AND DC1.DC_ID = DC4.DC_ID(+) " +
"AND DC1.DC_ID = DC5.DC_ID(+) " +
"AND DC1.DC_ID = DC6.DC_ID(+) " +
") ) DISCOUNT, DISCOUNT_CRITERIA_GROUP WHERE DISCOUNT.DC_GROUPID = DISCOUNT_CRITERIA_GROUP.DG_GROUPID AND DISCOUNT_CRITERIA_GROUP.DG_ACTIVE_FLAG = 'Y' " +
" ORDER BY DG_ORDER_BY,DG_MONTH_MIN , DG_DISCOUNT,DC_START_DT)) WHERE DG_GROUPID ='" + dcGroupID + "' AND SALE_CHANNEL = '" +
ch + "' AND MARKETING_CODE = '" + mkt + "' AND ORDER_TYPE = '" + ord + "' AND SPEED = '" + speed + "' AND PROVINCE = '" + prov + "'";
OracleCommand cmd = new OracleCommand(strQuery, ConnectionProd);
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
hasID = true;
string idEx = reader["DC_ID"].ToString();
existID += "DC_ID : " + idEx + ", DC Code : " + dcCode + ", MKT : " + mkt + ", Order : " + ord + ", Month " +
minMonth + ":" + maxMonth + ", Channel : " + ch + ", Speed : " + speed + ", Province : " + prov + "\r\n";
}
else
{
hasID = false;
}
for (int m = 1; m <= 6; m++)
{
object[] obj = new object[7];
if (hasID == false)
{
obj[0] = "DC" + string.Format("{0:00000000}", minID);
}
else
{
obj[0] = "";
}
obj[3] = dcGroupID;
obj[4] = effective;
if (String.IsNullOrEmpty(expire))
{
obj[5] = "";
}
else
{
obj[5] = expire;
}
obj[6] = "Y";
switch (m)
{
case 1:
obj[1] = "MARKETING_CODE";
obj[2] = mkt;
break;
case 2:
obj[1] = "ORDER_TYPE";
obj[2] = ord;
break;
case 3:
obj[1] = "PRODUCT";
obj[2] = "ALL";
break;
case 4:
obj[1] = "PROVINCE";
obj[2] = prov;
break;
case 5:
obj[1] = "SALE_CHANNEL";
obj[2] = ch;
break;
case 6:
obj[1] = "SPEED";
obj[2] = speed;
break;
}
dtTableView.Rows.Add(obj);
}
if (hasID == false)
{
minID += 1;
}
}
}
}
}
}
minID = minID - 1;
string max_id = "DC" + string.Format("{0:00000000}", minID);
if (FLAG_NEW == true && FLAG_EXISTING == true)
{
dataTableNew = dtTableView;
}
else
{
if (existID != "")
{
log += "Already exists data in database" + "\r\n" + existID + "\r\n";
}
DataTable[] lstTable = new DataTable[2];
lstTable[0] = dtTableView;
viewResult = new ViewResult(this, lstTable, ConnectionProd, ConnectionTemp, urNo, strMinID,
max_id, user, log, outputPath);
viewResult.ShowDialog();
}
}
private void Execute(int minId)
{
//create table view data from table mapping
DataTable dtTableView = new DataTable();
dtTableView.Clear();
dtTableView.Columns.Add("DC_ID");
dtTableView.Columns.Add("DC_TYPE");
dtTableView.Columns.Add("DC_VALUE");
dtTableView.Columns.Add("DC_GROUPID");
dtTableView.Columns.Add("DC_START_DT");
dtTableView.Columns.Add("DC_END_DT");
dtTableView.Columns.Add("DC_ACTIVE_FLAG");
string vas_code;
string channel;
string mkt;
string speed;
string product;
string orderType;
string effective;
string expire;
string province;
string strMinID = "VAS" + string.Format("{0:0000000}", minId);
string existID = "";
try
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
bool hasID = false;
vas_code = dataGridView1.Rows[i].Cells[0].Value.ToString().Trim();
channel = dataGridView1.Rows[i].Cells[1].Value.ToString();
mkt = dataGridView1.Rows[i].Cells[2].Value.ToString();
string upMkt = mkt.ToUpper();
if (upMkt.Equals("ALL"))
{
mkt = "ALL";
}
else
{
string[] lstMkt = mkt.Split('-');
mkt = lstMkt[0].Trim();
}
orderType = dataGridView1.Rows[i].Cells[3].Value.ToString();
product = dataGridView1.Rows[i].Cells[4].Value.ToString();
string getSpeed = dataGridView1.Rows[i].Cells[5].Value.ToString();
province = dataGridView1.Rows[i].Cells[6].Value.ToString();
effective = dataGridView1.Rows[i].Cells[7].Value.ToString();
expire = dataGridView1.Rows[i].Cells[8].Value.ToString();
if (expire.Equals("-"))
{
expire = String.Empty;
}
string[] lstChannel = null;
string[] lstorder = null;
//Cut each Order_Type
if (orderType.Contains(","))
{
lstorder = orderType.Split(',');
}
else
{
lstorder = new string[] { orderType };
string upper = lstorder[0].ToUpper().Trim();
if (upper.Equals("ALL"))
{
lstorder[0] = "ALL";
}
}
//Cut each Channel
if (channel.Contains(","))
{
lstChannel = channel.Split(',');
}
else
{
lstChannel = new string[] { channel };
string upper = lstChannel[0].ToUpper().Trim();
if (upper.Equals("ALL") || upper.Equals("DEFAULT"))
{
lstChannel[0] = "ALL";
}
}
//Speed
speed = changeFormat.formatSpeed(getSpeed);
//Change format Date
effective = changeFormat.formatDate(effective);
if (String.IsNullOrEmpty(expire))
{
expire = "";
}
else
{
expire = changeFormat.formatDate(expire);
}
for (int j = 0; j < lstChannel.Length; j++)
{
for (int k = 0; k < lstorder.Length; k++)
{
string ch = lstChannel[j].Trim();
string ord = lstorder[k].Trim();
string strQuery = "SELECT * FROM DISCOUNT_CRITERIA_MAPPING WHERE DC_ID IN (SELECT DC_ID FROM DISCOUNT_CRITERIA_MAPPING " +
"WHERE DC_VALUE = '" + mkt + "' AND DC_GROUPID = '" + vas_code + "' AND DC_ID IN (SELECT DC_ID FROM DISCOUNT_CRITERIA_MAPPING " +
"WHERE DC_VALUE = '" + ch + "') AND DC_ID IN (SELECT DC_ID FROM DISCOUNT_CRITERIA_MAPPING WHERE DC_VALUE = '" + speed +
"') AND DC_ID IN (SELECT DC_ID FROM DISCOUNT_CRITERIA_MAPPING WHERE DC_VALUE = '" + ord + "'))";
OracleCommand cmd = new OracleCommand(strQuery, ConnectionProd);
OracleDataReader reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
hasID = true;
string idEx = reader["DC_ID"].ToString();
existID += "ID : " + idEx + ", code : " + vas_code + ", MKT : " + mkt + ", Order : " + ord +
", Channel : " + ch + ", Speed : " + speed + "\r\n";
}
else
{
hasID = false;
if (lstChannel[0].Equals("ALL"))
{
UPDATECHANNEL(mkt, vas_code, speed, effective);
}
}
for (int m = 1; m <= 6; m++)
{
object[] obj = new object[7];
if (hasID == false)
{
obj[0] = "VAS" + string.Format("{0:0000000}", minId);
}
else
{
obj[0] = "";
}
obj[3] = vas_code;
obj[4] = effective;
if (String.IsNullOrEmpty(expire))
{
obj[5] = "";
}
else
{
obj[5] = expire;
}
obj[6] = "Y";
switch (m)
{
case 1:
obj[1] = "MARKETING_CODE";
obj[2] = mkt;
break;
case 2:
obj[1] = "ORDER_TYPE";
obj[2] = ord;
break;
case 3:
obj[1] = "PRODUCT";
obj[2] = product;
break;
case 4:
obj[1] = "PROVINCE";
obj[2] = province;
break;
case 5:
obj[1] = "SALE_CHANNEL";
obj[2] = ch;
break;
case 6:
obj[1] = "SPEED";
obj[2] = speed;
break;
}
dtTableView.Rows.Add(obj);
}
if (hasID == false)
{
minId += 1;
}
}
}
}
minId = minId - 1;
string max_id = "VAS" + string.Format("{0:0000000}", minId);
if (existID != "")
{
log += "\r\n" + "Already exists data in database" + "\r\n" + existID + "\r\n";
}
DataTable[] lstTable = new DataTable[2];
lstTable[0] = dtTableView;
ViewResult viewResult = new ViewResult(this, lstTable, ConnectionProd, ConnectionTemp, urNo, strMinID,
max_id, user, "", outputPath);
viewResult.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}