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); }