コード例 #1
0
            //public CResult ReadByBranchAndDate(string BranchCode, DateTime FromDate, DateTime ToDate)
            //{
            //    List<CGRDetails> oItemList = new List<CGRDetails>();


            //    oResult = new CResult();
            //    conn = oConnManager.GetConnection(out s_DBError);
            //    if (conn != null)
            //    {
            //        try
            //        {
            //            DataSet ds = new DataSet();
            //            SqlCommand cmd = new SqlCommand();

            //            cmd.Connection = conn;
            //            cmd.CommandText = "sp_GRDetail_ReadByBrnIdAndDate";
            //            cmd.CommandType = CommandType.StoredProcedure;
            //            cmd.Parameters.Clear();

            //            cmd.Parameters.AddWithValue("@BranchID", BranchCode);
            //            cmd.Parameters.AddWithValue("@SOMstr_DateFrom", FromDate);
            //            cmd.Parameters.AddWithValue("@SOMstr_DateTo", ToDate);

            //            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //            da.Fill(ds);
            //            DataTable dtItem = ds.Tables[0];

            //            //  oItemList.Add("");
            //            foreach (DataRow dr in dtItem.Rows)
            //            {
            //                CGRDetails oGRDetails = new CGRDetails();

            //                oGRDetails.GRDet_ItemOID = dr["GRDet_ItemOID"].ToString();
            //                oGRDetails.GRDet_QTY = float.Parse(dr["GRDet_QTY"].ToString());
            //                oGRDetails.GRDet_BranchOID = dr["GRDet_Branch"].ToString();
            //                oGRDetails.GRDet_Price = float.Parse(dr["GRDet_Price"].ToString());
            //                oGRDetails.GRDet_Amount = float.Parse(dr["GRDet_Amount"].ToString());
            //                oGRDetails.GRDet_ItemName = dr["GRDet_ItemName"].ToString();

            //                oItemList.Add(oGRDetails);

            //            }

            //            oResult.IsSuccess = true;
            //            oResult.Data = oItemList;

            //        }
            //        catch (SqlException e)
            //        {
            //            oResult.IsSuccess = false;
            //            oResult.ErrMsg = e.Message;
            //        }
            //        finally
            //        {
            //            oConnManager.Close();
            //        }
            //    }
            //    else
            //    {
            //        oResult.IsSuccess = false;
            //        oResult.ErrMsg = s_DBError;
            //    }

            //    return oResult;
            //}
            //public CResult ReduceByItemOID(List<CSODetails> oListSODetails)
            //{
            //    oResult = new CResult();
            //    conn = oConnManager.GetConnection(out s_DBError);
            //    if (conn != null)
            //    {
            //        SqlCommand cmd = new SqlCommand();
            //        cmd.Connection = conn;

            //        cmd.Transaction = oConnManager.BeginTransaction();
            //        try
            //        {
            //            foreach (CSODetails oSODetails in oListSODetails)
            //            {
            //                cmd.CommandText = "sp_GRDetails_ReduceByItemOID";
            //                cmd.CommandType = CommandType.StoredProcedure;
            //                cmd.Parameters.Clear();

            //                cmd.Parameters.AddWithValue("@ItemOID", oSODetails.SODet_ItemOID);
            //                cmd.Parameters.AddWithValue("@ItemQTY", oSODetails.SODet_QTY);
            //                cmd.Parameters.AddWithValue("@BranchOID", oSODetails.SODet_BranchOID);

            //                cmd.ExecuteNonQuery();
            //            }

            //            oConnManager.Commit();

            //            oResult.IsSuccess = true;
            //        }
            //        catch (SqlException e)
            //        {
            //            string sRollbackError = oConnManager.Rollback();

            //            oResult.IsSuccess = false;
            //            oResult.ErrMsg = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
            //        }
            //        finally
            //        {
            //            oConnManager.Close();
            //        }
            //    }
            //    else
            //    {
            //        oResult.IsSuccess = false;
            //        oResult.ErrMsg = s_DBError;
            //    }

            //    return oResult;
            //}


            public CResult ExportAndUpdate(CGRMaster oMaster, string url)
            {
                CInventory        oInventory      = new CInventory();
                CResult           oResult         = new CResult();
                DataTable         dt              = new DataTable();
                List <CInventory> oInvtList       = new List <CInventory>();
                List <CGRDetails> exportExcelList = new List <CGRDetails>();

                oResult = new CResult();
                conn    = oConnManager.GetConnection(out s_DBError);
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.Parameters.Clear();

                    cmd.Transaction = oConnManager.BeginTransaction();
                    try
                    {
                        foreach (CGRDetails oDetails in oMaster.GRMstr_DetailsList)
                        {
                            oInventory                = new CInventory();
                            oInventory.Invt_Branch    = oDetails.GRDet_Branch;
                            oInventory.Invt_BranchOID = oDetails.GRDet_BranchOID;
                            oInventory.Invt_InvType   = oDetails.GRDet_InvType;
                            oInventory.Invt_ItemOID   = oDetails.GRDet_ItemOID;
                            oInventory.Invt_LocOID    = oDetails.GRDet_LocOID;
                            oInventory.Invt_QTY       = oDetails.GRDet_QTY;

                            oInvtList.Add(oInventory);
                            exportExcelList.Add(oDetails);
                        }
                        CsvExport <CGRDetails> csv = new CsvExport <CGRDetails>(exportExcelList);
                        csv.Export();
                        csv.ExportToFile(url);

                        // InventoryDT = ConvertToDataTable<CGRDetails>(exportExcelList);

                        CInventoryBO oInventoryBO = new CInventoryBO();
                        oResult = oInventoryBO.InvtDec(oInvtList);

                        if (oResult.IsSuccess)
                        {
                            oConnManager.Commit();
                            oResult.IsSuccess = true;
                        }
                        else
                        {
                            oResult.ErrMsg    = oConnManager.Rollback();
                            oResult.IsSuccess = false;
                        }
                    }
                    catch (SqlException e)
                    {
                        string sRollbackError = oConnManager.Rollback();

                        oResult.IsSuccess = false;
                        oResult.ErrMsg    = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
                    }
                    finally
                    {
                        oConnManager.Close();
                    }
                }
                else
                {
                    oResult.IsSuccess = false;
                    oResult.ErrMsg    = s_DBError;
                }
                return(oResult);
            }
コード例 #2
0
            public CResult ReduceByItemOID(List <CSODetails> oListSODetails)
            {
                List <CInventory> oInvtList = new List <CInventory>();

                oResult = new CResult();
                conn    = oConnManager.GetConnection(out s_DBError);
                if (conn != null)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;

                    cmd.Transaction = oConnManager.BeginTransaction();
                    try
                    {
                        foreach (CSODetails oSODetails in oListSODetails)
                        {
                            StringBuilder sb = new StringBuilder();
                            sb.Append("DECLARE @GRDet_OID char(24); ");
                            sb.Append("DECLARE @GRDet_QTY float; ");
                            sb.Append("DECLARE @TempQTY float; ");
                            sb.Append("WHILE (@ItemQTY > 0) BEGIN ");
                            sb.Append("SELECT @GRDet_QTY = 0; ");
                            sb.Append("SELECT @TempQTY = 0; ");
                            sb.Append("SELECT TOP(1) @GRDet_OID = [GRDet_OID], @GRDet_QTY = [GRDet_QTY] ");
                            sb.Append("FROM t_GRDet ");
                            sb.Append("WHERE [GRDet_ItemOID] = @ItemOID	AND [GRDet_BranchOID] = @BranchOID AND [GRDet_QTY] > 0 ");
                            sb.Append("IF (@GRDet_OID IS NOT NULL) BEGIN ");
                            sb.Append("IF(@ItemQTY > @GRDet_QTY) BEGIN ");
                            sb.Append("SELECT @TempQTY = 0; ");
                            sb.Append("SELECT @ItemQTY = @ItemQTY - @GRDet_QTY; ");
                            sb.Append("END ELSE BEGIN ");
                            sb.Append("SELECT @TempQTY = @GRDet_QTY - @ItemQTY; ");
                            sb.Append("SELECT @ItemQTY = 0; ");
                            sb.Append("END ");
                            sb.Append("UPDATE t_GRDet ");
                            sb.Append("SET ");
                            sb.Append("[GRDet_QTY] = @TempQTY ");
                            sb.Append("WHERE ");
                            sb.Append("[GRDet_OID] = @GRDet_OID; ");
                            sb.Append("END ");
                            sb.Append("SELECT @GRDet_OID = NULL; ");
                            sb.Append("END");

                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Clear();

                            cmd.CommandText = sb.ToString();
                            cmd.Parameters.AddWithValue("@ItemOID", oSODetails.SODet_ItemOID);
                            cmd.Parameters.AddWithValue("@ItemQTY", oSODetails.SODet_QTY);
                            cmd.Parameters.AddWithValue("@BranchOID", oSODetails.SODet_BranchOID);

                            cmd.ExecuteNonQuery();

                            // populate inv list
                            CInventory oInventory = new CInventory();

                            oInventory.Invt_Branch    = oSODetails.SODet_Branch;
                            oInventory.Invt_BranchOID = oSODetails.SODet_BranchOID;
                            oInventory.Invt_InvType   = (int)EInvType.GOOD;
                            oInventory.Invt_ItemOID   = oSODetails.SODet_ItemOID;
                            oInventory.Invt_LocOID    = oSODetails.SODet_LocOID;
                            oInventory.Invt_QTY       = oSODetails.SODet_QTY;

                            oInvtList.Add(oInventory);
                        }

                        //Update inv
                        CInventoryBO oInventoryBO = new CInventoryBO();
                        oResult = oInventoryBO.InvtDec(oInvtList);

                        if (oResult.IsSuccess)
                        {
                            oConnManager.Commit();
                            oResult.IsSuccess = true;
                        }
                        else
                        {
                            oResult.ErrMsg    = oConnManager.Rollback();
                            oResult.IsSuccess = false;
                        }
                    }
                    catch (SqlException e)
                    {
                        string sRollbackError = oConnManager.Rollback();

                        oResult.IsSuccess = false;
                        oResult.ErrMsg    = sRollbackError.Equals("") ? oConnManager.GetErrorMessage(e) : sRollbackError;
                    }
                    finally
                    {
                        oConnManager.Close();
                    }
                }
                else
                {
                    oResult.IsSuccess = false;
                    oResult.ErrMsg    = s_DBError;
                }

                return(oResult);
            }