Exemple #1
0
        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);
        }
Exemple #2
0
        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);
        }