public List <AirtelModel> GetUploadedData(int SheetID, string ContentMonth, string ContentYear, int?page, int?limit, string sortBy, string direction, string searchString, out int total)
{
string sqlQuery = " EXEC spContentUploadDataRead " + SheetID + ",'" + ContentMonth + "','" + ContentYear + "'";
IDataReader dr = objDAL.GetDataReader(sqlQuery, KeyConstant.Server_Sahbox_17);
List <AirtelModel> objAirtelModelList = new List <AirtelModel>();
while (dr.Read())
{
AirtelModel objAirtelModel = new AirtelModel();
objAirtelModel.ExcelFileName = "Excel Name";
objAirtelModel.ContentDate = Convert.ToString(dr["Content_Date"], null);
objAirtelModel.TextLen = Convert.ToInt32(dr["Text_Len"], null);
objAirtelModel.ContentText = Convert.ToString(dr["Content_Text"], null);
objAirtelModel.UploadStatus = Convert.ToString(dr["Status"], null);
objAirtelModel.ContentCode = Convert.ToString(dr["Code"], null);
if (SheetID == 80)
{
objAirtelModel.HorosCopeCode = Convert.ToString(dr["HoroscopeCode"], null);
objAirtelModel.HorosCopeName = Convert.ToString(dr["HoroscopeName"], null);
}
objAirtelModelList.Add(objAirtelModel);
}
total = objAirtelModelList.Count();
var records = objAirtelModelList.Select(a => a);
if (!string.IsNullOrWhiteSpace(searchString))
{
// records = OperatorDataList.Where(p => p.KEYWORD.Contains(searchString) || p.SERVICE_ID.Contains(searchString));
}
if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
{
if (direction.Trim().ToLower() == "asc")
{
//records = SortHelper.OrderBy(records, sortBy);
}
else
{
// records = SortHelper.OrderByDescending(records, sortBy);
}
}
if (page.HasValue && limit.HasValue)
{
int start = (page.Value - 1) * limit.Value;
records = records.Skip(start).Take(limit.Value);
}
return(records.ToList());
}
private void ContentUpGeneric(DataTable dt, int Rows, string strExcelFileName)
{
clsDAL objDAL;
AirtelModel objAirtelModel = new AirtelModel();
objAirtelModel.ExcelFileName = strExcelFileName;
objAirtelModel.ContentDate = Convert.ToDateTime(dt.Rows[Rows]["Date"].ToString()).ToString("MM/dd/yyyy");
objAirtelModel.ContentText = dt.Rows[Rows]["F2"].ToString().Replace("'", null);
objAirtelModel.ContentMonth = ContentMonth(objAirtelModel.ContentDate);
objAirtelModel.SheetName = TableNameFormat(dt.TableName.ToString());
objDAL = new clsDAL();
string sqlQuery = "EXEC spContentUpload '" + objAirtelModel.ExcelFileName + "','" + objAirtelModel.ContentDate + "',N'"
+ objAirtelModel.ContentText + "','" + objAirtelModel.ContentMonth + "','" + objAirtelModel.SheetName + "'";
objDAL.ExecuteQuery(sqlQuery, KeyConstant.Server_Sahbox_17);
}
private void InsertAirtel(string dbServer, string dbName, string dbTable, int SheetID, string ContentMonth, string ContentYear)
{
string sqlQuerySelect, sqlQueryInsert, sqlQueryUpdate = String.Empty;
int StatusInsert = 0;
sqlQuerySelect = "SELECT Content_Date,Content_Text,Text_Len FROM tbl_Upload WHERE db_info=" + SheetID + " AND ContentMonth='" + ContentMonth + "' AND Year(Content_Date)='" + ContentYear + "' AND Status=0";
IDataReader dr = objDAL.GetDataReader(sqlQuerySelect, KeyConstant.Server_Sahbox_17);
while (dr.Read())
{
AirtelModel objAirtelModel = new AirtelModel();
objAirtelModel.ContentDate = Convert.ToString(dr["Content_Date"], null);
objAirtelModel.TextLen = Convert.ToInt32(dr["Text_Len"], null);
objAirtelModel.ContentText = Convert.ToString(dr["Content_Text"], null);
if (SheetID == 20)
{
sqlQueryInsert = " INSERT INTO " + dbTable + " VALUES('" + objAirtelModel.ContentDate + "','" + objAirtelModel.ContentText + "')";
}
else
{
sqlQueryInsert = " INSERT INTO " + dbTable + " (date,sms_eng) VALUES('" + objAirtelModel.ContentDate + "','" + objAirtelModel.ContentText + "')";
}
sqlQueryUpdate = " UPDATE tbl_Upload SET Status=1 WHERE db_info=" + SheetID + " AND Content_Date='" + objAirtelModel.ContentDate + "' ";
if (dbName == "WARID_CONTENT")
{
objDAL.ExecuteQuery(sqlQueryInsert, KeyConstant.Server_31_WaridContent);
StatusInsert = 1;
}
else if (dbName == "WARID")
{
objDAL.ExecuteQuery(sqlQueryInsert, KeyConstant.Server_31_Warid);
StatusInsert = 1;
}
if (StatusInsert == 1)
{
objDAL.ExecuteQuery(sqlQueryUpdate, KeyConstant.Server_Sahbox_17);
}
//clsDAL.db.CreateConnection().Close();
}
}
private void InsertAirtelHoroscope(string dbServer, string dbName, string dbTable, int SheetID, string ContentMonth, string ContentYear)
{
string sqlQuerySelect, sqlQueryInsert, sqlQueryUpdate = String.Empty;
int StatusInsert = 0;
sqlQuerySelect = " SELECT Content_Date, HoroscopeCode, HoroscopeName, Content_Text, Text_Len FROM tbl_AirtelHoroscope WHERE "
+ " Status= 0 AND Year(Content_Date)='" + ContentYear + "' AND DateName(Month , DateAdd(Month ,Month(Content_Date) ,-1 ))='" + ContentMonth + "'";
IDataReader dr = objDAL.GetDataReader(sqlQuerySelect, KeyConstant.Server_Sahbox_17);
while (dr.Read())
{
AirtelModel objAirtelModel = new AirtelModel();
objAirtelModel.ContentDate = Convert.ToString(dr["Content_Date"], null);
objAirtelModel.HorosCopeCode = Convert.ToString(dr["HoroscopeCode"], null);
objAirtelModel.HorosCopeName = Convert.ToString(dr["HoroscopeName"], null);
objAirtelModel.ContentText = Convert.ToString(dr["Content_Text"], null);
objAirtelModel.TextLen = Convert.ToInt32(dr["Text_Len"], null);
sqlQueryInsert = " INSERT INTO " + dbTable + "(HOR_DATE, HOR_CODE, HOR_NAME, SMS1) VALUES('" + objAirtelModel.ContentDate + "','" + objAirtelModel.HorosCopeCode + "','" + objAirtelModel.HorosCopeName + "','" + objAirtelModel.ContentText + "')";
sqlQueryUpdate = " UPDATE tbl_AirtelHoroscope SET Status=1 WHERE db_info=" + SheetID + " AND Content_Date='" + objAirtelModel.ContentDate + "' AND HoroscopeCode='" + objAirtelModel.HorosCopeCode + "'";
if (dbName == "WARID_CONTENT")
{
objDAL.ExecuteQuery(sqlQueryInsert, KeyConstant.Server_31_WaridContent);
StatusInsert = 1;
}
else if (dbName == "WARID")
{
objDAL.ExecuteQuery(sqlQueryInsert, KeyConstant.Server_31_Warid);
StatusInsert = 1;
}
if (StatusInsert == 1)
{
objDAL.ExecuteQuery(sqlQueryUpdate, KeyConstant.Server_Sahbox_17);
}
//clsDAL.db.CreateConnection().Close();
}
}
private void ContentUpAirtelHorosCope(DataTable dt, int Rows, string strExcelFileName)
{
clsDAL objDAL;
AirtelModel objAirtelModel = new AirtelModel();
objAirtelModel.ExcelFileName = strExcelFileName;
objAirtelModel.ContentDate = Convert.ToDateTime(dt.Rows[Rows]["Date"].ToString()).ToString("MM/dd/yyyy");
objAirtelModel.HorosCopeCode = dt.Rows[Rows]["Do Not Touch These Columns (B,C,D,E,F)"].ToString().Replace("'", null);
objAirtelModel.HorosCopeName = dt.Rows[Rows]["F3"].ToString().Replace("'", null);
objAirtelModel.ContentText = dt.Rows[Rows]["F4"].ToString().Replace("'", null);
objAirtelModel.ContentMonth = ContentMonth(objAirtelModel.ContentDate);
objAirtelModel.SheetName = TableNameFormat(dt.TableName.ToString());
objDAL = new clsDAL();
string sqlQuery = "EXEC spContentUploadAirtelHoroscope '" + objAirtelModel.ExcelFileName + "','" + objAirtelModel.ContentDate
+ "','" + objAirtelModel.HorosCopeCode + "','" + objAirtelModel.HorosCopeName + "',N'" + objAirtelModel.ContentText
+ "','" + objAirtelModel.ContentMonth + "','" + objAirtelModel.SheetName + "'";
objDAL.ExecuteQuery(sqlQuery, KeyConstant.Server_Sahbox_17);
}