public static DataTable ValuesToTable(IList <IList <Object> > values)
{
// Get and checking myConnectionString.
string myConnectionString = DB.AccessStringFromFile();
DataTable tableSQL = CreateTable(myConnectionString);
DataRow rowSQL;
string password = "******";
byte[] aesKey = AesCrypt.GetAesKey(password);
byte[] aesIV = AesCrypt.GetAesIV();
string day = "";
string month = "";
string year = "";
string insta = "";
string phone = "";
string requestDate = "";
// rowSheet -> rowSQL
foreach (var rowSheet in values)
{
rowSQL = tableSQL.NewRow(); // Add new empty row.
// ** Get cells as is **
rowSQL["status"] = $"{rowSheet[0]}"; // Column 0
rowSQL["photographer"] = rowSheet[1].ToString().ToUpper(); // Column 1
rowSQL["package1"] = rowSheet[2].ToString().ToUpper(); // Column 2
rowSQL["package2"] = rowSheet[3].ToString().ToUpper(); // Column 3
rowSQL["name_client"] = rowSheet[5]; // Column 5
rowSQL["itinerary"] = rowSheet[11]; // Column 11
rowSQL["year"] = rowSheet[12]; // Column 12
rowSQL["month"] = rowSheet[13]; // Column 13
rowSQL["city"] = rowSheet[15]; // Column 15
// ** Get and change cells only if exists **
// Column 4: Formatting [shoot_date]
// to YYYY-MM-DD (use columns: 4, 12, 13).
if ((string)rowSheet[4] != "")
{
day = String.Format("{0:d2}", Convert.ToInt32(rowSheet[4].ToString()));
month = String.Format("{0:d2}", Convert.ToInt32(rowSheet[13].ToString()));
year = $"{rowSheet[12]}";
rowSQL["shoot_date"] = $"{year}-{month}-{day}";
}
// Column 6: Encrypt [email].
if ($"{rowSheet[6]}" != "")
{
rowSQL["email"] = AesCrypt.EncryptStringToBytes_Aes($"{rowSheet[6]}", aesKey, aesIV);
}
// Column 7: Formatting and encrypt [phone] number.
if ((string)rowSheet[7] != "")
{
phone = "+" + String.Format("{0:+###########}", rowSheet[7]).Replace("+", "").Replace("-", "").Replace("(", "").Replace(")", "").Replace(" ", "");
if (phone.IndexOf("8") == 1)
{
phone = phone.Remove(1, 1).Insert(1, "7"); // The fastest way ~ 500-550 mcs.
}
rowSQL["phone"] = AesCrypt.EncryptStringToBytes_Aes(phone, aesKey, aesIV);
}
// Column 8: Encrypt [message].
if ($"{rowSheet[8]}" != "")
{
rowSQL["message"] = AesCrypt.EncryptStringToBytes_Aes($"{rowSheet[8]}", aesKey, aesIV);
}
// Column 9: Check [request_date].
requestDate = $"{rowSheet[9]}";
if (requestDate != "" & requestDate.IndexOf("2") == 0) // If 'Y0YY.MM.DD'
{
rowSQL["request_date"] = rowSheet[9];
}
// Column 10: Encrypt [remark].
if ($"{(string)rowSheet[10]}" != "")
{
rowSQL["remark"] = AesCrypt.EncryptStringToBytes_Aes($"{rowSheet[10]}", aesKey, aesIV);
}
// Column 14: Formatting and Encrypt [instagram].
if ((string)rowSheet[14] != "")
{
// Getting only the login:
// - Delete link.
insta = String.Format("{0}", rowSheet[14]).Replace("https://www.instagram.com/", "").Replace("https://instagram.com/", "").Replace("/", "");
// - Delete '@' if as instagram username.
insta = insta.Replace("@", "");
// - Delete UTM tag if exist.
if (insta.IndexOf("?") > 1)
{
insta = insta.Remove(insta.IndexOf("?"));
}
rowSQL["instagram"] = AesCrypt.EncryptStringToBytes_Aes(insta, aesKey, aesIV);
}
rowSQL["vector"] = aesIV;
// Add filled new row to table.
tableSQL.Rows.Add(rowSQL);
// For checking data transfer.
amountRow++;
}
return(tableSQL);
}
