private void ErrorLog(Exception ex, string SubDeviceId)
{
try
{
string[,] para = { { "SubDeviceId", SubDeviceId }, { "ErrorMessage", ex.Message.ToString() }, { "ErrorDetails", ex.ToString() } };
string sqlQue = "insert into DeviceErrorLog (DeviceErrorLogID,SubDeviceID,ErrorMessage,ErrorDetails) values (NEWID(),@SubDeviceID,@ErrorMessage,@ErrorDetails)";
SqlHelper.ExecuteNoneQueryWithPera(sqlQue, para);
CommanClass.SendMail(ex, SubDeviceId);
}
catch (Exception ex1)
{ }
}
private void SaveData(string FilePath, string DeviceDate, string SubDeviceId, decimal multiplier)
{
string extension = Path.GetExtension(FilePath), AccessColumn = "", SerialNo = "", DeviceTotalCount = "";
try
{
DataTable tempTable = GetCSVFileToDataTable("", FilePath, extension, out SerialNo);
if (SerialNo != "")
{
string ColNM = string.Empty, DelCol = string.Empty;
string[] SpSrNo = SerialNo.Trim().Split(' ');
DataTable dt = SqlHelper.ExecuteProcedure("Select A.ColumnMappingID,A.MainColumnID,B.ColumnName,A.CSVFileColumnsID,C.CSVFileColumnName,D.SerialNo from ColumnMapping as A join MainColumn as B on A.MainColumnID= B.MainColumnID join CSVFileColumns as C on A.CSVFileColumnsID = C.CSVFileColumnsID join MainDeviceMaster as F on C.DeviceID = F.DeviceID join SubDeviceMaster as D on F.DeviceID = D.DeviceID where D.SerialNo = '" + SpSrNo[3].Trim() + "' order by ColumnName");
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
try { AccessColumn += dt.Rows[i]["ColumnName"].ToString() + ","; tempTable.Columns[dt.Rows[i]["CSVFileColumnName"].ToString()].ColumnName = dt.Rows[i]["ColumnName"].ToString(); }
catch (Exception ex) { }
}
AccessColumn = AccessColumn.Remove(AccessColumn.Length - 1, 1);
DataTable dtCount = SqlHelper.ExecuteProcedure("select count(DeviceDataID) as TotalCount from DeviceData where SubDeviceId = '" + SubDeviceId + "' and DeviceDate = '" + DeviceDate + "' ");
DeviceTotalCount = dtCount.Rows[0]["TotalCount"].ToString();
for (int i = 0; i < tempTable.Columns.Count; i++)
{
ColNM = tempTable.Columns[i].ColumnName;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Rows[j]["ColumnName"].ToString() == ColNM)
{
ColNM = ""; break;
}
}
DelCol += !string.IsNullOrEmpty(ColNM) ? ColNM + "," : "";
}
foreach (string str in DelCol.Split(','))
{
if (!string.IsNullOrEmpty(str))
{
tempTable.Columns.Remove(str);
}
}
string[,] para = new string[dt.Rows.Count + 2, 2];
decimal Val = 0;
para[0, 0] = "SubDeviceId";
para[0, 1] = SubDeviceId;
para[1, 0] = "DeviceDate";
para[1, 1] = DeviceDate;
for (int i = int.Parse(DeviceTotalCount); i < tempTable.Rows.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
para[j + 2, 0] = dt.Rows[j]["ColumnName"].ToString();
//if (j == 0)
//para[j + 2, 1] = tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString();
if (dt.Rows[j]["ColumnName"].ToString() == "DeviceDateTime")
{
//para[j + 2, 1] = DateTime.ParseExact(DeviceDate + tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString(), "yyyy-MM-dd mm:ss", null).ToString("yyyy-MM-dd mm:ss");
para[j + 2, 1] = DeviceDate + " " + tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString();
}
else
{
try {
Decimal.TryParse(tempTable.Rows[i][dt.Rows[j]["ColumnName"].ToString()].ToString(), out Val);
if (dt.Rows[j]["ColumnName"].ToString().ToLower() == "eac")
{
if (Val != 0)
{
Val = (Val * Convert.ToDecimal(multiplier));
}
}
para[j + 2, 1] = Val.ToString();
}
catch
{
para[j + 2, 1] = string.Empty;
}
}
}
string sqlQue = "insert into DeviceData(SubDeviceId,DeviceDate," + AccessColumn + ") values (@SubDeviceId,@DeviceDate,@" + AccessColumn.Replace(",", ",@") + ")";
//sqlQue = sqlQue.Replace("@Eac", "@Eac*" + multiplier);
SqlHelper.ExecuteNoneQueryWithPera(sqlQue, para);
}
}
else
{
CommanClass.SendMail(null, SpSrNo[3].Trim());
}
}
}
catch (Exception ex)
{ ErrorLog(ex, SubDeviceId); }
//DataSet ds = CDRDataColumnMappingCLS.GetCDRDataColumnMappingDataDS(sub);
//DataTable dt = ds.Tables[0], dtNew = ds.Tables[1];
//Make column string for get data from acess database
//Make column string for insert data to sql server
//if (dt.Rows.Count > 0)
//{
// string AccessColumn = "", SqlColumn = "", OrderByColumn = "";
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// AccessColumn += "[" + dt.Rows[i]["CDRDataExcelColumnName"].ToString() + "] as " + dt.Rows[i]["CDRDataColumnName"].ToString() + ",";
// SqlColumn += "[" + dt.Rows[i]["CDRDataColumnName"].ToString() + "],";
// }
// AccessColumn = AccessColumn.Remove(AccessColumn.Length - 1, 1);
// string ColNM = string.Empty, DelCol = string.Empty;
// for (int i = 0; i < tempTable.Columns.Count; i++)
// {
// ColNM = tempTable.Columns[i].ColumnName;
// for (int j = 0; j < dt.Rows.Count; j++)
// { if (dt.Rows[j]["CDRDataColumnName"].ToString() == ColNM) { ColNM = ""; break; } }
// DelCol += !string.IsNullOrEmpty(ColNM) ? ColNM + "," : "";
// }
// foreach (string str in DelCol.Split(','))
// if (!string.IsNullOrEmpty(str)) tempTable.Columns.Remove(str);
//}
}