private void Form1_Load(object sender, EventArgs e)
{
var QCL = _read.QCLConfig();
lblBcFrom.Text = QCL.From;
lblBcTo.Text = QCL.To;
}
public string getKPTN(string tableName, string transDate, string empID, string branchcode)
{
string server = _read.QCLConfig().Server;
string user = _read.QCLConfig().User;
string password = _read.QCLConfig().Password;
int empcode = Convert.ToInt32(empID.Substring(4));
string constring = "server = " + server + ";database = " + branchcode + ";uid = " + user + ";password= "******"; pooling=false;min pool size=0;max pool size=1000;connection lifetime=0; connection timeout=360000";
List <string> kptnList = new List <string>();
try
{
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "select KPTN from " + tableName + " where usr_id = @empcode and Transdate = @transDate";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@empcode", empcode);
cmd.Parameters.AddWithValue("@transDate", transDate);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
kptnList.Add(dr["KPTN"].ToString());
}
string kptn = string.Join(",", kptnList.ToArray());
return(kptn);
}
else
{
return("0");
}
}
}
}
}
catch (Exception ex)
{
_logger.Error(ex.ToString());
return("0");
}
}
private void FetchSendoutDomestic(List <DomesticSendoutTrans> _sendoutData, string transDate, string empID, string empFullname, string branchcode)
{
var _gl = _read.GLAccounts().GLAccounts;
string server = _read.QCLConfig().Server;
string user = _read.QCLConfig().User;
string password = _read.QCLConfig().Password;
string constring = "server = " + server + ";database = " + branchcode + ";uid = " + user + ";password= "******"; pooling=false;min pool size=0;max pool size=1000;connection lifetime=0; connection timeout=360000";
double totalPrincipal = 0;
double totalCharge = 0;
double totalQuantity = _sendoutData.Count();
bool saveSOPrincipal = false;
bool saveSOCharge = false;
List <string> kptnList = new List <string>();
try
{
foreach (var sendoutData in _sendoutData)
{
if (sendoutData.domestic_principal != 0.0)
{
totalPrincipal += sendoutData.domestic_principal;
}
if (sendoutData.domestic_charge != 0.0)
{
totalCharge += sendoutData.domestic_charge;
}
kptnList.Add(sendoutData.kptn);
}
string kptn = string.Join(",", kptnList.ToArray());
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = trans;
saveSOPrincipal = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_TellerGL, _gl.Domestic_PHP_SendoutPrincipalGL, totalPrincipal, 0.0, totalQuantity, "KP Sendout Principal", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
saveSOCharge = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_TellerGL, _gl.Domestic_PHP_SendoutChargeGL, totalCharge, 0.0, totalQuantity, "KP Sendout Charge", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
if (saveSOPrincipal && saveSOCharge)
{
bool insertSuccess = _query.InsertKPTN(tableName, kptn, transDate, empID, branchcode, cmd);
if (insertSuccess)
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
else
{
trans.Rollback();
}
}
}
}
catch (Exception ex)
{
_logger.Error(ex.ToString());
}
}
private void FetchSendoutCancelDomestic(List <DomesticSendoutCancelTrans> _sendoutCancelData, string transDate, string empID, string empFullname, string branchcode)
{
var _gl = _read.GLAccounts().GLAccounts;
string server = _read.QCLConfig().Server;
string user = _read.QCLConfig().User;
string password = _read.QCLConfig().Password;
string constring = "server = " + server + ";database = " + branchcode + ";uid = " + user + ";password= "******"; pooling=false;min pool size=0;max pool size=1000;connection lifetime=0; connection timeout=360000";
double totalRTSPrincipal = 0;
double totalDSOPrincipal = 0;
double totalCSOPrincipal = 0;
double totalRFCCharge = 0;
double totalDSOCharge = 0;
double totalCSOCharge = 0;
double totalRTSQuantity = 0;
double totalRFCQuantity = 0;
double totalDSOQuantity = 0;
double totalCSOQuantity = 0;
bool saveRTSPrincipal = false;
bool saveDSOPrincipal = false;
bool saveDSOCharge = false;
bool saveRFCCharge = false;
bool saveCSOPrincipal = false;
bool saveCSOCharge = false;
List <string> kptnList = new List <string>();
try
{
foreach (var sendoutCancelData in _sendoutCancelData)
{
switch (sendoutCancelData.cancelreason.Trim())
{
case "Return to Sender":
totalRTSQuantity += 1;
totalRTSPrincipal += sendoutCancelData.principal;
break;
case "Request for Change":
totalRFCQuantity += 1;
totalRFCCharge += sendoutCancelData.cancelcharge;
break;
case "Double Sendout":
totalDSOQuantity += 1;
totalDSOPrincipal += sendoutCancelData.principal + sendoutCancelData.charge;
totalDSOCharge += sendoutCancelData.cancelcharge;
break;
case "Cancel Sendout":
totalCSOQuantity += 1;
totalCSOPrincipal += sendoutCancelData.principal + sendoutCancelData.charge;
totalCSOCharge += sendoutCancelData.cancelcharge;
break;
}
kptnList.Add(sendoutCancelData.kptn);
}
string kptn = string.Join(",", kptnList.ToArray());
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = trans;
if (totalRTSQuantity > 0)
{
saveRTSPrincipal = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_ReturnToSenderGL, _gl.Domestic_PHP_TellerGL, totalRTSPrincipal, 0.0, totalRTSQuantity, "RTS / Cancelled Sendout", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
}
if (totalDSOQuantity > 0)
{
saveDSOPrincipal = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_SendoutCancelGL, _gl.Domestic_PHP_TellerGL, totalDSOPrincipal, totalDSOQuantity, 0.0, "Double / Cancelled Sendout", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
saveDSOCharge = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_TellerGL, _gl.Domestic_PHP_SendoutCancelGL, totalDSOCharge, 0.0, totalDSOQuantity, "KP Other Income", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
}
if (totalRFCQuantity > 0)
{
saveRFCCharge = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_TellerGL, _gl.Domestic_PHP_RFCGL, totalRFCCharge, 0.0, totalRFCQuantity, "KP Other Income", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
}
if (totalCSOQuantity > 0)
{
saveCSOPrincipal = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_SendoutCancelGL, _gl.Domestic_PHP_TellerGL, totalCSOPrincipal, totalCSOQuantity, 0.0, "RTS / Cancelled Sendout", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
saveCSOCharge = _qcl.SaveTransaction(_gl.Domestic_PHP_Journal, _gl.Domestic_PHP_TellerGL, _gl.Domestic_PHP_SendoutCancelGL, totalCSOCharge, 0.0, totalCSOQuantity, "KP Other Income", "KPSOPRDOM1234", "PHP", transDate, empID, empFullname, branchcode, cmd);
}
if (saveRTSPrincipal || saveDSOPrincipal && saveDSOCharge || saveRFCCharge || saveCSOPrincipal && saveCSOCharge)
{
bool insertSuccess = _query.InsertKPTN(tableName, kptn, transDate, empID, branchcode, cmd);
if (insertSuccess)
{
trans.Commit();
}
else
{
trans.Rollback();
}
}
else
{
trans.Rollback();
}
}
}
}
catch (Exception ex)
{
_logger.Error(ex.ToString());
}
}