public Boolean UpdateProfit(string MBLID, string CATEGORY)
{
// VERSION - 101
Boolean bRet = false;
Boolean bDetails = true;
string sql = "";
decimal MAR_Total = 0, MAP_Total = 0;
decimal HAR_Total_wt = 0, HAP_Total_wt = 0;
decimal HAR_Total_cbm = 0, HAP_Total_cbm = 0;
decimal AR = 0, AP = 0;
decimal mFactor_wt = 0, hFactor_wt = 0;
decimal mFactor_cbm = 0, hFactor_cbm = 0;
decimal nProfit = 0;
decimal nProfit_wt = 0;
decimal nProfit_cbm = 0;
decimal EXP_TOTAL = 0, INC_TOTAL = 0, PER = 0;
int ZeroWt = 0;
int ZeroCbmOrWt = 0;
DataRow DR_HEXP = null;
DataTable DT_MEXP = new DataTable();
DataTable DT_HEXP = new DataTable();
DataTable DT_HOUSE = new DataTable();
int iHouseTot = 0;
string mCol_1 = "";
string hCol_1 = "";
string mCol_2 = "";
string hCol_2 = "";
bDetails = false;
DataBase.Connections.DBConnection DB = null;
try
{
DB = new DataBase.Connections.DBConnection();
if (CATEGORY == "OI" || CATEGORY == "OE")
{
mCol_1 = "MBL_WEIGHT";
mCol_2 = "MBL_CBM";
hCol_1 = "HBL_WEIGHT";
hCol_2 = "HBL_CBM";
bDetails = true;
}
if (CATEGORY == "AI" || CATEGORY == "AE")
{
mCol_1 = "MBL_WEIGHT";
mCol_2 = "MBL_CHWT";
hCol_1 = "HBL_WEIGHT";
hCol_2 = "HBL_CHWT";
bDetails = true;
}
if (CATEGORY == "OT" || CATEGORY == "EX")
{
mCol_1 = "MBL_CBM";
hCol_1 = "HBL_CBM";
mCol_2 = "MBL_CHWT";
hCol_2 = "HBL_CHWT";
bDetails = true;
}
// Sum AR and AP Total For Master
sql = " select ";
sql += " sum(case when inv_arap = 'AR' then inv_total else 0 end) as income, ";
sql += " sum(case when inv_arap = 'AP' then inv_total else 0 end) as expense, ";
sql += " sum(case when inv_arap = 'AR' and inv_cost_type = 'M' then inv_total else 0 end) as ar_total, ";
sql += " sum(case when inv_arap = 'AP' and inv_cost_type = 'M' then inv_total else 0 end) as ap_total ";
sql += " from cargo_invoicem a where ";
sql += " inv_mbl_id = '" + MBLID + "' and rec_deleted = 'N'";
DT_MEXP = DB.ExecuteQuery(sql);
// Sum AR and AP Total For House
sql = " select inv_hbl_id, ";
sql += " sum(case when inv_arap = 'AR' then inv_total else 0 end) as ar_total, ";
sql += " sum(case when inv_arap = 'AP' then inv_total else 0 end) as ap_total ";
sql += " from cargo_invoicem a where ";
sql += " inv_mbl_id = '" + MBLID + "' and inv_cost_type = 'H' and rec_deleted = 'N'";
sql += " group by inv_hbl_id ";
DT_HEXP = DB.ExecuteQuery(sql);
sql = "";
sql = " select mbl_pkid,hbl_pkid,mbl_zero_wt,mbl_zero_cbm,mbl_zero_chwt,mbl_weight,mbl_cbm,mbl_chwt, hbl_weight,hbl_cbm,hbl_chwt ";
sql += " from cargo_masterm a inner join cargo_housem b on a.mbl_pkid = b.hbl_mbl_id ";
sql += " where mbl_pkid = '" + MBLID + "'";
// House Wise Weight And CBM For Master And House
DT_HOUSE = DB.ExecuteQuery(sql);
iHouseTot = DT_HOUSE.Rows.Count;
ZeroWt = 0;
ZeroCbmOrWt = 0;
if (iHouseTot > 0)
{
ZeroWt = Lib.Conv2Integer(DT_HOUSE.Rows[0]["mbl_zero_wt"].ToString());
if (CATEGORY == "OI" || CATEGORY == "OE")
{
ZeroCbmOrWt = Lib.Conv2Integer(DT_HOUSE.Rows[0]["mbl_zero_cbm"].ToString());
}
else
{
ZeroCbmOrWt = Lib.Conv2Integer(DT_HOUSE.Rows[0]["mbl_zero_chwt"].ToString());
}
}
string Profit_Type_Wt = "E"; // Equal
string Profit_Type_CbmOrWt = "E"; // Divide
if (iHouseTot > 0)
{
Profit_Type_Wt = (ZeroWt == 0) ? "D" : "E";
Profit_Type_CbmOrWt = (ZeroCbmOrWt == 0) ? "D" : "E";
}
// Master AR total And AP total
MAR_Total = 0; MAP_Total = 0;
if (DT_MEXP.Rows.Count > 0)
{
INC_TOTAL = Lib.Conv2Decimal(DT_MEXP.Rows[0]["income"].ToString());
EXP_TOTAL = Lib.Conv2Decimal(DT_MEXP.Rows[0]["expense"].ToString());
MAR_Total = Lib.Conv2Decimal(DT_MEXP.Rows[0]["ar_total"].ToString());
MAP_Total = Lib.Conv2Decimal(DT_MEXP.Rows[0]["ap_total"].ToString());
}
// Master Expense and Income
DataColumn[] Pkey = new DataColumn[1];
Pkey[0] = DT_HEXP.Columns["inv_hbl_id"];
DT_HEXP.PrimaryKey = Pkey;
DB.BeginTransaction();
if (bDetails == true)
{
foreach (DataRow Dr in DT_HOUSE.Rows)
{
mFactor_wt = Lib.Conv2Decimal(Dr[mCol_1].ToString());
hFactor_wt = Lib.Conv2Decimal(Dr[hCol_1].ToString());
mFactor_cbm = Lib.Conv2Decimal(Dr[mCol_2].ToString());
hFactor_cbm = Lib.Conv2Decimal(Dr[hCol_2].ToString());
if (Profit_Type_Wt == "E" || iHouseTot == 1)
{
mFactor_wt = iHouseTot;
hFactor_wt = 1;
}
if (Profit_Type_CbmOrWt == "E" || iHouseTot == 1)
{
mFactor_cbm = iHouseTot;
hFactor_cbm = 1;
}
DR_HEXP = DT_HEXP.Rows.Find(Dr["HBL_PKID"].ToString());
HAR_Total_wt = 0; HAP_Total_wt = 0;
HAR_Total_cbm = 0; HAP_Total_cbm = 0;
//if (mFactor_wt <= 0)
// mFactor_wt = 1;
//if (iHouseTot ==1 && hFactor_wt <= 0)
// hFactor_wt = 1;
//if (mFactor_cbm <= 0)
// mFactor_cbm = 1;
//if (iHouseTot == 1 && hFactor_cbm <= 0)
// hFactor_cbm = 1;
//if (CATEGORY == "OT") // Only single row
//{
// mFactor_wt = 1;
// hFactor_wt = 1;
// mFactor_cbm = 1;
// hFactor_cbm = 1;
//}
if (DR_HEXP != null)
{
HAR_Total_wt = Lib.Conv2Decimal(DR_HEXP["ar_total"].ToString());
HAP_Total_wt = Lib.Conv2Decimal(DR_HEXP["ap_total"].ToString());
HAR_Total_cbm = HAR_Total_wt;
HAP_Total_cbm = HAP_Total_wt;
}
AR = 0; AP = 0;
if (MAR_Total > 0)
{
AR = MAR_Total * hFactor_wt / mFactor_wt;
}
if (MAP_Total > 0)
{
AP = MAP_Total * hFactor_wt / mFactor_wt;
}
HAR_Total_wt += AR; HAP_Total_wt += AP;
nProfit_wt = HAR_Total_wt - HAP_Total_wt;
AR = 0; AP = 0;
if (MAR_Total > 0)
{
AR = MAR_Total * hFactor_cbm / mFactor_cbm;
}
if (MAP_Total > 0)
{
AP = MAP_Total * hFactor_cbm / mFactor_cbm;
}
HAR_Total_cbm += AR; HAP_Total_cbm += AP;
nProfit_cbm = HAR_Total_cbm - HAP_Total_cbm;
sql = " update cargo_housem set ";
sql += " hbl_inc_total_wt = " + HAR_Total_wt.ToString() + ",";
sql += " hbl_exp_total_wt = " + HAP_Total_wt.ToString() + ",";
sql += " hbl_inc_total_cbm = " + HAR_Total_cbm.ToString() + ",";
sql += " hbl_exp_total_cbm = " + HAP_Total_cbm.ToString() + ",";
sql += " hbl_revenue_wt = " + nProfit_wt.ToString() + ",";
sql += " hbl_revenue_cbm = " + nProfit_cbm.ToString() + "";
sql += " where hbl_pkid = '" + Dr["HBL_PKID"].ToString() + "'";
DB.ExecuteNonQuery(sql);
}
}
PER = 0;
nProfit = INC_TOTAL - EXP_TOTAL;
if (INC_TOTAL != 0)
{
PER = nProfit / INC_TOTAL * 100;
PER = Lib.Conv2Decimal(Lib.NumFormat(PER.ToString(), 2));
}
sql = "";
sql = " update cargo_masterm set ";
sql += " mbl_profit_type ='" + Profit_Type_Wt + Profit_Type_CbmOrWt + "',";
sql += " mbl_inc_total = " + INC_TOTAL.ToString() + ", ";
sql += " mbl_exp_total = " + EXP_TOTAL + ",";
sql += " mbl_revenue = " + nProfit.ToString() + ",";
sql += " mbl_per = " + PER.ToString();
sql += " where mbl_pkid = '" + MBLID + "'";
DB.ExecuteNonQuery(sql);
DB.CommitTransaction();
bRet = true;
}
catch (Exception)
{
bRet = false;
DB.RollbackTransaction();
}
return(bRet);
}
public Boolean IsShipmentClosed(string sID, string sTYPE, string BranchID)
{
Boolean bRet = false;
int LockDays = 0;
DateTime Dt_Now;
DateTime REF_DATE = DateTime.Now;
double Days = 0;
string sql = "";
string LOCK_STATUS = null;
string OPR_MODE = "";
DataBase.Connections.DBConnection DB = null;
DataTable Dt_temp = new DataTable();
DataTable Dt_param = new DataTable();
try
{
DB = new DataBase.Connections.DBConnection();
sql = "";
if (sTYPE == "MASTER")
{
sql = "select mbl_pkid,mbl_mode,mbl_lock,mbl_unlock_date,mbl_ref_date from cargo_masterm where mbl_pkid = '" + sID + "'";
}
else if (sTYPE == "HOUSE")
{
sql = "select mbl_pkid,mbl_mode,mbl_lock,mbl_unlock_date,mbl_ref_date from cargo_masterm a inner join cargo_housem b on a.mbl_pkid = b.hbl_mbl_id where b.hbl_pkid ='" + sID + "'";
}
else if (sTYPE == "INVOICE")
{
sql = "select mbl_pkid,mbl_mode,mbl_lock,mbl_unlock_date,mbl_ref_date from cargo_masterm a inner join cargo_invoicem b on a.mbl_pkid = b.inv_mbl_id where b.inv_pkid ='" + sID + "'";
}
if (sql != "")
{
Dt_temp = DB.ExecuteQuery(sql);
if (Dt_temp.Rows.Count > 0)
{
LOCK_STATUS = Dt_temp.Rows[0]["mbl_lock"].ToString();
OPR_MODE = Dt_temp.Rows[0]["mbl_mode"].ToString();
REF_DATE = (DateTime)Dt_temp.Rows[0]["mbl_ref_date"];
if (OPR_MODE == "GE" || OPR_MODE == "PS" || OPR_MODE == "PR" || OPR_MODE == "CM")
{
OPR_MODE = "ADMIN";
}
if (OPR_MODE == "OTHERS" || OPR_MODE == "FA" || OPR_MODE == "EXTRA")
{
OPR_MODE = "OTHERS";
}
sql = "";
if (OPR_MODE == "SEA EXPORT" || OPR_MODE == "SEA IMPORT")
{
sql = "select param_name3 from mast_param where param_name1 = 'LOCK-DAYS-SEA' and param_name2 = '" + BranchID + "'";
}
if (OPR_MODE == "AIR EXPORT" || OPR_MODE == "AIR IMPORT")
{
sql = "select param_name3 from mast_param where param_name1 = 'LOCK-DAYS-AIR' and param_name2 = '" + BranchID + "'";
}
if (OPR_MODE == "OTHERS")
{
sql = "select param_name3 from mast_param where param_name1 = 'LOCK-DAYS-OTHERS' and param_name2 = '" + BranchID + "'";
}
if (OPR_MODE == "ADMIN")
{
sql = "select param_name3 from mast_param where param_name1 = 'LOCK-DAYS-ADMIN' and param_name2 = '" + BranchID + "'";
}
if (sql != "")
{
Dt_param = DB.ExecuteQuery(sql);
if (Dt_param.Rows.Count > 0)
{
LockDays = Lib.Conv2Integer(Dt_param.Rows[0]["param_name3"].ToString());
}
}
if (LOCK_STATUS == null || LOCK_STATUS.Trim() == "")
{
Dt_Now = DateTime.Now;
Days = Dt_Now.Subtract(REF_DATE).TotalDays;
if ((OPR_MODE == "SEA EXPORT" || OPR_MODE == "SEA IMPORT") && Days > LockDays && LockDays > 0)
{
bRet = true;
}
if ((OPR_MODE == "AIR EXPORT" || OPR_MODE == "AIR IMPORT") && Days > LockDays && LockDays > 0)
{
bRet = true;
}
if ((OPR_MODE == "OTHERS") && Days > LockDays && LockDays > 0)
{
bRet = true;
}
if ((OPR_MODE == "ADMIN") && Days > LockDays && LockDays > 0)
{
bRet = true;
}
}
else if (LOCK_STATUS == "L")
{
bRet = true;
}
else if (LOCK_STATUS == "U")
{
bRet = false;
if (Dt_temp.Rows[0]["mbl_unlock_date"] != DBNull.Value)
{
Dt_Now = DateTime.Now;
Days = Dt_Now.Subtract((DateTime)Dt_temp.Rows[0]["mbl_unlock_date"]).TotalDays;
if (Days >= 2)
{
bRet = true;
}
}
}
}
}
}
catch (Exception)
{
bRet = true;
}
if (DB != null)
{
DB.CloseConnection();
}
return(bRet);
}