public void runStoreProcedure()
{
CRUD sql = new CRUD();
SqlConnection con = ckon.sqlConMsg();
try
{
con.Open();
String cmd_sp = "DataGenerated";
SqlCommand cmd = new SqlCommand(cmd_sp, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 120;
cmd.ExecuteNonQuery();
UploaddFile();
}
catch (Exception e)
{
using (EventLog eventLog = new EventLog("Application"))
{
eventLog.Source = "Application";
eventLog.WriteEntry("Upload background " + e.Message.ToString(), EventLogEntryType.Information, 101, 1);
}
MessageBox.Show(e.ToString(), "No Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
public void downloadFile()
{
CRUD sql = new CRUD();
String downloadPath = "";
String fileName = "";
String id = "";
String jobId = "";
String storeId = "";
String rowFatch = "";
int syncType = 0;
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT JobID, StoreID, DownloadPath, SynchDetail, RowFatch, Synctype FROM JobTabletoSynchDetailDownload " +
"WHERE SynchDetail NOT IN(SELECT SynchDetail FROM JobSynchDetailDownloadStatus) OR " +
"SynchDetail IN(SELECT SynchDetail FROM JobSynchDetailDownloadStatus a WHERE a.RowApplied = 0)";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
Guid downloadSession = Guid.NewGuid();
while (ckon.sqlDataRd.Read())
{
downloadPath = Convert.ToString(ckon.sqlDataRd["DownloadPath"]);
id = Convert.ToString(ckon.sqlDataRd["SynchDetail"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
syncType = Convert.ToInt32(ckon.sqlDataRd["Synctype"]);
fileName = getFilename(downloadPath, syncType);
downloadFromFTP(downloadPath, fileName, id, jobId.Trim(), storeId.Trim(), rowFatch, downloadSession, syncType);
createFilePath(downloadPath);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=========================================================================================
public void ExecuteNonQueryMsg(String query)
{
string command;
SqlCommand sqlCmd = null;
SqlDataReader sqlDataRd = null;
SqlConnection sqlCon;
sqlCon = ckon.sqlConMsg();
try
{
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
sqlCmd = new SqlCommand(query);
sqlCmd.Connection = sqlCon;
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (sqlCon.State == ConnectionState.Open)
{
sqlCon.Close();
}
}
}
public void retreive(string cmd)
{
CRUD sql = new CRUD();
dgv_UploadFile.Rows.Clear();
try
{
ckon.sqlConMsg().Open();
if (cmd == "")
{
cmd = "SELECT a.TableName, a.JobID, a.StoreID, a.RowFatch, b.RowApplied, b.Status, a.SynchDate FROM JobTabletoSynchDetailUpload a " +
"INNER JOIN JobSynchDetailUploadStatus b ON b.SynchDetail = a.SynchDetail WHERE CONVERT(date, a.SynchDate) = '" + DateTime.Today.ToString("yyyy-MM-dd") + "'";
}
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
tableName = Convert.ToString(ckon.sqlDataRd["TableName"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
rowApplied = Convert.ToString(ckon.sqlDataRd["RowApplied"]);
status = Convert.ToString(ckon.sqlDataRd["Status"]);
syncDate = Convert.ToString(ckon.sqlDataRd["SynchDate"]);
if (status == "0")
{
newStatus = "Not Uploaded";
}
else
{
newStatus = "Uploaded";
}
int dgrows = dgv_UploadFile.Rows.Add();
dgv_UploadFile.Rows[dgrows].Cells[0].Value = tableName;
dgv_UploadFile.Rows[dgrows].Cells[1].Value = rowFatch;
dgv_UploadFile.Rows[dgrows].Cells[2].Value = rowApplied;
dgv_UploadFile.Rows[dgrows].Cells[3].Value = newStatus;
dgv_UploadFile.Rows[dgrows].Cells[4].Value = syncDate;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public async Task uploadSyncDetailReq(String SyncDetail)
{
int syncDetailId = 0;
int jobId = 0;
String storeId = "";
String tableName = "";
String uploadPath = "";
DateTime syncDate = DateTime.MinValue;
String createTable = "";
int rowFatch = 0;
int minId = 0;
int maxId = 0;
String tablePrimaryKey = "";
String identityColumn = "";
link_api = link.aLink;
bracketSyncUploadDetail uploadSyncs = new bracketSyncUploadDetail();
List <syncUploadDetail> uploadSyncDetails = new List <syncUploadDetail>();
uploadSyncs.uploadDetails = new List <syncUploadDetail>();
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT SynchDetail, JobID, StoreID, TableName, UploadPath, Synchdate, CreateTable, RowFatch, MinId, MaxId, TablePrimaryKey, identityColumn FROM JobTabletoSynchDetailUpload "
+ "WHERE SynchDetail = " + SyncDetail + "";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
syncDetailId = Convert.ToInt32(ckon.sqlDataRd["SynchDetail"]);
jobId = Convert.ToInt32(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
tableName = Convert.ToString(ckon.sqlDataRd["TableName"]);
uploadPath = Convert.ToString(ckon.sqlDataRd["UploadPath"]);
syncDate = Convert.ToDateTime(ckon.sqlDataRd["Synchdate"]);
createTable = Convert.ToString(ckon.sqlDataRd["CreateTable"]);
rowFatch = Convert.ToInt32(ckon.sqlDataRd["rowFatch"]);
minId = Convert.ToInt32(ckon.sqlDataRd["MinId"]);
maxId = Convert.ToInt32(ckon.sqlDataRd["MaxId"]);
tablePrimaryKey = Convert.ToString(ckon.sqlDataRd["TablePrimaryKey"]);
identityColumn = Convert.ToString(ckon.sqlDataRd["identityColumn"]);
syncUploadDetail tmp = new syncUploadDetail();
tmp.syncDetailsId = syncDetailId;
tmp.JobId = jobId;
tmp.StoreId = storeId;
tmp.TableName = tableName;
tmp.UploadPath = uploadPath;
tmp.Synchdate = syncDate;
tmp.CreateTable = createTable;
tmp.RowFatch = rowFatch;
tmp.minId = minId;
tmp.maxId = maxId;
tmp.TablePrimaryKey = tablePrimaryKey;
tmp.identityColumn = identityColumn;
uploadSyncs.uploadDetails.Add(tmp);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
var syncData = JsonConvert.SerializeObject(uploadSyncs);
String response = "";
var credentials = new NetworkCredential("username", "password");
var handler = new HttpClientHandler {
Credentials = credentials
};
var httpContent = new StringContent(syncData, Encoding.UTF8, "application/json");
using (var client = new HttpClient(handler))
{
HttpResponseMessage message = client.PostAsync(link_api + "/homsg/Upload", httpContent).Result;
}
}
public async Task updateSyncDetailReq()
{
String syncDetailId = "";
String rowFatch = "";
String rowApplied = "";
String status = "";
String downloadSessionId = "";
link_api = link.aLink;
bracketSyncDetail updateSyncs = new bracketSyncDetail();
List <updateSyncDetailDownload> updateSyncDetails = new List <updateSyncDetailDownload>();
updateSyncs.syncDetailDownload = new List <updateSyncDetailDownload>();
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT * FROM JobSynchDetailDownloadStatus WHERE RowApplied != 0 AND status = 1 ORDER BY SynchDetail ASC";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
syncDetailId = Convert.ToString(ckon.sqlDataRd["SynchDetail"]);
rowApplied = Convert.ToString(ckon.sqlDataRd["RowApplied"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
status = Convert.ToString(ckon.sqlDataRd["Status"]);
downloadSessionId = Convert.ToString(ckon.sqlDataRd["Downloadsessionid"]);
updateSyncDetailDownload tmp = new updateSyncDetailDownload();
tmp.syncDetailsId = syncDetailId;
tmp.rowApplied = rowApplied;
tmp.rowFatch = rowFatch;
tmp.status = status;
tmp.downloadSessionId = downloadSessionId;
updateSyncs.syncDetailDownload.Add(tmp);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
var syncData = JsonConvert.SerializeObject(updateSyncs);
String response = "";
var credentials = new NetworkCredential("username", "password");
var handler = new HttpClientHandler {
Credentials = credentials
};
var httpContent = new StringContent(syncData, Encoding.UTF8, "application/json");
using (var client = new HttpClient(handler))
{
HttpResponseMessage message = client.PostAsync(link_api + "/homsg/updateSyncDownload", httpContent).Result;
}
}
