/// <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 ToVasProduct()
{
//Create Excel
xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = null;
//Create an OracleCommand object using the connection object
OracleCommand command = ConnectionProd.CreateCommand();
OracleTransaction transaction = null;
try
{
//Read data from file input
xlWorkbook = xlApp.Workbooks.Open(filename);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets["New VAS code (VCare&CCBS)"];
Excel.Range xlRange = xlWorksheet.UsedRange;
for (int index = 4; index <= xlRange.Rows.Count; index++)
{
string rangeCode = "B" + index;
string vas_code = xlWorksheet.Range[rangeCode].Value2;
string vas_name;
string vas_type;
string vas_channel;
string vas_rule;
string vas_price;
string vas_parent;
string vas_start;
string vas_user = user.Trim();
string vas_end = "";
if (vas_code != null)
{
vas_name = xlWorksheet.Range["C" + index].Value2.Trim();
vas_type = xlWorksheet.Range["D" + index].Value2.Trim();
vas_rule = xlWorksheet.Range["E" + index].Value2.Trim();
vas_price = xlWorksheet.Range["F" + index].Value2.ToString().Trim();
vas_channel = xlWorksheet.Range["G" + index].Value2.Trim();
vas_parent = xlWorksheet.Range["H" + index].Value2.Trim();
vas_start = xlWorksheet.Range["I" + index].Value2.ToString().Trim();
if (vas_start != "-" || vas_start != "")
{
vas_start = changeFormat.formatDate(vas_start);
}
else
{
vas_start = "";
}
string query = "SELECT VAS_CODE FROM VAS_PRODUCT WHERE VAS_CODE = '" + vas_code + "' AND VAS_TYPE = '" +
vas_type + "' AND VAS_CHANNEL = '" + vas_channel + "'";
OracleCommand cmd = new OracleCommand(query, ConnectionProd);
OracleDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
//write log
log += "VasCode already exists! : " + vas_code + "\r\n";
}
else
{
using (transaction = ConnectionProd.BeginTransaction())
{
try
{
string cmdTxt = "INSERT INTO VAS_PRODUCT VALUES('" + vas_code + "','" + vas_name + "','" + vas_type + "','Active','" +
vas_rule + "','" + vas_price + "', null, '" + vas_channel + "', null, '" + vas_parent + "',TO_DATE('" + vas_start + "','dd/mm/yyyy')," +
" TO_DATE('" + vas_end + "','dd/mm/yyyy'), sysdate,'" + vas_user + "',null,null)";
command = new OracleCommand(cmdTxt, ConnectionProd, transaction);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
reader.Close();
}
}
}
catch (NullReferenceException nullRef)
{ }
finally
{
xlApp.Quit();
}
}