private void Login()
{
string tmpHolder = "SELECT Username, Password FROM tblSysUsers WHERE Username = '******' AND Password ='******'";
if (globalData.AlreadyExist(tmpHolder) == true)
{
string tmpHolderAuth = "SELECT * FROM tblServerAuth WHERE MacAddress='" + GlobalData.GetMACAddress() + "'";
if (globalData.AlreadyExist(tmpHolderAuth) == true)
{
DateTime tmpSDate = Convert.ToDateTime(globalData.GetValue(tmpHolderAuth, "StartDate"));
DateTime tmpEDate = Convert.ToDateTime(globalData.GetValue(tmpHolderAuth, "EndDate"));
var Different = tmpEDate - DateTime.Now;
double tmpDays = Different.TotalDays;
if (tmpDays == 0 || tmpDays < 0)
{
MessageBox.Show("Your Software License is Upto Date, Please Purchase License ", "Authendication Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
getUserInfo();
getCompanyInfo();
GlobalData.GCompName = globalData.GetValue("SELECT * FROM tblCompany WHERE CustID=" + GlobalData.GCompID + "", "NameOF");
GlobalData.GUserGroup = globalData.GetValue("SELECT * FROM tblUserGroup WHERE CustID=" + gUserGroupID + "", "NameOF");
string stockRommID = globalData.GetValue("SELECT * FROM tblSetCompStockRoom WHERE CompID = " + GlobalData.GCompID + "", "StockRoomID");
if (string.IsNullOrEmpty(stockRommID))
{
GlobalData.GStockRoomID = 0;
}
else
{
GlobalData.GStockRoomID = Convert.ToInt32(stockRommID);
}
this.Hide();
frmIPMain f = new frmIPMain();
f.ShowDialog();
}
}
else
{
MessageBox.Show("Sorry.. This PC don't have license ", "Authendication Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
else
{
LoginAttempts += 1;
MessageBox.Show("Sorry.. Username/ Password is incorrect", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (LoginAttempts == 3)
{
Environment.Exit(0);
}
return;
}
}
private void PatchPaidAmount()
{
for (int i = 0; i < dgvGrid.Rows.Count; i++)
{
gSB.Clear();
gSB.Append("SELECT SUM(TotalPaid) AS PaidAmount " +
" FROM tblSaleBrief " +
" WHERE IsActive = 'N' AND MOP = 'CRE' AND CustID = " + dgvGrid.Rows[i].Cells[3].Value + " ");
dgvGrid.Rows[i].Cells[6].Value = Convert.ToDouble(GlbData.GetValue(gSB.ToString(), "PaidAmount"));
dgvGrid.Rows[i].Cells[7].Value = Convert.ToDouble(dgvGrid.Rows[i].Cells[5].Value) - Convert.ToDouble(dgvGrid.Rows[i].Cells[6].Value);
}
}
private void txtCusCode_Validating(object sender, CancelEventArgs e)
{
if (txtCusCode.Text == "")
return;
gSB.Clear();
gSB.Append("SELECT * FROM tblItem WHERE Code = '" + txtCusCode.Text.Trim() + "' AND IsActive = 'Y'");
if (GlbData.AlreadyExist(gSB.ToString()))
{
gItemID = Convert.ToInt32(GlbData.GetValue(gSB.ToString(), "CustID"));
lblCustomer.Text = GlbData.GetValue(gSB.ToString(), "NameOF");
}
else
{
MessageBox.Show("Sorry.. This Product does not Exist", "INVALID", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtCusCode.Focus();
return;
}
}
private void FillTranInfo()
{
gSB.Clear();
gSB.Append("SELECT ISNULL(SUM(ManAmount),0) AS Sale FROM tblSaleBrief WHERE DateOn = CONVERT(date, '" + DateTime.Now + "') AND CompId = " + GlobalData.GCompID + " AND IsActive = 'Y' ");
lblSale.Text = string.Format("{0:N}", Convert.ToDouble(GlbData.GetValue(gSB.ToString(), "Sale")));
gSB.Clear();
gSB.Append("SELECT ISNULL(SUM(ManAmount),0) AS CCN FROM tblCCNBrief WHERE DateOn = CONVERT(date, '" + DateTime.Now + "') AND CompId = " + GlobalData.GCompID + " ");
lblReturns.Text = string.Format("{0:N}", Convert.ToDouble(GlbData.GetValue(gSB.ToString(), "CCN")));
lblNetSale.Text = string.Format("{0:N}", Convert.ToDouble(lblSale.Text) - Convert.ToDouble(lblReturns.Text));
gSB.Clear();
gSB.Append("SELECT ISNULL(SUM(MO.CashTendered),0) AS Collection " +
" FROM tblCustPayBrief AS BR INNER JOIN " +
" tblMOP AS MO ON BR.MOPID = MO.MOPID INNER JOIN " +
" tblCompany AS CO ON CO.CustID = BR.CompID " +
" WHERE BR.DateOn = CONVERT(date, '" + DateTime.Now + "') AND BR.CompId = " + GlobalData.GCompID + " AND BR.TranCode = 'CUS004' ");
lblCollections.Text = string.Format("{0:N}", Convert.ToDouble(GlbData.GetValue(gSB.ToString(), "Collection")));
gSB.Clear();
gSB.Append("SELECT ISNULL(COUNT(BillID),0) AS Bills FROM tblSaleBrief WHERE DateOn = CONVERT(date, '" + DateTime.Now + "') AND CompId = " + GlobalData.GCompID + " AND IsActive = 'Y' ");
lblBills.Text = GlbData.GetValue(gSB.ToString(), "Bills").ToString();
}
private void btnShow_Click(object sender, EventArgs e)
{
btnShow.Enabled = false;
string tmpQuery = "";
//getAmount();
if (cboBasedOn.SelectedIndex == 2)
{
tmpQuery = getPaticularInvoice();
gCSHTotal = Convert.ToDouble(GlbData.GetValue("SELECT DisAmount FROM tblSaleBrief WHERE BillNo = '" + txtInvoice.Text.Trim() + "'", "DisAmount"));
print.RoadToPrintReport(tmpQuery, @"C:\LOB\Bin\Debug\Rep\Invoice.rpt", rptView, "P", gCSHTotal, gCHQTotal, gCCSTotal, gCRETotal);
}
else
{
tmpQuery = GetQuery() + getDateRange() + getCustomerID() + getMOP();
print.RoadToPrintReport(tmpQuery, @"C:\LOB\Bin\Debug\Rep\NetSale.rpt", rptView, "P", gCSHTotal, gCHQTotal, gCCSTotal, gCRETotal);
}
btnShow.Enabled = true;
}
private void getAmount()
{
string tmpHolder = "";
string tmpAmount = "";
switch (cboMOP.SelectedIndex)
{
case 0:
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE MO.MOPType = 'CSH' AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCSHTotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE MO.MOPType = 'CHQ' AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCHQTotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE MO.MOPType = 'CCS' AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCCSTotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE (MO.MOPType = 'CRE' OR MO.MOPType = 'CREI') AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCRETotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
break;
case 1:
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE MO.MOPType = 'CSH' AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCSHTotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
gCHQTotal = 0;
gCCSTotal = 0;
gCRETotal = 0;
break;
case 2:
gCSHTotal = 0;
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE MO.MOPType = 'CHQ' AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCHQTotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
gCCSTotal = 0;
gCRETotal = 0;
break;
case 3:
gCSHTotal = 0;
gCHQTotal = 0;
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE MO.MOPType = 'CCS' AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCCSTotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
gCRETotal = 0;
break;
case 4:
gCSHTotal = 0;
gCHQTotal = 0;
gCCSTotal = 0;
tmpHolder = "SELECT SUM(BR.ManAmount) AS Amount " +
" FROM tblSaleBrief AS BR INNER JOIN " +
" tblCustomer AS CU ON BR.CustID = CU.CustID INNER JOIN " +
" tblMOP AS MO ON BR.BillId = MO.BillID " +
" WHERE (MO.MOPType = 'CRE' OR MO.MOPType = 'CREI) AND BR.IsActive = 'Y' " + getDateRange() + getCustomerID();
tmpAmount = GlbData.GetValue(tmpHolder, "Amount");
gCRETotal = string.IsNullOrEmpty(tmpAmount) ? 0 : Convert.ToDouble(tmpAmount);
break;
default:
break;
}
}
private void InsertRecord()
{
MySqlCommand cmd5 = new MySqlCommand("TRUNCATE TABLE tblTEMPPOAnalyser", GlobalData.GCon);
cmd5.ExecuteNonQuery();
gSB.Clear();
gSB.Append(" INSERT INTO tblTEMPPOAnalyser ( " +
" ItemId, ItemCode, ItemName, CategoryId, CategoryCode, CategoryName, " +
" SupplierId, SupplierName, SupplierCode, CostPrice, SellingPrice, WholeSalePrice, " +
" OpenStk, Purchase, PurchaseFree, SCN, SCNFree, Sale, SaleFree, CCN, CCNFree, STKAdd, " +
" StkDED, CloseStk ) " +
" SELECT IT.CustId AS ItemId, IT.Code AS ItemCode, IT.NameOF AS ItemName, MC.CustID AS CategoryID, " +
" MC.Code AS CategoryCode, MC.NameOF AS CategoryName, SU.CustID AS SupplierId, SU.NAmeOf AS SupplierName, " +
" SU.Code AS SupplierCode, IT.Cost AS CostPrice, IT.SellingPrice AS SellingPrice, IT.WholeSalePrice AS WholeSalePrice, " +
" 0 AS OpenStk, 0 AS Purchase, 0 AS PurchaseFree, 0 AS SCN, 0 AS SCNFree, 0 AS Sale, 0 AS SaleFree, 0 AS CCN, 0 AS CCNFree, " +
" 0 AS StkAdd, 0 AS StkDED, 0 AS CloseStk " +
" FROM tblItem AS IT LEFT OUTER JOIN " +
" tblComSTKStore AS STK ON IT.CustID = STK.ItemID INNER JOIN " +
" tblStockRoom AS ST ON ST.CustID = STK.StockRoomID INNER JOIN " +
" tblSupplier AS SU ON IT.SupplierID = SU.CustID LEFT OUTER JOIN " +
" tblMajorCategory AS MC ON IT.CategoryID = MC.CustID ");
MySqlCommand cmd = new MySqlCommand(gSB.ToString(), GlobalData.GCon);
cmd.ExecuteNonQuery();
//PURCHASE - FREE
gSB.Clear();
gSB.Append("UPDATE t1 SET t1.Purchase = PR.Purchase, t1.PurchaseFree = PR.PurchaseFree " +
" FROM tblTEMPPOAnalyser AS t1 INNER JOIN " +
" (SELECT ItemId, SUM(QtyIn) AS Purchase, SUM(FreeIn) AS PurchaseFree " +
" FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP002' " +
" GROUP BY ItemId) AS PR ON PR.ItemId = t1.ItemId ");
MySqlCommand command1 = new MySqlCommand(gSB.ToString(), GlobalData.GCon);
command1.ExecuteNonQuery();
//SCN - FREE
gSB.Clear();
gSB.Append("UPDATE t1 SET t1.SCN = SR.SCN, t1.SCNFree = SR.SCNFree " +
" FROM tblTEMPPOAnalyser AS t1 INNER JOIN " +
" (SELECT ItemId, SUM(QtyOut) AS SCN, SUM(FreeOut) AS SCNFree " +
" FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP003' " +
" GROUP BY ItemId) AS SR ON SR.ItemId = t1.ItemId ");
MySqlCommand command2 = new MySqlCommand(gSB.ToString(), GlobalData.GCon);
command2.ExecuteNonQuery();
//SALE -FREE
gSB.Clear();
gSB.Append("UPDATE t1 SET t1.Sale = SAL.Sale, t1.SaleFree = SAL.SaleFree " +
" FROM tblTEMPPOAnalyser AS t1 INNER JOIN " +
" (SELECT ItemId, SUM(QtyOut) AS Sale, SUM(FreeOut) AS SaleFree " +
" FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS002' " +
" GROUP BY ItemId) AS SAL ON SAL.ItemId = t1.ItemId ");
MySqlCommand command3 = new MySqlCommand(gSB.ToString(), GlobalData.GCon);
command3.ExecuteNonQuery();
//CCN - FREE
gSB.Clear();
gSB.Append("UPDATE t1 SET t1.CCN = CN.CCN, t1.CCNFree = CN.CCNFree " +
" FROM tblTEMPPOAnalyser AS t1 INNER JOIN " +
" (SELECT ItemId, SUM(QtyIn) AS CCN, SUM(FreeIn) AS CCNFree " +
" FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS003' " +
" GROUP BY ItemId) AS CN ON CN.ItemId = t1.ItemId ");
MySqlCommand command4 = new MySqlCommand(gSB.ToString(), GlobalData.GCon);
command4.ExecuteNonQuery();
//ADJUSTMENT
gSB.Clear();
gSB.Append("UPDATE t1 SET t1.StkADD = ADJ.StkADD, t1.StkDED = ADJ.StkDED " +
" FROM tblTEMPPOAnalyser AS t1 INNER JOIN " +
" (SELECT ItemId, SUM(QtyIn) AS StkADD, SUM(QtyOut) AS StkDED " +
" FROM tblBinCard WHERE DateOn BETWEEN CONVERT(date, '" + dtpFrom.Text + "') AND CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'STO003' " +
" GROUP BY ItemId) AS ADJ ON ADJ.ItemId = t1.ItemId ");
MySqlCommand cmd4 = new MySqlCommand(gSB.ToString(), GlobalData.GCon);
cmd4.ExecuteNonQuery();
DataTable dataTable = GlbData.getDataTable("SELECT ItemId FROM tblTEMPPOAnalyser");
for (int i = 0; i < dataTable.Rows.Count; i++)
{
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS Purchase, ISNULL(SUM(FreeIn),0) AS PurchaseFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'SUP002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string purchaseOP = GlbData.GetValue(gSB.ToString(), "Purchase").ToString();
string purchaseFreeOP = GlbData.GetValue(gSB.ToString(), "PurchaseFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS SCN, ISNULL(SUM(FreeOut),0) AS SCNFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'SUP003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string scnOP = GlbData.GetValue(gSB.ToString(), "SCN").ToString();
string scnFreeOP = GlbData.GetValue(gSB.ToString(), "SCNFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS Sale, ISNULL(SUM(FreeOut),0) AS SaleFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'CUS002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string saleOP = GlbData.GetValue(gSB.ToString(), "Sale").ToString();
string saleFreeOP = GlbData.GetValue(gSB.ToString(), "SaleFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS CCN, ISNULL(SUM(FreeIn),0) AS CCNFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'CUS003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string ccnOP = GlbData.GetValue(gSB.ToString(), "CCN").ToString();
string ccnFreeOP = GlbData.GetValue(gSB.ToString(), "CCNFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS StkADD, ISNULL(SUM(QtyOut),0) AS StkDED " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpFrom.Text + "') AND TranCode = 'STO003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string stkAddOP = GlbData.GetValue(gSB.ToString(), "StkADD").ToString();
string stkDedOP = GlbData.GetValue(gSB.ToString(), "StkDED").ToString();
double purchaseOPSTK = 0;
double purchaseFreeOPSTK = 0;
double scnOPSTK = 0;
double scnFreeOPSTK = 0;
double saleOPSTK = 0;
double saleFreeOPSTK = 0;
double ccnOPSTK = 0;
double ccnFreeOPSTK = 0;
double stkAddOPSTK = 0;
double stkDedOPSTK = 0;
if (string.IsNullOrEmpty(purchaseOP))
{
purchaseOPSTK = 0;
}
else
{
purchaseOPSTK = Convert.ToDouble(purchaseOP);
}
if (string.IsNullOrEmpty(purchaseFreeOP))
{
purchaseFreeOPSTK = 0;
}
else
{
purchaseFreeOPSTK = Convert.ToDouble(purchaseFreeOP);
}
if (string.IsNullOrEmpty(scnOP))
{
scnOPSTK = 0;
}
else
{
scnOPSTK = Convert.ToDouble(scnOP);
}
if (string.IsNullOrEmpty(scnFreeOP))
{
scnFreeOPSTK = 0;
}
else
{
scnFreeOPSTK = Convert.ToDouble(scnFreeOP);
}
if (string.IsNullOrEmpty(saleOP))
{
saleOPSTK = 0;
}
else
{
saleOPSTK = Convert.ToDouble(saleOP);
}
if (string.IsNullOrEmpty(saleFreeOP))
{
saleFreeOPSTK = 0;
}
else
{
saleFreeOPSTK = Convert.ToDouble(saleFreeOP);
}
if (string.IsNullOrEmpty(ccnOP))
{
ccnOPSTK = 0;
}
else
{
ccnOPSTK = Convert.ToDouble(ccnOP);
}
if (string.IsNullOrEmpty(ccnFreeOP))
{
ccnFreeOPSTK = 0;
}
else
{
ccnFreeOPSTK = Convert.ToDouble(ccnFreeOP);
}
if (string.IsNullOrEmpty(stkAddOP))
{
stkAddOPSTK = 0;
}
else
{
stkAddOPSTK = Convert.ToDouble(stkAddOP);
}
if (string.IsNullOrEmpty(stkDedOP))
{
stkDedOPSTK = 0;
}
else
{
stkDedOPSTK = Convert.ToDouble(stkDedOP);
}
double openSTK = (purchaseOPSTK + purchaseFreeOPSTK - scnOPSTK - scnFreeOPSTK - saleOPSTK - saleFreeOPSTK + ccnOPSTK + ccnFreeOPSTK + stkAddOPSTK - stkDedOPSTK);
MySqlCommand cmdOP = new MySqlCommand("UPDATE tblTEMPPOAnalyser SET OpenStk = " + openSTK + " WHERE ItemId=" + dataTable.Rows[i][0].ToString() + " ", GlobalData.GCon);
cmdOP.ExecuteNonQuery();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS Purchase, ISNULL(SUM(FreeIn),0) AS PurchaseFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string purchaseCL = GlbData.GetValue(gSB.ToString(), "Purchase").ToString();
string purchaseFreeCL = GlbData.GetValue(gSB.ToString(), "PurchaseFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS SCN, ISNULL(SUM(FreeOut),0) AS SCNFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'SUP003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string scnCL = GlbData.GetValue(gSB.ToString(), "SCN").ToString();
string scnFreeCL = GlbData.GetValue(gSB.ToString(), "SCNFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyOut),0) AS Sale, ISNULL(SUM(FreeOut),0) AS SaleFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS002' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string saleCL = GlbData.GetValue(gSB.ToString(), "Sale").ToString();
string saleFreeCL = GlbData.GetValue(gSB.ToString(), "SaleFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS CCN, ISNULL(SUM(FreeIn),0) AS CCNFree " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'CUS003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string ccnCL = GlbData.GetValue(gSB.ToString(), "CCN").ToString();
string ccnFreeCL = GlbData.GetValue(gSB.ToString(), "CCNFree").ToString();
gSB.Clear();
gSB.Append("SELECT ItemId, ISNULL(SUM(QtyIn),0) AS StkADD, ISNULL(SUM(QtyOut),0) AS StkDED " +
" FROM tblBinCard WHERE DateOn <= CONVERT(date, '" + dtpTo.Text + "') AND TranCode = 'STO003' AND ItemId = " + dataTable.Rows[i][0].ToString() + " " +
" GROUP BY ItemId");
string stkAddCL = GlbData.GetValue(gSB.ToString(), "StkADD").ToString();
string stkDedCL = GlbData.GetValue(gSB.ToString(), "StkDED").ToString();
double purchaseCLSTK = 0;
double purchaseFreeCLSTK = 0;
double scnCLSTK = 0;
double scnFreeCLSTK = 0;
double saleCLSTK = 0;
double saleFreeCLSTK = 0;
double ccnCLSTK = 0;
double ccnFreeCLSTK = 0;
double stkAddCLSTK = 0;
double stkDedCLSTK = 0;
if (string.IsNullOrEmpty(purchaseCL))
{
purchaseCLSTK = 0;
}
else
{
purchaseCLSTK = Convert.ToDouble(purchaseCL);
}
if (string.IsNullOrEmpty(purchaseFreeCL))
{
purchaseFreeCLSTK = 0;
}
else
{
purchaseFreeCLSTK = Convert.ToDouble(purchaseFreeCL);
}
if (string.IsNullOrEmpty(scnCL))
{
scnCLSTK = 0;
}
else
{
scnCLSTK = Convert.ToDouble(scnCL);
}
if (string.IsNullOrEmpty(scnFreeCL))
{
scnFreeCLSTK = 0;
}
else
{
scnFreeCLSTK = Convert.ToDouble(scnFreeCL);
}
if (string.IsNullOrEmpty(saleCL))
{
saleCLSTK = 0;
}
else
{
saleCLSTK = Convert.ToDouble(saleCL);
}
if (string.IsNullOrEmpty(saleFreeCL))
{
saleFreeCLSTK = 0;
}
else
{
saleFreeCLSTK = Convert.ToDouble(saleFreeCL);
}
if (string.IsNullOrEmpty(ccnCL))
{
ccnCLSTK = 0;
}
else
{
ccnCLSTK = Convert.ToDouble(ccnCL);
}
if (string.IsNullOrEmpty(ccnFreeCL))
{
ccnFreeCLSTK = 0;
}
else
{
ccnFreeCLSTK = Convert.ToDouble(ccnFreeCL);
}
if (string.IsNullOrEmpty(stkAddCL))
{
stkAddCLSTK = 0;
}
else
{
stkAddCLSTK = Convert.ToDouble(stkAddCL);
}
if (string.IsNullOrEmpty(stkDedCL))
{
stkDedCLSTK = 0;
}
else
{
stkDedCLSTK = Convert.ToDouble(stkDedCL);
}
double closeSTK = (purchaseCLSTK + purchaseFreeCLSTK - scnCLSTK - scnFreeCLSTK - saleCLSTK - saleFreeCLSTK + ccnCLSTK + ccnFreeCLSTK + stkAddCLSTK - stkDedCLSTK);
MySqlCommand cmdCL = new MySqlCommand("UPDATE tblTEMPPOAnalyser SET CloseStk = " + closeSTK + " WHERE ItemId=" + dataTable.Rows[i][0].ToString() + " ", GlobalData.GCon);
cmdCL.ExecuteNonQuery();
}
}
