public static DataTable GetDataTableByStoreProcedure(string storeProcedureName, SqlParameters sqlParameters)
{
DataTable resultDt = new DataTable();
MySqlConnection connection = ConnectToSql();
if (connection == null)
{
return(null);
}
try
{
MySqlCommand cmd = new MySqlCommand(storeProcedureName, connection);
foreach (SqlParameter sqlParameter in sqlParameters)
{
cmd.Parameters.AddWithValue(sqlParameter.Field, sqlParameter.Value);
}
cmd.CommandType = CommandType.StoredProcedure;
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
da.Fill(resultDt);
}
connection.Close();
return(resultDt);
}
catch (Exception ex)
{
LogsHelper.Print(MessagesVariable.FailedFetchDataInMySql + "\n" + ex.ToString());
return(null);
}
}
public DataTable getdb_branch(string SQL, string server, string userid, string password, string database)
{
string cs = @"server=" + server + ";userid=" + userid + ";password="******";database=" + database + ";Allow Zero Datetime=true;Charset = utf8;Connect Timeout=300;";
MySqlConnection myconn = null;
try
{
myconn = new MySqlConnection(cs);
myconn.Open();
DataTable dataTable = new DataTable();
MySqlCommand cmd = new MySqlCommand(SQL, myconn);
cmd.CommandTimeout = 300;
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
da.Fill(dataTable);
}
myconn.Close();
return(dataTable);
}
catch (Exception ex)
{
LogsHelper.Print("[getdb_branch] " + ex.Message.ToString());
LogsHelper.Print("[getdb_branch(sql)] " + SQL);
Console.WriteLine("getdb_branch error: " + SQL);
Console.WriteLine(ex.Message.ToString());
return(new DataTable());
}
}
private void frmError_Load(object sender, EventArgs e)
{
this.lblError.Text = this.errormessage;
LogsHelper.Print("ERROR MESSAGE: " + this.errormessage);
fncFullScreen fncfullscreen = new fncFullScreen(this);
fncfullscreen.ResizeFormsControls();
}
public static DataTable GetDataTableByTransaction(SqlDetails sqlDetails)
{
MySqlConnection connection = ConnectToSql();
DataTable resultDt = null;
if (connection == null)
{
return(resultDt);
}
MySqlTransaction transaction = connection.BeginTransaction();
resultDt = new DataTable();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandTimeout = 300;
foreach (SqlDetail sqlDetail in sqlDetails)
{
cmd.CommandText = sqlDetail.Sql;
SqlParameters sqlParameters = sqlDetail.SqlParameters;
foreach (SqlParameter sqlParameter in sqlParameters)
{
cmd.Parameters.AddWithValue(sqlParameter.Field, sqlParameter.Value);
}
if (sqlDetail.Sql.Trim('\r', '\n', ' ').IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) == 0)
{
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
da.Fill(resultDt);
}
}
else
{
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
connection.Close();
return(resultDt);
}
catch (Exception ex)
{
transaction.Rollback();
connection.Close();
LogsHelper.Print(MessagesVariable.FailedFetchDataInMySql + "\n" + ex.ToString());
return(resultDt);
}
}
public static void DeleteUnusedSalesHead()
{
// done to avoid deletion of current pending transaction
return;
string sql = @"
SELECT
`ornumber`
FROM
`saleshead`
WHERE
`branchid`=" + cls_globalvariables.BranchCode + @"
AND `terminalno` = " + cls_globalvariables.terminalno_v + @"
AND `date` >= '" + DateTime.Now.AddDays(-10).ToString("yyyy-MM-dd") + @"'
AND `status` = 1
ORDER BY `ornumber` DESC LIMIT 1;";
DataTable DT = mySQLFunc.getdb(sql);
if (DT == null || DT.Rows.Count <= 0)
{
return;
}
sql = @"
SELECT
GROUP_CONCAT(`SyncId`) AS 'wids', GROUP_CONCAT(`ornumber`) AS 'ornumbers'
FROM
`saleshead`
WHERE
`branchid`=" + cls_globalvariables.BranchCode + @"
AND `terminalno` = " + cls_globalvariables.terminalno_v + @"
AND `date` >= '" + DateTime.Now.AddDays(-10).ToString("yyyy-MM-dd") + @"'
AND `ornumber` > " + DT.Rows[0]["ornumber"].ToString() + @"
AND `status` = 0";
DT = mySQLFunc.getdb(sql);
if (DT == null || DT.Rows[0]["wids"] == null || DT.Rows[0]["wids"].ToString() == "")
{
return;
}
string wids = DT.Rows[0]["wids"].ToString();
string ornumbers = DT.Rows[0]["wids"].ToString();
sql = @"DELETE FROM saleshead WHERE `SyncId` IN (" + wids + ")";
mySQLFunc.setdb(sql);
LogsHelper.Print("DELETED2 in saleshead ors = " + ornumbers);
}
private void frmChange_c_Shown(object sender, EventArgs e)
{
if (Visible && flag)
{
flag = false;
int temp = 0;
bool retry = false;
do
{
retry = false;
frmLoad loadForm = new frmLoad("Saving Transaction", "Loading Screen");
loadForm.BackgroundWorker.DoWork += (sender1, e1) =>
{
temp = POSMain.save_transaction_thread(tran);
};
loadForm.ShowDialog();
if (temp == 0)
{
LogsHelper.Print("Tender success");
}
else if (temp == 1)
{
fncFilter.alert("Member transaction was unable to complete due to connection problems. F8 - Payment to try again.");
LogsHelper.Print("Tender failed: Member Feature Offline");
isTransactionDone = false;
}
else if (temp == -1)
{
LogsHelper.Print("Tender failed: General Saving failure");
if (DialogHelper.ShowDialog("An error occured in saving transaction. Would you like to retry?", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
retry = true;
LogsHelper.Print("Tender failure popup: Retry");
}
else
{
LogsHelper.Print("Tender failure popup: Ignore");
fncHardware.print_receipt(tran, false, false);
}
}
}while (retry);
}
}
public static Boolean setdb_branch(string SQL, string server, string userid, string password, string database)
{
if (!check_connection_branch(server, userid, password, database))
{
return(false);
}
string cs = @"server=" + server + ";userid=" + userid + ";password="******";database=" + database + ";Allow Zero Datetime=true;Charset = utf8;Connect Timeout=300;";
MySqlConnection myconn = null;
try
{
myconn = new MySqlConnection(cs);
myconn.Open();
}
catch (Exception ex)
{
Console.WriteLine("setdb_branch connect error: " + server);
Console.WriteLine(ex.Message.ToString());
return(false);
}
try
{
MySqlCommand cmd = new MySqlCommand(SQL, myconn);
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
myconn.Close();
return(true);
}
catch (Exception ex)
{
LogsHelper.Print("[setdb_branch] " + ex.Message.ToString());
LogsHelper.Print("[setdb_branch(sql)] " + SQL);
Console.WriteLine("setdb_branch error: " + SQL);
Console.WriteLine(ex.Message.ToString());
return(false);
}
}
public static bool ExecuteQueryByStoreProcedure(string storeProcedureName, SqlParameters sqlParameters)
{
try
{
MySqlConnection connection = ConnectToSql();
MySqlCommand cmd = new MySqlCommand(storeProcedureName, connection);
foreach (SqlParameter sqlParameter in sqlParameters)
{
cmd.Parameters.AddWithValue(sqlParameter.Field, sqlParameter.Value);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
connection.Close();
return(true);
}
catch (Exception ex)
{
LogsHelper.Print(MessagesVariable.FailedExecuteQueryInMySql + "\n" + ex.ToString());
return(false);
}
}
public static bool ExecuteTransaction(SqlDetails sqlDetails)
{
MySqlConnection connection = ConnectToSql();
if (connection == null)
{
return(false);
}
MySqlTransaction transaction = connection.BeginTransaction();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandTimeout = 300;
foreach (SqlDetail sqlDetail in sqlDetails)
{
cmd.CommandText = sqlDetail.Sql;
foreach (SqlParameter sqlParameter in sqlDetail.SqlParameters)
{
cmd.Parameters.AddWithValue(sqlParameter.Field, sqlParameter.Value);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
connection.Close();
return(true);
}
catch (Exception ex)
{
transaction.Rollback();
connection.Close();
LogsHelper.Print(MessagesVariable.FailedExecuteQueryInMySql + "\n" + ex.ToString());
return(false);
}
}